Programming Microsoft SQL Server 2005 : Deployment (part 2) - Testing Your Stored Procedures

1/29/2011 6:39:58 PM

Deploying Your Stored Procedures

In the SQL Server project version of the sample code, deployment of all the stored procedures is handled by Visual Studio when the assembly itself is deployed. This is due to the application of the SqlProcedure attribute to the functions in class StoredProcedures (found in file spTest.cs). The SqlProcedure attribute accepts an optional Name parameter, the value of which is the actual callable stored procedure name. If you do not supply a value for the Name parameter, the name of the .NET function is used as the stored procedure name.

The SqlProcedure attribute is used only by Visual Studio in SQL Server projects. Therefore, it has been removed from the source code in the Class Library project. Deploying the stored procedures from that version of the source code requires issuing a CREATE PROCEDURE T-SQL command using the new EXTERNAL NAME clause to specify the assembly, fully qualified class name specifier, and function name. For example, to load the Class Library version of spContacts, you would issue the following command.

AS EXTERNAL NAME Chapter03.StoredProcedures.spContacts

The preceding command specifies that function spContacts, in class StoredProcedures, in the loaded assembly with T-SQL name Chapter03, should be registered as a CLR stored procedure callable under the name spContacts.


All necessary CREATE PROCEDURE commands for the Class Library project version of the sample code are contained in the CreateObjects.sql script in the Management Studio project supplied with the sample code. You will need to run that script in order to execute the various SQL CLR entities implemented in the Class Library project.

Note that had the CLR stored procedure been written in Visual Basic .NET rather than C#, the class name specifier would change to Chapter03.StoredProcedures. This would necessitate a change to the deployment T-SQL code as follows:

AS EXTERNAL NAME Chapter03.[Chapter03.StoredProcedures].spContacts

In Visual Basic projects, the default namespace for a project itself defaults to the project name, as does the assembly name. The class within the project must be referenced using the default namespace as a prefix. Because the class specifier is a multipart dot-separated name, it must be enclosed within square brackets so that SQL Server can identify it as a single indivisible name. Because C# projects handle the default namespace setting a little differently, the namespace prefix is not used in the class specifier for C# assemblies.

One last point before we discuss how to test your now-deployed CLR stored procedures. It is important to realize that the class specifier and function name in the EXTERNAL NAME clause are case-sensitive and that this is true even for assemblies developed in Visual Basic .NET. Although this point perplexed us quite a bit at first, it does make sense in hindsight. SQL Server searches for your subs/functions within your assemblies, not within your source code. In other words, it’s looking within Microsoft Intermediate Language (MSIL) code, not Visual Basic .NET or C# source code. Because MSIL is case-sensitive (it has to be, to support case-sensitive languages like C#), SQL Server must be as well as it searches for a specific class and sub/function.

The fact that SQL Server is not case sensitive by default (even though it once was) and that Visual Basic .NET is not a case-sensitive language is of no import! If you attempt to register a sub/function and you receive an error that it cannot be found within the assembly, double-check that the case usage in your command matches that of your source code.

Testing Your Stored Procedures

With your assembly and stored procedures now deployed, you’re ready to run and test them. Typically, you should do this from Management Studio; however, Visual Studio SQL Server projects allow you to test your SQL CLR code from Visual Studio itself. When you create a Visual Studio SQL Server project, a folder called Test Scripts is created as a subdirectory in your source code directory. Within that subdirectory, Visual Studio creates a script file called Test.sql. If you look at that file, you will see that it contains commented instructions as well as commented sample T-SQL code for testing stored procedures, functions, and UDTs. It also contains an uncommented generic SELECT command that echoes a text literal to the caller.

Visual Studio connects to your database and runs this script immediately after your assembly is deployed, and the output from the script appears in Visual Studio’s Output window. This allows you to execute any number of T-SQL commands directly from Visual Studio without having to switch to another tool. Although this approach is much less interactive than a Management Studio query window, it allows you to run quick tests against your code. It is especially useful for regression testing—that is, confirming that a new version of your assembly does not break older, critical functionality.

The file extension of the script must be .sql, but otherwise the name of the file is inconsequential. You can have multiple script files in the Test Scripts folder. To add a new one, right-click the Test Scripts folder node or the project node in the Solution Explorer window and select the Add Test Script option from the shortcut menu. Only one script can be active at one time, and as soon as you have more than one script, you must specify which one is active. To make a script active, simply right-click its node in the Solution Explorer window and select the Set As Default Debug Script option from its shortcut menu. When you do so, the node is displayed in bold. You may run or debug a script even if it is not the active script. To do so, right-click its node in the Solution Explorer window and select the Debug Script option from its shortcut menu.


At press time, there appears to be an anomaly in the working of the test script facility and the Output window in Visual Studio 2005. Simply put, if your test script executes a query (whether it be a T-SQL SELECT command or a call to a stored procedure) that returns a column of type uniqueidentifier (GUID), the query’s result set will not appear in the Output window, and execution of the test script might hang Visual Studio. For this reason, you should avoid calling the sample code CLR stored procedures spContactsQuick and spContactsSELECT * FROM Person.Contact query and thus retrieve the rowguid column, which is of type uniqueidentifier) from your test script and instead test these procedures from SQL Server Management Studio, where the anomaly does not occur. You can safely call spContactCount, which simply performs a SELECT COUNT(*) FROM Person.Contact query, from your Visual Studio test script. Alternatively, you can modify spContactsQuick and/or spContacts to select specific columns from the Person.Contact table, making sure that rowguid is not one of them. (both of which perform a

If you’re working with the Class Library version of the sample code, you must test the stored procedures from Management Studio or another SQL Server query tool. Even if you are working with the SQL Server project version, you’ll find that testing your SQL CLR code in Management Studio provides a richer experience and more flexibility.

The script file TestStoredProcs.sql in the Management Studio project supplied with the sample code will run both of our CLR stored procedures (spContactCountspContacts). Open the file in Management Studio, and click the Execute button on the SQL Editor toolbar, choose the Query/Execute option on the main menu, or press F5. (You can also right-click the query window and select Execute from the shortcut menu.) and

When the script runs, you should see the single-valued result of the spContactCount stored procedure appear first, as shown in Figure 2. Note that the column name ContactCount appears on the Results tab and recall that this is a direct result of your using the SqlMetaData object in the CLR code. Below the spContactCount result, you will see the results from the spContacts stored procedure come in. Because the Person.Contact table has almost 20,000 rows, these results might take some time to flow in.

Figure 2. TestStoredProcs.sql script code and results

Even while the results are coming in, the “Starting data dump” status message should be visible on the Messages tab (or on the Results tab if you’re using Management Studio’s Results To Text option). Once all rows have been fetched, you should see the “Data dump complete” message appear as well. If you get impatient and want to abort the query before all rows have been fetched, you can use the Cancel Executing Query button on the SQL Editor toolbar or the Query/Cancel Executing Query option on the main menu; you can also use the Alt+Break keyboard shortcut.

We have yet to cover CLR functions, triggers, aggregates, and UDTs, but you have already learned most of the skills you need to develop SQL CLR code. You have learned how to create Visual Studio SQL Server projects and use its autodeployment and test script features. You have also learned how to develop SQL CLR code in standard Class Library projects and to use T-SQL commands and Management Studio to deploy the code for you. You’ve learned about the subtle differences between deploying C# code and Visual Basic .NET code, and we’ve covered the case-sensitive requirements of T-SQL-based deployment.

With all this under your belt, we can cover the remaining four basic SQL CLR entities relatively quickly.

  •  Programming Microsoft SQL Server 2005 : CLR Stored Procedures and Server-Side Data Access
  •  SQL Server 2008 : Explaining Advanced Query Techniques - Controlling Execution Plans (part 3) - Using the Resource Governor
  •  SQL Server 2008 : Explaining Advanced Query Techniques - Controlling Execution Plans (part 2)
  •  SQL Server 2008 : Explaining Advanced Query Techniques - Controlling Execution Plans (part 1)
  •  Reporting Services with SQL Azure : Deploying the Report & Creating a Subreport
  •  Reporting Services with SQL Azure : Creating the Report Design
  •  SQL Server 2008 : Explaining Advanced Query Techniques - Applying Ranking Functions (part 2) - Using RANK, DENSE_RANK and NTILE
  •  SQL Server 2008 : Explaining Advanced Query Techniques - Applying Ranking Functions (part 1) - Using ROW_NUMBER
  •  SQL Server 2008 : Demystifying Data Types - Computed Columns
  •  Programming Microsoft SQL Server 2005: Overview of SQL CLR - Visual Studio/SQL Server Integration
    Top 10
    SG50 Ferrari F12berlinetta : Prancing Horse for Lion City's 50th
    The latest Audi TT : New angles for TT
    Era of million-dollar luxury cars
    Game Review : Hearthstone - Blackrock Mountain
    Game Review : Battlefield Hardline
    Google Chromecast
    Keyboards for Apple iPad Air 2 (part 3) - Logitech Ultrathin Keyboard Cover for iPad Air 2
    Keyboards for Apple iPad Air 2 (part 2) - Zagg Slim Book for iPad Air 2
    Keyboards for Apple iPad Air 2 (part 1) - Belkin Qode Ultimate Pro Keyboard Case for iPad Air 2
    Michael Kors Designs Stylish Tech Products for Women
    - First look: Apple Watch

    - 3 Tips for Maintaining Your Cell Phone Battery (part 1)

    - 3 Tips for Maintaining Your Cell Phone Battery (part 2)
    Popular Tags
    Video Tutorail Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Exchange Server Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe Photoshop CorelDRAW X5 CorelDraw 10 windows Phone 7 windows Phone 8 Iphone