Troubleshooting tips for T-SQL Debugger in Visual Studio .NET

This article contains guidelines and directions to troubleshoot basic problems that are related to T-SQL Debugger in Microsoft Visual Studio .NET. However, this is not an exhaustive list, and some problems may be specific to your environment. For additional information about T-SQL debugging, click the following article numbers to view the articles in the Microsoft Knowledge Base: For more information about enabling SQL debugging, visit the following Microsoft Developer Network (MSDN) Web site:

Troubleshooting T-SQL Debugger

Use the following methods to help troubleshoot T-SQL Debugger in Visual Studio .NET:

• Make sure that Remote Procedure Call (RPC) services are started on the server computer. To do this, follow these steps:
  1. In Control Panel, right-click Services, and then click Open.
  2. Check to make sure that the Remote Procedure Call (RPC) and the Remote Procedure Call (RPC) Locator services are running, and then set these services to start automatically.
• Make sure that the T-SQL Debugger user account has read and execute permissions for debugger components in the SQL Debugging folder. The SQL Debugging folder is in the following location:
  Program Files\Common Files\Microsoft Shared
  If the user account does not have these permissions, you receive the following error message:
  Unable to start T-SQL debug session. Ensure sqldbreg2.exe and proxy/stub DLL sqldbg.dll are registered on this machine.
• Make sure that the name of the stored procedure does not contain any quotation marks.
  
  For more information about errors that occur when you debug procedures with names that contain quotation marks, visit the following MSDN Web site:
  http://msdn2.microsoft.com/en-us/library/abk849z6(vs.71).aspx (http://msdn2.microsoft.com/en-us/library/abk849z6(vs.71).aspx)
• Make sure that the stored procedure does not contain a RAISERROR statement that raises any error with severity 16 or higher. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
  280099  (http://support.microsoft.com/kb/280099/ ) BUG: Transact-SQL Debugger cannot debug a stored procedure that Contains a RAISERROR statement that raises any error with severity 16 or higher
• Check T-SQL Debugger error logs in the Event Viewer.
  
  For more information, visit the following MSDN Web site:
  http://msdn2.microsoft.com/en-us/library/hcez68k4(vs.71).aspx (http://msdn2.microsoft.com/en-us/library/hcez68k4(vs.71).aspx)
• T-SQL Debugger uses Distributed COM (DCOM) to communicate between the client computer and the database server. It is a good idea to 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 MSDN Web sites:
  http://msdn2.microsoft.com/en-us/library/s0t6w6kc(vs.71).aspx (http://msdn2.microsoft.com/en-us/library/s0t6w6kc(vs.71).aspx)
  http://msdn2.microsoft.com/en-us/library/6ksc0a82(vs.71).aspx (http://msdn2.microsoft.com/en-us/library/6ksc0a82(vs.71).aspx)
  http://msdn2.microsoft.com/en-us/library/aa291270(VS.71).aspx (http://msdn2.microsoft.com/en-us/library/aa291270(VS.71).aspx)
• If you install a new version of Microsoft SQL Server after you install SQL remote debugging, you must run the remote components setup to make sure that you have the correct components installed for remote debugging.
  
  For more information about SQL remote debugging, visit the following MSDN Web sites:
  http://msdn2.microsoft.com/en-us/library/bt727f1t(vs.71).aspx (http://msdn2.microsoft.com/en-us/library/bt727f1t(vs.71).aspx)
  http://msdn2.microsoft.com/en-us/library/ey7ec813(vs.71).aspx (http://msdn2.microsoft.com/en-us/library/ey7ec813(vs.71).aspx)
• Examine the application event log and the system event log on the computer that is running SQL Server. To do this, follow these steps:
  1. In Control Panel, double-click Administrative Tools.
  2. Double-click Event Viewer. In the Event Viewer window, you see the application event log and the system event log.
• For T-SQL Debugger to work correctly in Visual Studio .NET, the computer name and the SQL Server installation name must match. If you rename the SQL Server installation, the computer name does not match the SQL Server installation name and T-SQL Debugger fails. For additional information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
  817253  (http://support.microsoft.com/kb/817253/ ) PRB: Cannot debug stored procedure in Visual Studio .NET after you rename SQL Server
  
  
  317241  (http://support.microsoft.com/kb/317241/ ) PRB: Renaming a host computer for SQL Server causes stored procedure debug to fail
• Verify that the T-SQL Debugger session is started from the terminal server client session. T-SQL Debugger debugging options are not available when a terminal server client is connecting to a terminal server that has Microsoft SQL Server 2000 installed. Debugging a stored procedure from this terminal client computer through a terminal server session is not available.
  
  The error message in the application event log of the terminal server is similar to the following error message:
  Unable to connect to debugger on %SQLSERVER_MACHINENAME%(Error = 0x80070057 The parameter is incorrect. ). Ensure that client side components such as SQLLE.DLL are installed and registered on %SQLSERVER_MACHINENAME%. Debugging disabled for connection 54.
  For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
  280100  (http://support.microsoft.com/kb/280100/ ) BUG: Transact-SQL Debugger is not available through Terminal Server session
• Verify that you have two stored procedures with the same name. One stored procedure is owned by the database owner (DBO) and the other stored procedure is owned by a non-DBO user. If you have two stored procedures that have the same name but that have different owners, neither of the users can step through the stored procedure by using the T-SQL Debugger. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
  296539  (http://support.microsoft.com/kb/296539/ ) BUG: T-SQL Debugger doesn't allow stepping through stored procedures with same name owned by non-DBO users
• Make sure that you are the owner of the stored procedure that you are trying to debug. In T-SQL Debugger, you cannot debug stored procedures that you do not own after you install Microsoft SQL Server 2000 Service Pack 3 (SP3). When you debug the stored procedure that is not owned by you, you may receive an error message that is similar to the following:
  Server: Msg 229, Level 14, State 5, Procedure sp_sdidebug, Line 1 [Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE permission denied on object 'sp_sdidebug', database 'dbname', owner 'ownername'.
  For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
  328173  (http://support.microsoft.com/kb/328173/ ) PRB: A database user cannot debug stored procedures that another user owns
• Make sure that you have execute permissions on the stored procedure debugging. When you do not have execute permissions on the stored procedure debugging, you receive an error message that is similar to the following:
  Server: Msg 229, Level 14, State 5, Procedure sp_sdidebug, Line 1 [Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE permission denied on object 'sp_sdidebug', database 'dbname', owner 'dbowner'.
  To grant execute permissions on the stored procedure debugging, run the following command in Query Analyzer:

  GRANT EXECUTE
  ON sp_sdidebug 
  TO <myUserName>
  

• If your server is Microsoft SQL Server 2000 SP3, you must run the following:

  EXECUTE  sp_sdidebug 'LEGACY_ON' 

  on your computer that is running SQL Server each time it starts. You can do this manually from Query Analyzer. This is a new security switch that was added in SP3.
• To make sure that T-SQL Debugger is working correctly, test T-SQL Debugger on the same computer where SQL Server is installed, if you can.
• It is not a good idea to use the T-SQL Debugger on a production server. While in step execution mode, the debugger can lock certain system resources that other processes must access.
• Make sure that you have correct versions of T-SQL Debugger component files.
  • Server components include Mssdi98.dll and Sqldbg.dll.
  • Client components include Sqldbreg.exe and Sqldbg.dll.
  The Mssdi98.dll file should be in the same folder as the Sqlservr.exe file. By default, Mssdi98.dll is in the BINN folder. By default, Sqldbg.dll and Sqldbreg.exe are in the SQL Debugging folder. The SQL Debugging folder is in the following location:
  Program Files\Common Files\Microsoft Shared

For additional information, click the following article numbers to view the articles in the Microsoft Knowledge Base: For more information about debugging SQL, visit the following Microsoft Developers Network (MSDN) Web site: