Debugging Extended Procedures

for RuBoard

You debug extended stored procedures the same way you debug any other DLL-based routineby supplying a host application that loads the DLL and placing a breakpoint in the routine you wish to debug. In the case of extended procedures, the host application is SQL Server itself.

You can't debug extended procedures using the service version of SQL Server, so you have to use its console version instead. How do you start SQL Server as a console application? First, shut down the SQL Server service, then run sqlservr .exe (in the SQL Server BINN folder) directly and pass -c as a parameter. Additionally, if you wish to start a named instance of SQL Server, specify the -s command-line parameter and specify the name of the instance you wish to start.

NOTE

This section assumes that you're using Microsoft's Visual C++ to code and debug your extended procedures. If you're using some other language tool, you'll need to set it up to debug your extended procedure as a DLL using sqlservr.exe as its host executable. Most modern Windows development tools allow you to debug DLLs and specify alternate host executables.

You set up SQL Server as the host application for your DLL via a setting in your development environment. If you're using Visual C++, find the edit box titled Executable for debug session: on the Debug tab in the Project Settings dialog. Key in the full path to sqlservr.exe, along with the -c parameter. Include the -s parameter if necessary.

Place a breakpoint in your extended procedure code, then "run" your application. You should see SQL Server start in a console window. Once it has finished starting up (you can determine this by watching for the Recovery Complete message in the console window), open a Query Analyzer window and run your extended procedure. You should see execution stop at your breakpoint in Visual C++. From here, you can debug your extended procedure just as you would any other routine. You can step through the code line by line, trace into subroutines, view watch variables , and terminate the debugging session whenever you like.

NOTE

If you wish to debug using a named instance of SQL Server, be sure to include the -s parameter followed by the name of the instance (don't include the machine name) you wish to start. Failing to do this can cause a spurious error message to display indicating that your SQL Server installation is corrupt. If you see this message when you start SQL Server as a console application, check to see whether you've specified the -s option correctly.

for RuBoard