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

This article was previously published under Q280101
This article has been archived. It is offered "as is" and will no longer be updated.
This article describes the limitations of Transact-SQL Debugger in SQL Server 2000. This article also provides guidelines and directions to troubleshoot basic problems related to Transact-SQL Debugger. However, this is not an exhaustive list, and some problems may be specific to your environment.

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.
  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:
  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.
For additional information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
272074 FIX: T-SQL Debugger Parses SQL Server 2000 Return Value Wrong
281535 PRB: Error Debugging Procedure with sql_variant Input Parameter
270061 BUG: Access Violation Occurs When You Debug Stored Procedure
175868 BUG: T-SQL Debugger Does Not Run on NT with User Account
328173 PRB: A Database User Cannot Debug Stored Procedures That Another User Owns
317241 PRB: Renaming a Host Computer for SQL Server Causes Stored Procedure Debug to Fail
296539 BUG: T-SQL Debugger Doesn't Allow Stepping Through Stored Procedures with Same Name Owned by Non-DBO Users
328151 INF: T-SQL Debugger Is Turned Off By Default for Earlier Clients After You Install SQL Server 2000 Service Pack 3

Article ID: 280101 - Last Review: 12/05/2015 22:53:08 - Revision: 2.7

Microsoft SQL Server 2000 Standard Edition

  • kbnosurvey kbarchive kbtsql kbsysadmin kbdebug kbfaq kbprb kbhowto kbinfo KB280101