Speed up SQL script deployment

Problem
One of the nice things about developing your SQL Server CLR code in Visual Studio is that you get to take advantage of the debugging aspects of the tool.  When developing it is always beneficial to use a debugger and step through your code to find any coding problems, but how is this done when writing CLR code for SQL Server?

Solution
In order to take advantage of the debugging aspects for your SQL Server CLR code you need to be using either the Professional or Team Edition of Visual Studio.  The following table outlines the edition and which languages you can use the debugger.

In addition to  having the correct version of Visual Studio and one of the supported languages you also need to make sure the CLR is enabled.  Here is a tip that refers to this http://www.mssqltips.com/tip.asp?tip=1344.

It is important that you know that when you are debugging this will cause all other threads to stop as well, so this is why it is key that you do not try to debug on a production server.

Once you are set you need to enable the debugging for the connection using the "Server Explorer" in Visual Studio as shown below.  Server Explorer can be found under the "View" menu item in Visual Studio.

 When you enable debugging you will get this message as mentioned above.

Once you have the CLR enabled for SQL Server and the connection has been enabled for debugging you can begin the debug process. 

There are three ways you can debug CLR code:

1. Stepping into a SQL Server 2005 database object using Server Explorer
2. Running a test script using a Visual Studio 2005 SQL Server project
3. Running an application that calls a stored procedure

Process 1 - Stepping into an object

If you have already deployed your CLR code to your SQL Server you can browse for the CLR function or SP, right click on it and select "Step Into Function" or "Step Into Stored Procedure" as shown below for the function we are debugging.  This is the same function listed in this previous tip http://www.mssqltips.com/tip.asp?tip=1344.

This will execute the function or stored procedure and will pop up a window so you can enter in values for the parameters as shown below.

After you enter the value and click "OK" the debugging process will begin.  The first place it will stop is at the beginning of the function code whether you use a breakpoint or not as shown below.

At this point we can step through each line of the code using the debug controls toolbar as shown below or by using the options in the "Debug" menu.

In the Locals window you can see the variable values as they change as shown below. In addition, you can take advantage of other debugging features as well.

Process 2 - Using a test script

Another way is to create a test script and then debug the test script.  The test scripts get created in your solution in the "Test Scripts" folder.

Here is what the code in our Test1.sql script looks like. This is going to run the sortString function with the values shown below.

After you have set your breakpoints, to start the debug process right click on the test script and select "Debug Script".  One thing to note for this approach is that you have to set breakpoints otherwise the debugger will not stop it will just execute all of the code.  This is one difference from Process 1.

Once the debug process starts it will stop at the first breakpoint as shown below.

At this point you can step through each line of code and see the values as they change.

Process 3 - Test with an application

In this process you are debugging your application. If the application calls the SQL Server CLR code and you have breakpoints setup you can debug the code.

Next Steps

• Although this is not a comprehensive end to end process for coding and debugging it should hopefully give you enough information to begin debugging your code
• To debug a trigger you can create a test script that does an insert, update or delete against your table and it will allow you to debug your trigger code as long as you have setup breakpoints.  Another way this could be done is to debug a stored procedure which in turn fires the CLR trigger.

• Send Email from SQL Server Express Using a CLR Stored Procedure
• SQL Server CLR function to concatenate values in a column
• SQL Server CLR function to improve performance of validating email addresses
• SQL Server CLR and T-SQL functions to parse a delimited string
• Writing to an operating system file using the SQL Server SQLCLR
• More...

• Discuss this tip: http://blogs.mssqltips.com/forums/t/984.aspx
• There are 1 comments for this tip, last post by sgperlman