SQL Philosopher

Once we have setup our environment properly, written the code we are wanting to deploy, and decided how we are going to handle code security for SQL CLR, the remaining steps are very straightforward.  Unless we are referencing other libraries that are not part of .NET, but that will be discussed in detail later.

Problem with the master database

Before we deploy, there is one error that you may run into on Visual Studio 2010, if you attempt to deploy your code directly to the “master” database.  If when you deploy to master, you receive the following error in the “Build” Output:

Deploy error SQL01268: .Net SqlClient Data Provider: Msg 15657, Level 16, State 1, Procedure sp_db_vardecimal_storage_format, Line 65 Vardecimal storage format is not available in system database 'master'.

This is due to the deploy script attempting to enable the vardecimal storage format on master, but you cannot perform this action in master. So, to work around this problem, you will either have to deploy to a different database, or you can implement a new “Database Project” that references your SQL Server CLR Object.  This process is described in detail on MSDN, so I will not repeat it.

Deploy Your Code

If you have .NET setup properly, all you have to do to deploy your code is right-click on the project in Solution Explorer and click “Deploy”.  If you haven’t already built your code, it will build (compile) it before deploying the code to SQL Server:

You should be able to see the results of your deployment in the “Output” pane within Visual Studio. Toward the end of this output, hopefully you will see something like:

Build succeeded.

Time Elapsed 00:00:06.84
========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ==========
========== Deploy: 1 succeeded, 0 failed, 0 skipped ==========

Testing Your Code

At this point, your SQL CLR stored procedure should be deployed and ready to use on your SQL Server database.  Connect to SQL Server through SSMS and connect to the database to which you deployed your code.

I have created a very simple Test that you can use to test your code:

DECLARE @isMatched	BIT,
		@pattern	VARCHAR(256) = '([A-Za-z0-9\-]+)',
		@testString	VARCHAR(256) = 'some text string'
 
EXEC @isMatched = RegExTestMatch @pattern, @testString
 
SELECT @isMatched
 
SET @pattern = '([0-9\-]+)'
 
EXEC @isMatched = RegExTestMatch @pattern, @testString
 
SELECT @isMatched

Assuming that you get no errors, the first Execution should return a “1” because the test String matches the Regular Expression. The second Execution should return a “0” because the test String does not match the Regular Expression defined.

I will be adding some more complex objects soon, but in the meantime, I hope that this short tutorial gives you an insight into how SQL CLR can improve your environment.

This post is part of a series of blog posts entitled SQL CLR: An Introduction, the other posts are as follows:

1. The Origination and Uses of SQL CLR
2. Infrastructure Requirements and Configuration
3. Writing a Simple CLR Stored Procedure
4. Security and Signing
5. Deployment and Usage
6. Table-Valued Functions and Other SQL CLR (Coming Soon)
7. Incorporating Existing Libraries (Coming Soon)