INF: Transact-SQL Debugger Limitations and Troubleshooting Tips for SQL Server 2000 (280101)

MORE INFORMATION

Limitations with Transact-SQL Debugger

1. You cannot debug a stored procedure from the terminal client computer through a terminal server session. You may receive the following error message in the Event Viewer Application log:
   Unable to connect to debugger on SQLSERVER_COMPUTERNAME (Error = 0x80070057 The parameter is incorrect. ). Ensure that client side components such as SQLLE.DLL are installed and registered on SQLSERVER_COMPUTERNAME. Debugging disabled for connection spid-number.
   For additional information about how to solve this problem, click the following article number to view the article in the Microsoft Knowledge Base:

   280100 BUG: Transact-SQL Debugger Is Not Available Through Terminal Server Session

2. If you have installed different versions of SQL Server on one computer, the debugger of the named instance uses the SQL Server startup account of the default instance.
3. You cannot debug the OLE Automation extended stored procedures (sp_OA*) in Transact-SQL Debugger by using the Step Into (F11) option or any other extended stored procedures because extended stored procedures are actually DLLs that are written in languages other than SQL. For additional information about how to debug extended stored procedures, click the following article number156099 to view the article156099 in the Microsoft Knowledge Base:

   HOW TO: Debug an Extended Stored Procedure

4. You cannot debug stored procedures that contain the RAISERROR statement. (The RAISERROR statement raises errors that have a severity 16 or higher.) For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

   280099 BUG: Transact-SQL Debugger Cannot Debug a Stored Procedure that Contains a RAISERROR Statement that Raises any Error with Severity 16 or Higher

5. You can only run one stored procedure in debug mode in a SQL Query Analyzer session. SQL Query Analyzer does not support multiple instances of Transact-SQL Debugger. When you try to debug a second stored procedure in the same SQL Query Analyzer session, you are prompted to cancel the active debugging stored procedure.
6. You cannot modify the active stored procedure in debug mode. The object that starts the debugging process is locked. Because it is locked, you are prevented from making any modifications except for printing and scripting. Because of connection constraints, you cannot create a new query while the debugger window is in the foreground. To create a new query, either bring an existing query window to the foreground or open a new connection to the database.
7. In debug mode, variables of the table data type do not appear in the local variables window.
8. In debug mode, variables of sql_variant, text, ntext, image, and cursor data types appear in the local variables window. You cannot modify these variables.
9. Microsoft does not support debugging stored procedure greater than 128K.
10. Microsoft does not support debugging nested stored procedure greater than 32 stored procedure calls.
11. Microsoft does not support debugging a stored procedure that has more than 1023 arguments.
12. In debug mode, local variables windows watch expression displays only the first 255 characters for varchar, char and nvarchar local variables.

Troubleshooting the Transact-SQL Debugger

1. See the "SQL Server Tools Troubleshooting" topic in SQL Server Books Online.
2. Examine SQL error logs and the Application and System logs in Event Viewer.
3. SQL debugging uses distributed COM (DCOM) to communicate between the client computer and the database server. You must configure DCOM to allow remote users to attach the debugger to a process on the database server. Problems may occur if the DCOM permission settings are incorrect.
   
   For more information about DCOM permission settings, visit the following Microsoft Web sites:

   Troubleshooting the Transact-SQL Debugger
   Setting Machine-Wide Security Using DCOMCNFG

4. Make sure that the remote procedure call (RPC) services are started on the server.
5. Make sure that you have the correct versions of Transact-SQL Debugger component files.
   • Server components include Mssdi98.dll and Sqldbg.dll.
   • Client components include Sqldbreg.exe and Sqldbg.dll.
   The Mssdi.dll file should be in the same folder as the Sqlservr.exe file. By default, Mssdi.dll is in the BINN folder. By default, Sqldbg.dll and Sqldbreg.exe are in the Program Files\Common Files\Microsoft Shared\SQL Debugging folder.
6. Use the Domain User account (and not the Local System account) as the SQL Server service account and that the Domain User account is a member of the Local Administrators group for remote debugging. For example, the following error message is logged in the Application log when the SQL Server logon account is set to use the Local System account:
   SQL Server when started as service must not log on as System Account. Reset to logon as user account using Control Panel.
   For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

   281649 PRB: SQL Server Debugging with Visual Studio Service Pack 5 Requires a Non-System Account

   
   
7. It is not a good idea to use Transact-SQL Debugger on a production server. While in step execution mode, the debugger can lock certain system resources that other processes must access.