SQL Server 2012 : Exploring SQL CLR - Deployment (part 2) - Deploying Your Stored Procedures, Testing Your Stored Procedures

11/12/2013 8:09:40 PM

3. Deploying Your Stored Procedures

In the SQL Server Database 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 methods in the StoredProcedures class (found in the spTest.cs file). 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 method is used as the stored procedure name.

The SqlProcedure attribute is used only by Visual Studio when auto-deploying SQL CLR assemblies. Therefore, it does not appear in 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 EXTERNAL NAME clause to specify the assembly, the fully qualified class name specifier, and the method name. For example, to load the Class Library version of spContacts, you would issue the following command:

AS EXTERNAL NAME SQLCLRDemo.StoredProcedures.spContacts

The preceding command specifies that the spContacts method, found in the class named StoredProcedures, in the loaded assembly with T-SQL name SQLCLRDemo, 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 SSMS project supplied with the chapter’s sample code on the book’s companion website. You will need to run that script in order to utilize the various SQL CLR entities implemented in the Class Library project. Before running the script, you will need to edit the CREATE ASSEMBLY command at the top so that the assembly’s file specification points to the location on your system where the assembly has been installed. By default, the script looks for the assembly in the C:\Demos\VS\SQLCLRDemoManual\SQLCLRDemo\bin\Debug folder on your PC.

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

AS EXTERNAL NAME SQLCLRDemo.[SQLCLRDemo.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 SQL CLR stored procedures. It is important to realize that the class specifier and method 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 may seem perplexing at first, it does make sense. SQL Server searches for your methods 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 within an assembly for a specific class and method.

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 importance! If you attempt to register a method 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.

4. Testing Your Stored Procedures

The TestStoredProcs.sql script file in the SSMS project supplied with this chapter’s sample code will run both CLR stored procedures (spContactCount and spContacts). Open the file in SSMS, and then click the Execute button on the SQL Editor toolbar, choose Execute from the Query menu, press F5, or press Ctrl-E. (You can also right-click anywhere inside the query window and select Execute from the shortcut menu.)

When the script runs, you should see the single-valued result of the spContactCount stored procedure appear first, as shown in Figure 4. Notice 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.Person table has almost 20,000 rows, on certain machines, these results might take some time to retrieve.

TestStoredProcs.sql script code and results.

Figure 4. 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 the Results To Text option in SSMS). After all the rows have been fetched, you should see the “Data Dump Complete” message appear as well.

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 a Visual Studio SQL Server Database Project and use SSDT’s CLR assembly auto-deployment features. You have also learned how to develop SQL CLR code in standard Class Library projects and how to use T-SQL commands to deploy the code for you. You’ve learned about the subtle differences between deploying C#- and Visual Basic .NET-based SQL CLR assemblies, 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.

  •  SQL Server 2012 : Exploring SQL CLR - Your First SQL CLR Stored Procedure, CLR Stored Procedures and Server-Side Data Access
  •  SQL Server 2012 : Exploring SQL CLR - Visual Studio/SQL Server Integration
  •  SQL Server 2008 : DBCC validation - Removing corruption
  •  SQL Server 2008 : Mirroring in action (part 3) - Suspending and resuming mirroring, Initiating failover
  •  SQL Server 2008 : Mirroring in action (part 2) - Monitoring database mirroring
  •  SQL Server 2008 : Mirroring in action (part 1) - Mirroring setup
  •  SQL Server 2008 : High availability with database mirroring - Failover options
  •  Programming ASP.NET 3.5 : Data Source-Based Data Binding (part 4) - Iterative Controls
  •  Programming ASP.NET 3.5 : Data Source-Based Data Binding (part 3) - List Controls
  •  Programming ASP.NET 3.5 : Data Source-Based Data Binding (part 2) - Data-Binding Properties
    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