INF: Tips for Debugging Stored Procedures from Visual Basic

• Microsoft SQL Server versions 6.5, 7.0

SUMMARY

With Visual Basic 5.0 Enterprise Edition or later, you can debug SQL Server stored procedures. This article contains a list of assorted tips that may help you configure the Visual Basic 5.0 or later Enterprise Edition Transact-SQL (T-SQL) Debugger.

MORE INFORMATION

Use the following information for debugging:

• To debug, you must have the Enterprise Edition of Visual Basic 5.0 or later, not the Professional or Standard versions.



• To have all the correct components, you must have SQL Server version 6.5 or SQL Server 6.5 Service Pack 2 or later. SQL Server 6.5 Service Pack 4 ships with Visual Studio 6.0 and is found on Disk Two under the Sql\Servpak directory. You can obtain the latest SQL Server service pack from the following Web address:




http://support.microsoft.com/support

• SQL Server must run under a Windows NT user account, the local Windows NT computer's Administrator user account or group is recommended. For best performance, run the SQL Server under a domain account that is part of the local administrators group. The debugger will not work correctly when executing SQL Server under the local system account.



• Before the distributed component object model (DCOM) protocol was available, Remote Automation was the legacy model used to communicate between platforms. Remote Automation is implementated to faciliate communication between the Visual Basic T-SQL Debugger and SQL Server. To ensure all the Remote Automation components are correctly put in place during installation make sure that the Remote Automation, Connection Designer, Remote Data Objects (RDO) Control, and SQL Debugging are all selected under the Enterprise Features on the client.
  
  NOTE: The T-SQL Debugger that ships with SQL Server 7.0 or Visual Interdev 6.0 is built upon the newer DCOM protocol and mechanisms. To install these components with SQL Server 7.0 make sure that the Debugger interface options presented during setup are selected.



• Install the server side components needed for remote automation. These components are located in the CliSrv\Tsql\SrvSetup directory of your Visual Basic Enterprise Edition version 5.0 CD and under the sqdbg_ss\ directory on the Visual Basic Enterprise Edition version 6.0 CD Two. For Visual Basic 6.0, run the setup.exe found under the sqdbg_ss\ directory. For Visual Basic 5.0, on Windows NT 4.0, run the Sdi_nt4.exe setup program. For a Windows NT Server 3.51 computer, you must manually copy and register the necessary files. For complete instructions on how to do this, please see the Readme.txt file located in the CliSrv\Tsql\SrvSetup directory. After this is complete, make sure that the Sdi.dll file is located in the same directory as Sqlservr.exe.



• You cannot use the Visual Basic Enterprise edition T-SQL Debugger to debug a SQL Server that is using the Sdi50.dll or Sdi60.dll debugging component; the correct Sdi.dll components must be installed as noted previously. Each DLL has a separate interface; hence the unique identifier (UID) used to register this DLL is different than the one used by Sdi.dll.



• Make sure that the remote procedure call (RPC) services are started on the server computer. You can do this by opening Control Panel, double- clicking Services, and checking to make sure the Remote Procedure Call (RPC) Service and Remote Procedure Call (RPC) Locator services are running and set to start automatically.



• If your client computer is running a client that has DCOM capable platform, like Windows 95, Windows 98 and Windows NT 4.0 or later, run DCOMCNFG and make sure that 'everyone' has Execute and Access permissions for the Vbsdicli.exe file. If the client computer is running Windows 95, you can download the DCOM Configuration utility from the following:




http://msdn.microsoft.com/library/default.htm

• If possible, test the T-SQL debugger on the same computer that the SQL Server is installed upon, to make sure it is working correctly.



• Check to see that the computers can connect to one another and that you have permission to use the resources on that Windows NT computer. For example, if the network uses TCP/IP, use the PING utility to confirm that the client can communicate with the SQL Server. You can also test this communication by mapping a drive from the client computer (the one that has Visual Basic installed) to the SQL Server.



• To enable the use of breakpoints during a debugging session with the T-SQL Debugger, use client-side cursors. You can set this option under the Miscellaneous tab on the properties of the UserConnection object.



• If the SQL Server service is stopped and restarted while the Automation Manager continues running on the same computer, you will see an "unexpected error" message in the Windows NT event log on the SQL Server the next time the Automation Manager attempts to connect to SQL Server. This error message will have a source of MSDEVSDI. If this is the case, use Task Manager to stop the Automation Manager (Autmgr32.exe) on the SQL Server and let the Sdi.dll and Autprx32.dll load Autmgr32.exe on the server through the DCOM protocol mechanism.



• Make sure that the remote Automation Manager (Autmgr32.exe) is not started from the command prompt on the client. Allow the Sdi.dll and Autoprx32.dll files to load Autmgr32.exe on the server through the COM service control manager mechanism (rpcss.exe).



• Extended error information may be contained in the Windows NT event log. Specifically, Event ID 7 relates directly to the preceding third and eleventh items. The text for the error follows:

  Event ID #7: An unexpected error (xxxxxxxx) occurred. Debugging will be turned off for connection %. Server execution failed.

  The event entries are made under MSDEVSDI.



• Do not debug on a production server. When stepping through the debugger there are resources that can be locked that others need, therefore adversely affecting other clients performance and operation.



• You can add global variables like @@ERROR to the watch window.



• Image datatypes are not a datatype option to pass as a parameter. The Unassigned Parameters dialog box never displays. You cannot edit or display the text datatype in the watch window.



• You can use the T-SQL Debugger to debug stored procedures without writing code. However, when using the T-SQL Debugger to perform 'run-time debugging' on Visual Basic code, it can be performed on an RDO connection only.



• To configure for run-time debugging, do the following:
  
  
  1. In the Add-In Manager, under the Add-Ins menu, make sure that Visual Basic T-SQL Debugger is available.
  
  
  
  2. On the Visual Basic Tools menu, click T-SQL Debugger Options.
  
  
  
  3. Make sure that the "Automatically step into Stored Procedures through RDO connections" option is enabled.
  
  
  
  4. To use the T-SQL Debugger, you must use a SQL Server ODBC driver version later than 2.65.0203.
  
  
  

REFERENCES

Q166280 DOC: T-SQL Debugger Server Setup Provides Incorrect Location

Q179023 BUG: T-SQL Debugger Hangs When User Has No Execute Permission

Q181854 FILE: Vbc.exe Fixes VB 5.0 Control Installation Problem

Q175868 BUG: T-SQL Debugger Does Not Run on NT with User Account

Q172099 PRB: T-SQL - Running Remote and Local SQL Debugging

Additional query words: trans-sql tran-sql eventlog

Keywords          : kbusage SSrvStProc SSrvTran_SQL 
Version           : winnt:6.5,7.0
Platform          : winnt 
Issue type        : kbinfo 

Last Reviewed: May 6, 1999