Debugging CLR database objects

One of the coolest features found in the integration of the .NET Framework, Visual Studio 2005, and SQL Server 2005 is the ability to debug the CLR database objects that you create. This tight level of integration sets SQL Server way ahead of competing database products like Oracle and DB2 that offer the ability to create stored procedures and functions using .NET code. While the other database products provide for the creation of these objects, they do not support the ability to provide integrated debugging. Visual Studio 2005 enables you to set breakpoints in your CLR database objects and then seamlessly step through your code and perform all of the debugging tasks that you would expects for a standard Windows or Web application, including the ability to set breakpoints, single-step through the code, inspect and change variables, and create watches—even between T-SQL and CLR code. Visual Studio 2005 automatically generates test scripts that are added to your projects. You can customize and use these test scripts to execute the CLR database objects that you create.

To debug a SQL Server project using Visual Studio 2005, first open the project that you want to debug and then go to the Servers window and right-click the database connection. From the pop-up menu select the option Allow SQL/CLR Debugging as is shown in Figure 3-13.


Figure 3-13: Setting the Allow SQL/CLR Debugging option

Next, set up the script that you want to use to run the database

    Requires Free Membership to View

    Login

object. Using the Solution window, open the Test Scripts folder and then the Test.sql file. You can set up multiple test scripts, but the Test.sql script is provided by default. If you want to change the script that Visual Studio 2005 uses to run the CLR database object, you simply right-click the desired script listed under the Test Scripts folder and select the Set As Default Debug Script option as is shown in Figure 3-14.


Figure 3-14: Setting the default debug script

To use the default Test.sql script, open the file using the Visual Studio editor. Here you can see T-SQL boilerplate code for testing each of the different CLR database object types. Go to the section that you want and edit the code to execute the database object. You can see the test code for the usp_ImportFile stored procedure in the following listing: 

-- Examples for queries that exercise different SQL objects
  -- implemented by this assembly
-----------------------------------------------------------------
-- Stored procedure
-----------------------------------------------------------------
declare @MyColumn varchar(30)
exec usp_ImportFile 'c:temptestfile.txt',@MyColumn
Select @MyColumn

When the test script is ready to go, use Visual Studio's Debug | Start option or simply press F5 to launch the Test.sql that will execute your CLR database object. You can see an example of using the Visual Studio 2005 debugger to step through a SQL Server project in Figure 3-15.


Figure 3-15: Debugging Visual Studio 2005 SQL Server projects

At this point you can step through the code, set new breakpoints, and change and inspect variables.

The previous tip is from "Developing CLR database objects: 10 tips in 10 minutes," excerpted from Chapter 3 of the book "Microsoft SQL Server 2005: A Developer's Guide" written by Michael Otey and Denielle Otey, courtesy of McGraw-Hill Publishing.


Developing CLR database objects

 Home: Introduction
 Tip 1: CLR architecture
 Tip 2: CLR assemblies in SQL Server 2005
 Tip 3: Creating CLR database objects
 Tip 4: CLR stored procedures
 Tip 5: User-Defined Functions
 Tip 6: CLR triggers
 Tip 7: User-Defined Types
 Tip 8: CLR aggregates
 Tip 9: Debugging CLR database objects
 Tip 10: .NET database object security

This was first published in May 2007

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top