SQL Philosopher

To create and execute SQL CLR procedures (as well as other objects), there are several prerequisites that must first be met.  We are going to need:

• Visual Studio (I’ll be using Visual Studio 2010 Ultimate)
• A SQL Server Instance (I’ll be using SQL Server 2008 R2)
• The .NET framework (For SQL Server 2008 R2, I’ll be using .NET 3.5)

These three components give us an application in which we can write and compile code, a database engine to host the code we will write, and the CLR we need to execute the code.

Configuration

The configuration steps for each component are simple, but I want to review them quickly here.  You may need to take particular consideration to setting up Visual Studio with your environment’s source control, but I will not be covering that in this series.  Each shop implements their source control a little differently and it would be best to stay consistent with the rest of your development endeavors.

SQL Server

For basic SQL CLR procedures, the only configuration needed on SQL Server is to enable CLR. I have included the script from MSDN here, for quick reference:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

.NET Framework

To enable the .NET Framework on the SQL Server, you will need to install the .NET 3.5 Framework (available from Microsoft).  If you are on Windows 2008 or Windows 2008 R2, you will have to Enable the Application Server Role first.  To enable the Application Server Role, you simply open “Server Manager”, right-click on “Roles” and select “Add Roles”.  Then select “Application Server” from the “Server Roles” list in the “Add Roles Wizard” dialog box, and click through the rest of the wizard to complete installation:

Visual Studio

Lastly, we need to configure Visual Studio.  After we have Visual Studio installed, we can open it up and Create a New Project.  In the “New Project” Dialog box, select “Database” and then “SQL Server” from the Left pane, called “Installed Templates”.  In the center pane, select “Visual C# SQL CLR Database Project”.  I suggest C# just because it is the language I prefer and also the language I will be writing all future examples in, but you can also choose Visual Basic, if that suits you better.  At the top of the dialog, change the .NET Framework to 3.5 (this can be changed later, if needed), give the project a name, and click “OK” to create your project:

Now that we have an open project, we need to setup our connection to the database we will be using.  In the Solution Explorer pane of Visual Studio, right-click on the project you created, and select “Properties”. Find the “Database” tab, click on it, and then click the “Browse…” button next to the “Connection String” field.  A small dialog will come up, allowing you to chose an existing “Database Reference” or add a new one, if needed.  Select the appropriate database reference, and close out of this dialog:

Lastly, we need to add a class that will contain the object(s) that we will be creating.  For example, we can create a “Stored Procedure” class by right-clicking on the Project in the Solution Explorer, and then select “Add”, and then “New Item…”.  This brings up the “Add New Item” dialog box, where we can select “Stored Procedure”.  Select that item, give it a name, and click the “Add” button:

Now we have an environment that is ready to start creating SQL CLR objects.

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)