Because SQL Server debugging is quite pedantic in its requirements, you should perhaps read this section rather carefully . Here are the basic version and compatibility requirements:
The database must be SQL Server 6.5 (Service Pack 3 [SP3] or later), SQL Server 7.0, or SQL Server 2000.
The database server must be running Windows NT 4.0 or later.
The application that you're debugging must be using the managed data adapter for SQL Server.
You must be using OLE DB, ODBC, or DB-LIB as the database connection protocol.
The desktop edition of SQL Server 2000 can be debugged using the Professional version (or higher) of Visual Studio .NET.
The full edition of SQL Server needs the Enterprise Developer or Enterprise Architect version of Visual Studio.
If you can't see stored procedures in Visual Studio's Server Explorer window, it's likely that you're trying to debug the full edition of SQL Server with only the Professional version of Visual Studio ”this won't work.
Although Query Analyzer 2000 has a new built-in debugging feature, which incidentally works when used with either SQL Server 7.0 or SQL Server 2000, Query Analyzer 7.0 doesn't have this feature.
One important point to consider is that it isn't advisable to debug a production database server. Single-stepping through a stored procedure can cause the debugger to lock system resources that are needed by other SQL Server processes, which could cause nasty production problems.