Debugging Extended Stored Procedures

Caution You should not debug an extended stored procedure on a computer that is running as a production server.

To debug an extended stored procedure, you must have the following products installed:

• SQL Server 6.5
  
  
• Windows NT (server or workstation)
  
  
• Visual C++

To debug an extended stored procedure

1. Use Visual C++ to create a debug build of an extended stored procedure DLL.
   
   
2. Stop SQL Server if it is currently running as a service.  Open the Services tool in Control Panel, highlight MSSQLSERVER and click Stop.
   
   
3. Copy the debug version of the DLL to the directory where sqlservr.exe resides or to any directory in the search path. 

   Tip  You can use the Post-build step tab in the Project Settings dialog box to copy the DLL.  Type in a command to copy the dll, for example, Copy c:\MyProjects\MyXProc\debug\MyXProc.dll c:\mssql\binn.

4. Register the extended stored procedure.
   
   
5. Specify SQL Server as the calling executable and working directory for the extended stored procedure DLL.  Modify the settings in the Debug tab of the Project Settings dialog box as follows:
   • If SQL Server was installed in the default location, type C:\MSSQL\BINN\SQLSERVR.EXE in the Executable for debug session box.
     
     
   • Set the Working directory entry to C:\MSSQL\BINN.
     
     
   • Set the Program arguments entry to -c.  The -c option indicates to SQL Server that it is being started from the command line and not as a service.  This will cause SQL Server to start up faster.

   This will start the SQL Server not as a service but as a console application under the control of the Visual C++ debugging environment. This will allow breakpoints to be properly trapped and handled.

6. Set breakpoints in the source code of the extended stored procedure and begin the debugging session.

   A console window will appear as SQL Server starts. When text scrolling stops (the last messsage will say  “Launched startup procedure 'sp_sqlregister'”), SQL Server will be started and ready to process requests.

7. Execute the extended stored procedure.  You can do this is one of three ways:
   • Start Visual C++ Enterprise Edition and create a database project with a data connection for the database that contains the extended stored procedure.  Create an SQL script file that calls the extended stored procedure.
     
     
   • Call the extended stored procedure from your own application.
     
     
   • Use any application that allows you to call extended stored procedures. For example, ISQL/W (included with SQL Server) or ODBC Test (included with ODBC SDK).

   The VC++ debugger will break when a line containing a breakpoint is executed.

For specific tips on writing extended stored procedures, consult the SQL Server section of the Microsoft Knowledge Base.

See Also  Debugging DLLs, Creating an Extended Stored Procedure.