Article ID: 156096 - Last Review: October 13, 2003 - Revision: 4.2

INF: Tips for Debugging Stored Procedures from VC++

View products that this article applies to.

This article was previously published under Q156096

SUMMARY

With Visual C/C++ 4.2 Enterprise Edition or later you can debug SQL Server stored procedures and triggers. This article contains a list of assorted tips that might help with debugging. At the end of the article, there is an excerpt from the Readme.txt file.

MORE INFORMATION

Text, numeric, and float data types cannot be edited in a watch window.
You must have the Enterprise Edition of Visual C/C++ 4.2 or later, not Professional or Standard, to debug.
You must apply Service Pack 4 for Windows NT 3.51 computers running version SQL Server 6.5. Computers running SQL Server 7.0, Windows NT version 4.0 must have NT Service Pack 4 or later.
Before the DCOM protocol was available, Remote Automation was the legacy model used to communicate between platforms. This implementation of automation allows other DBMSs to be debugged by the T-SQL debugger, such as an Oracle Server, provided the vendor implements or exposes the proper interfaces. Remote Automation is implemented to facilitate communication between the Visual C++ T-SQL Debugger and SQL Server. To ensure all the components are correctly put in place during installation, ensure 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, ensure that the Debugger interface options presented during Microsoft SQL Server Setup are selected.
The retail version of SQL Server 6.5 will not work with the T-SQL debugger. For best behavior, apply the latest Service Pack for SQL Server 6.5, which can be located at:
http://technet.microsoft.com/en-us/sqlserver/bb972143.aspx (http://technet.microsoft.com/en-us/sqlserver/bb972143.aspx)
SQL Server must run under a user account, preferably the local Administrator's user account. When run under the local account, the breakpoints are ignored and the query is simply executed.
Extended error information may be contained in the Windows NT Eventlog. Specifically, Event ID 11 relates directly to executing the SQL Server under the system account:
Event ID #11: SQL Server when started as service must not log on as System Account. Reset to logon as user account using Control Panel.
The event entries are made under MSDEVSDI.
Best performance when debugging is achieved if the SQL Server is running under the same user account with which you are logged on to the Windows NT computer. A quick way to assure this is to start SQL Server in a Windows NT command prompt, as follows:
..\<SQL>\BINN\SQLSERVR -c
Starting the SQL Server with this method will enable the SQL Server to interact with the desktop and any error message will be seen by the logged in user.
It is not recommended that you debug on a production server. Due to the added overhead and break-in nature of the debugging product, you could adversely effect other users.
String and text values longer than 255 bytes are shown as NULL in the watch window.
Use the alternate-mouse button, and select the Refresh option to obtain object changes in DDL from other clients.
Dump table does not perform the Transact-SQL dump command but performs a select * from the selected table. Be careful, however; many results can be written to the output window. For large tables you should use a standard SQL Server query window.
If you delete all characters from a string in the watch window, the value will show NULL. If the variable does not allow NULL the next step operation will reset the value to its previous value.
Only the first 64 bytes of a text column are displayed in the output window, even if more data is contained in the actual column.
You can add global variables like @@ERROR to the watch window.
You must use the DBLIBRARY version 6.50.212, the SQL Server ODBC 2.50.0212 or later version to be able to step from source code to stored procedure code and back.
If installing for VC 4.2, you must apply the SQL Server 6.5 Service Pack to get the proper SDI debugging components. If installing from Visual C++ 5.0 or later, you should install the "VC++ Enterprise Edition Server Components" from the installation CD number 2.

NOTE: For more details, be sure to read the Readme.txt file shipped with Visual C/C++ The following is an important excerpt from the Readme file:

SQL Server Debugging must be enabled. To enable SQL Server debugging, check the SQL Server Debugging check box on the SQL tab of the Options dialog box. The Options dialog box is available by choosing the Options command on the Tools menu. If using VC++ 5.0 or 6.0 the tab is "Data View" instead of SQL.

The Remote Connection must be set to Local, not to TCP/IP or to Serial. To set the Remote Connection, choose the Remote Connection command on the Tools menu.

The current project must be of type x86 (not Macintosh). To set the current project type, choose the Configurations command from the Build menu.

The stored procedure must be stored on SQL Server version 6.5 or later.