Walkthrough: Debug an Extended Stored Procedure 

This topic applies to:

Visual Studio Edition

Visual Basic

C#

C++

J#

Visual Web Developer

Express

No

No

No

No

No

Standard

No

No

No

No

No

Pro/Team

Yes

Yes

Yes

Yes

Yes

Warning

Extended stored procedures have been deprecated. Although they continue to be supported for backward compatibility in ASP.NET, that support will be removed in a future release.

Extended stored procedures were introduced to let developers perform actions not possible in T-SQL, such as accessing the file system, reading the registry, and so on. Because you can do these things by writing SQL CLR procedures, the requirement to write these procedures is now gone. It is strongly recommended that you not write new Extended Stored Procedures, and consider replacing existing ones with equivalent, but safer, SQL CLR stored procedures.

Extended stored procedures are actually DLLs that are written in C++, or any other language, and are not SQL-based. Therefore, debugging an extended stored procedure is much like debugging any application in C++, or whatever language the extended stored procedure is written in.

Warning

Extended Stored Procedures are written in native C++, and so lack the protections afforded by managed code. They should be very carefully tested because bugs have the potential to crash SQL Server. To avoid potential loss of data and other problems, do not debug an extended stored procedure on a computer that is running as a production server. For more information, see ASP.NET Books Online.

Note

The dialog boxes and menu commands you see might differ from those described in Help depending on your active settings or edition. To change your settings, choose Import and Export Settings on the Tools menu. For more information, see Visual Studio Settings.

To debug an extended stored procedure

  1. Create a debug build of an extended stored procedure DLL. For more information, see the SQL Server Books On-Line.

  2. You need an application that calls the extended stored procedure you want to debug. If you do not have one ready, you can:

    • Create a Visual Studio database project with a data connection for the database that contains the extended stored procedure and code to call the extended stored procedure.

    • Create a SQL script file that calls the extended stored procedure.

      -or-

    • Use an application such as ISQL/W, included with SQL Server, or ODBC Test, included with ODBC SDK.

  3. If SQL Server is currently running as a service, stop it by opening the Services control panel, selecting SQL Server, and clicking Stop.

  4. Copy the debug version of the DLL to the directory where sqlservr.exe resides or to any directory in the search path.

    -or-

    Set a Post-Build event to copy the DLL in your C++ project as follows:

    1. Open the <Project> Property Pages dialog box.

    2. In the <Project> Property Pages dialog box, open the Configuration Properties folder.

    3. Under Configuration Properties, open the Build Events folder.

    4. Select Post-Build Event.

    5. In the grid control, next to Command Line, enter a copy command, such as the following example:

      Copy c:\MyProjects\MyXProc\debug\MyXProc.dll C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn

  5. Register the extended stored procedure.

  6. Specify SQL Server as the calling executable and working directory for the extended stored procedure DLL. Modify the settings in the <Project> Project Properties dialog box, which you can access in the Debugging category of Configuration Properties, as follows:

    • If SQL Server was installed in the default location, type C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\SQLSERVR.EXE in the Command box.

    • Set the Working directory entry to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn.

    • Set the Command 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, which will cause SQL Server to start faster. SQL Server will start not as a service but as a console application under the control of the Visual Studio debugging environment. This will allow breakpoints to be correctly trapped and handled.

  7. Set breakpoints in the source code of the extended stored procedure.

  8. Use an execution command to start the debugging session. For more information, see Execution Control.

    A console window will appear as SQL Server starts. When text scrolling stops, the last message will say:

    Launched startup procedure 'sp_sqlregister'

    SQL Server starts and starts to process requests.

  9. Execute the extended stored procedure.

    The debugger breaks when it reaches a line that contains a breakpoint.

    For additional tips about how to write extended stored procedures, consult the SQL Server section of the Microsoft Knowledge Base or the Programming Extended Stored Procedures section of the SQL Server documentation in the MSDN Library.

See Also

Concepts

Debugging SQL