The T-SQL Debugger

See Also

The T-SQL debugger is integrated with the Data Environment designer. It allows you to interactively debug remote stored procedures written in Microsoft SQL Server's Transact SQL dialect, from within the Visual Basic development environment. Using the T-SQL debugger, you can:

• Display the SQL call stack, local variables, and parameters for the SQL stored procedure.
  
  
• Control and manage breakpoints.
  
  
• View and modify local variables and parameters.
  
  
• View global variables.

Setup and Compatibility

In order to use the T-SQL debugger, you must have SQL Server version 6.5 with Service Pack 3 or later installed as your database server. The debugger uses the functionality exposed by SQL Server's Sdi.dll, and exposes that functionality through Remote Automation.

The client-side components of the T-SQL debugger are correctly installed and configured when you choose to install all the Enterprise tools in your Visual Basic installation. If it is necessary to repeat the setup process, select "Custom" from the CD Installation dialog box, and choose "Select All" for the Enterprise Tools selection.

Server-Side Setup

With SQL Server version 6.5 and Service Pack 3 or later installed, you can install and register the SQL Debugger interface and Remote Automation component on the server. These components are located at \Program files\Common Files\Microsoft Shared\SQL Debugging. On Windows NT 4.0, simply run the setup program Sdi_nt4.exe.

Note   For setup on NT Server 3.51, you must manually copy and register the necessary files. Complete instructions for this process are included in the Readme.txt file in the \Program Files\Common Files\Microsoft Shared\SQL Debugging folder.

Using the T-SQL Debugger

There are different methods you can use to invoke T-SQL debugging.

1. To debug a stored procedure or batch query at design time, add the T-SQL Debugger Add-In via Visual Basic's Add-In Manager (on the Add-Ins menu). Then you can start the add-in by clicking T-SQL Debugger on the Add-Ins menu. You then simply select a DSN, and either Stored Procedure or Batch SQL and click the Execute button. This will invoke the debugger and allow you to debug the SQL you are interested in.
   
   
2. To debug stored procedures while debugging Visual Basic code (run-time debugging), select T-SQL Debugging Options on Visual Basic's Tools menu. The options dialog box allows you to:
   • Turn on automatic step into stored procedures, which will bring up the T-SQL Debugger whenever you step into an ADO or RDO method that executes a stored procedure.
     
     
   • Turn Safe Mode on, which will automatically roll back any design-time queries that you debug.
     
     
   • Limit the number of rows that appear in the T-SQL Debugger output window when debugging design time queries.
     
     
   • Set the login timeout value that the debugger uses to connect to the database, to get internal SQL State.

   Once you have selected the Automatically step into Stored Procedures check box, if you step into (F8) a line of code that executes an ADO or RDO method that invokes a stored procedure, the debugger will automatically be started. You can then step through the stored procedure and then continue debugging your Visual Basic code.

   Note   SQL Server will return from a stored procedure before it has finished executing if the stored procedure returns enough data to fill its buffers. If this happens, both the T-SQL Debugger and the Visual Basic debugger will be active at the same time. Your Visual Basic code must fetch the results from ADO or RDO before the stored procedure will complete its execution. If this happens, make sure your basic code reads the result sets by placing Visual Basic in Run Mode (F5) and setting breakpoints where you would like to stop execution. You can toggle back and forth between Visual Basic and the T-SQL Debugger by using the taskbar or using the ALT+TAB key combination.

3. You can also launch the T-SQL Debugger:
   • From the Data Environment designer
     
     
   • While stepping through ADO or RDO code
     
     
   • By right-clicking a stored procedure in the Data View window and choosing the Debug command
     
     
   • From the UserConnection designer

Once you have started the debugger, it establishes the ODBC connection and displays the Enter Unassigned Parameters dialog box, as shown.

Unassigned Parameters Dialog box

Enter values for any unassigned parameters in the Value field, then click OK. The T-SQL debugger interface appears and displays the text of the stored procedure:

T-SQL Debugger Interface

Debugging Options

With the SQL statement displayed, several debugging options are available on the toolbar buttons and on the Debug menu. These options include:

• Go
  
  
• Set and clear breakpoints
  
  
• Step
  
  
• Step into subexpression
  
  
• Step over subexpression
  
  
• Run to cursor
  
  
• Stop debugging
  
  
• Restart

Views and Options

In addition to the code window containing the SQL statement you are debugging, the T-SQL debugger interface presents separate output windows for local and global variables, and for the output (result set) of the query. The View menu also allows you to open a separate Call Stack window and a Temp Table Dump window, so that you can examine these as the code executes.

The Options menu lets you customize the appearance of the T-SQL debugger by changing the fonts and colors used for display.

Exiting from the T-SQL Debugger

When you are finished with your debugging session, click Exit on the File menu to close the debugger. To execute a query again, click Restart on the Debug menu.

Troubleshooting

If you are having problems getting T-SQL debugging to work, you will need to check the event log on the server. SDI.DLL will log events in the application section of the event viewer. COM or distributed COM errors will log events in the system section of the viewer.

• Make sure that the two computers can communicate with each other. The easiest mechanism to do this is by typing ping and the computer name of the client at a command prompt on the server if you are running TCP/IP. If this fails, fix the connectivity problem between the machines.
  
  
• Make sure the file SDI.DLL resides in the same directory as SQLSERVR.EXE. This will be in the binn sub-directory under the main SQL Server directory. The default is c:\mssql\binn.
  
  
• Ensure that the RPC services are started on the server machine. You do this by starting the control panel, opening the services application and checking that the Remote Procedure Call ( RPC ) Service is running and set to start automatically, as well as the Remote Procedure Call ( RPC ) Locator.
  
  
• Ensure that SQL Server is not set to log on as the SystemAccount. You do this by starting the control panel, opening the services application and double clicking on the MSSQLServer service. If the service is set to run as the SystemAccount, change this so the server will log on to a specific account that is valid to the domain that you are in. If debugging still fails, make sure that the account SQL Server started as has sufficient rights to launch an automation server on the client machine.
  
  
• If you see COM error 80080005 in the event log, make sure that you did not start remote automation (autmgr32) from the command prompt. Autmgr32.exe should only be running in the winstation of the account that SQL Server logged in as. Any other winstation will cause problems. If this is the case, close down autmgr32.exe via the task manager and let the sdi.dll and autprx32.dll load autmgr32 via COM.
  
  
• Make sure Remote Automation is successfully installed on the server and client machines, if both the client and server do not have Distributed COM (DCOM) installed and loaded.
  
  
• If your client system is running Windows NT 4.0 or later, run DCOMCNFG and make sure that everyone has launch and access permission for vbsdicli.exe.