Using CLR Integration in SQL Server 2008
Lab version: 1.0.0
Last updated: 11/10/2011
OVERVIEW ................................................................................................................................................... 3
EXERCISE 1: CREATING USER-DEFINED FUNCTIONS USING SQL CLR ............................................ 5
Task 1 - Enabling CLR Integration in SQL Server 2008 .......................................................................... 5
Task 2 - Creating a SQL Server Project in Visual Studio 2008 ............................................................... 6
Task 3 - Reviewing the Generated Project .......................................................................................... 10
Task 4 - Adding a User-Defined Function to the Project..................................................................... 13
EXERCISE 2: DEBUGGING SQL CLR OBJECTS ................................................................................... 17
Task 1 - Setting up a Debugging Session in Visual Studio ................................................................... 17
Task 2 - Using Test Scripts to Debug SQL CLR Objects ........................................................................ 19
Task 3 - Attaching to the SQL Server Process to Debug SQL CLR Objects........................................... 25
Task 4 - Debugging SQL CLR Code without a Project .......................................................................... 28
SUMMARY .................................................................................................................................................. 30
SQL Server integration with the .NET Framework common language runtime (CLR) enables database
programmers to implement functions, stored procedures, triggers, data types, and aggregates using
managed code. The CLR provides cross-language integration, code access security, object lifetime
management, and debugging and profiling support.
Transact-SQL is specifically designed for direct data access and manipulation in the database. While
Transact-SQL excels at data access and management, it is not a full-fledged programming language. For
example, Transact-SQL does not support arrays, collections, for-each loops, bit shifting, or classes. While
some of these constructs can be simulated in Transact-SQL, managed code has integrated support for
Database programmers have two alternatives for implementing their database objects. Transact-SQL is
an appropriate choice when code will mostly perform data access with little or no procedural logic.
Managed code is better suited for calculations and complicated execution logic, and features extensive
support for many complex tasks, including string handling and regular expressions.
This hands-on lab introduces you to writing CLR objects using managed code. You will learn how to
create SQL CLR user-defined functions using Visual Studio and how to deploy them to SQL Server. You
will also explore different alternatives for debugging your SQL CLR managed code.
In this Hands-On Lab, you will learn how to:
Enable CLR integration in SQL Server
Use Visual Studio to implement a SQL CLR user-defined function
Automatically deploy SQL CLR database objects and source code to SQL Server
Debug SQL CLR database objects in Visual Studio
You must have the following items to complete this lab:
Microsoft SQL Server 2008 R2
Microsoft Visual Studio 2008 Professional or Team editions
All the requisites for this lab are verified using the Configuration Wizard. To make sure that everything is
correctly configured, follow these steps.
Note: To perform the setup steps you need to run the scripts in a command window with
1. Launch the Configuration Wizard for this Lab by running the Setup.cmd script located under the
Setup folder in the Source folder of this lab. Install any pre-requisites that are missing
(rescanning if necessary) and complete the wizard.
Note: For convenience, much of the code you will be managing along this lab is available as
Visual Studio code snippets. The Setup.cmd file launches the Visual Studio installer file that
installs the code snippets.
The Configuration Wizard also creates the SQLTrainingKitDB database that you will use along
the Lab and the 'SQLServerTrainingKitAlias' sever alias used to establish a connection with the
The following exercises make up this Hands-On Lab:
2. Creating User-Defined Functions Using SQL CLR
3. Debugging SQL CLR Objects
Note: The source code that accompanies this hands-on lab includes an end folder where you can find a
Visual Studio solution with the code that you would obtain if you complete the steps in each exercise.
You can use this solution as a guide if you need additional help working through the exercises.
Estimated time to complete this lab: 60 minutes.
Exercise 1: Creating User-Defined
Functions Using SQL CLR
In this exercise, you will create a Visual Studio SQL Server project and implement a user-defined function
using managed code. In addition, you use automatic deployment in Visual Studio to publish the
assembly to SQL Server.
Task 1 - Enabling CLR Integration in SQL Server 2008
The common language runtime (CLR) integration feature is off by default. You must enable it in order to
use objects that are implemented using CLR integration. In this task, you will enable CLR integration
using a stored procedure named sp_configure.
1. Start Microsoft SQL Server Management Studio from Start | All Programs | Microsoft SQL
Server 2008 R2.
2. In the Connect to Server dialog, type SqlServerTrainingKit (the server alias) in the Server name
Connecting to the SQL Server instance
3. Press CTRL+N to open a new query window.
4. Enable SQL Server to execute CLR code. To do this, enter the following Transact-SQL script in
the query window.
sp_configure 'show advanced options', 1;
sp_configure 'clr enabled', 1;
Note: The 'clr enable' option is an advance option so you first have to configure SQL Server to
show advanced options with the 'show advanced options' option.
5. Press CTRL+E to execute the Transact-SQL statements in the query window and enable CLR
Enabling CLR integration in SQL Server
Task 2 - Creating a SQL Server Project in Visual Studio 2008
In this task, you will create a new SQL Server project in Visual Studio to implement CLR database objects.
You will use this project for the remainder of the lab to explore CLR integration features in SQL Server
1. Start Microsoft Visual Studio 2008 from Start | All Programs | Microsoft Visual Studio 2008.
Make sure that the account used to start Visual Studio is a member of the sysadmin role in SQL
Note: To debug on SQL Server, both the account running Visual Studio and the account used to
connect to SQL Server must be members of the sysadmin role. By default, the administrator
account is a member of this role. To launch Visual Studio as an administrator, right-click the
program shortcut on the start menu and select Run as administrator.
For more information, see How to: Create a Database User.
2. Create a new SQL Server project. To do this, in the File menu, select New and then Project. In
the New Project dialog, expand the Visual C# project type node, select Database from the list of
categories and choose the SQL Server Project template. (If this template is missing, ensure that
.NET Framework 3.5 is selected in the drop-down list on the top right of the dialog box).
3. Enter SqlClassLibrary as the name of the new project, set the Location to the Ex1-
CreatingFunctions folder inside the Source folder of this lab, set the Solution Name to Begin,
check Create directory for solution, and click OK.
Creating a new SQL Server project
4. Configure a reference to the database where automatic deployment will publish the project
assembly containing the CLR database objects. To do this, select the SQLTrainingKitDB.dbo
reference and click OK.
Note: The Add Database Reference dialog enumerates data connections that you have
defined in Server Explorer. The list will be empty if you currently have no data connections. If
you have not previously defined a connection to the SQLTrainingKitDB database, you will need
to do so now. The setup script creates this database during setup. You need to have completed
the setup procedure before proceeding.
Adding a database reference
5. [Optional] If you cannot find any available reference in the Add Database Reference dialog, you
will need to create the SQLTrainingKitDB reference manually. To do this, follow these steps:
a. In the Add Database Reference dialog, click Add New Reference.
b. In the New Database Reference dialog, type the SqlServerTrainingKitAlias server
alias in the Server name combo box.
c. Choose the SQLTrainingKitDB database in the Select or enter a database name drop
d. Click Test Connection to ensure that the server name is correct and the database
exists, then click OK.
Creating a reference to the SQLTrainingKitDB database
6. Enable SQL CLR debugging for the connection. This step is required to be able to debug the
project. When prompted by Visual Studio, click Yes to enable debugging for the
Note: The message shown by Visual Studio warns that debugging will stop all managed threads
on the server. Later on, you will see how this affects other queries executing on the server
Enabling SQL/CLR debugging for the connection
Note: The dialog will not appear if you have already configured debugging on this data
connection previously. You may also configure debugging from Server Explorer if you right-
click the data connection and enable or disable Allow SQL CLR Debugging in the context menu.
Task 3 - Reviewing the Generated Project
In the previous task, you created a SQL Server project starting from its template. You now examine the
code that Visual Studio generated for you in the SqlClassLibrary project.
1. Open Solution Explorer to review the files that were included in the solution.
Note: The project does not contain any source code at this stage. It only includes a Test Scripts
folder where you place Transact-SQL scripts that you use to test and debug CLR database
objects implemented in the solution. Currently, it contains a single script file with boilerplate
Transact-SQL code that has been commented out.
The SqlClassLibrary project in Solution Explorer
2. Next, review the database configuration settings for the project. To do this, right-click the
SqlClassLibrary project node in Solution Explorer and select Properties. In the project
properties page, click the Database tab to display the database configuration settings.
Database settings for the project
Note: The Database property page only applies to SQL Server projects. It contains a
connection string for the target database where Visual Studio deploys the SQL CLR assembly.
You configured this database connection in the first part of this exercise, when you created the
This property page also allows you to configure a Permission Level for the assembly, which is
currently set at SAFE, the most restrictive permission set. Code executed by an assembly with
SAFE permissions cannot access external system resources such as files, the network,
environment variables, or the registry.
Additionally, you can assign the name of a user or role as owner of the assembly. If you do not
specify an Assembly Owner, the deployment process assigns ownership to the current user.
3. Click the Browse button to open the Add Database Reference dialog. Here you can explore the
available database references and choose another data connection. Click Cancel to close the
Add Database Reference dialog
4. Now, click the Deploy tab to review the current deployment options. In this property page,
setting the Deploy Code option ensures that Visual Studio copies source code files in the project
to the target database during deployment. This option allows source-level debugging of the CLR
objects in the project without having the Visual Studio project available. Make sure that you
leave this option enabled for this exercise.
Deployment settings for the project
Task 4 - Adding a User-Defined Function to the Project
Implementing a user-defined function using managed code allows you to take advantage of the richer
programming environment provided by the .NET Framework. In this task, you will create a function that
converts an IP address from an IPV4 string format to its numeric 32-bit equivalent. Database
applications that deal with IP address information often need to perform similar conversions, for
example, to perform lookups against IP location databases or to store this information in a more
Note: The IP function is used to illustrate the use of control loops, string manipulation and
mathematical computation, which can be difficult to implement using Transact-SQL, but does not
necessarily represent an optimal implementation.
1. Add a new user-defined function to the project. To do this, right-click the SqlClassLibrary
project node in Solution Explorer, point to Add, and then select User-Defined Function. In the
Add New Item dialog, select the User-Defined Function template, type IPHelpers.cs in the
Name field, and click OK.
Note: Many different CLR database items can be created using SQL CLR including stored
procedures, aggregates, triggers, user-defined types, and regular classes.
Adding a new user-defined function to the project
Add New Item dialog showing different CLR database objects available in SQL Server projects
2. Open the IPHelpers.cs file in the code editor. The generated class contains an IPHelpers method
with a default body, which you will replace shortly.
Note: The UserDefinedFunctions class has been qualified with the partial keyword. Using
partial classes allows multiple developers to implement different functions in separate source
code files without interfering with each other.
Also notice the Microsoft.SqlServer.Server.SqlFunction attribute that decorates the method.
This attribute indicates that Visual Studio should register the method in SQL Server as a
function when it deploys the assembly.
Template generated code for a user-defined function
3. Delete the IPHelpers method in the UserDefinedFunctions class; it is not required. You will
replace it with a new function in the next step.
4. Create a new user-defined function to convert IP addresses from IPV4 string format to a 32-bit
value. To do this, insert the IPAddressToNumber function shown below in the code block, and
press CTRL+S to save the file.
(Code Snippet – IPAddressToNumber function - Ex01 Creating Function Using SQL CLR)
public static SqlInt64 IPAddressToNumber(SqlString IPaddress)
long num = 0;
if (IPaddress.IsNull || IPaddress.Value == String.Empty)
string arrDec = IPaddress.Value.Split('.');
for (long i = arrDec.Length - 1; i >= 0; i--)
num += (Int64.Parse(arrDec[i]) % 256) * (long)Math.Pow(256, (3 - i));
Note: The implementation of this function is based on code available at:
5. In Solution Explorer, expand the Test Scripts folder and double-click Test.sql to open this file in
the script editor. The file contains sample Transact-SQL statements to test different kinds of CLR
Note: The content in this file has been commented out because you will need to provide your
own T-SQL statements to test any objects that you implement in the project.
6. Scroll to the bottom of the file and insert the following Transact-SQL statement to test the
IPAddressToNumber function passing in an IP address parameter.
7. Press CTRL+S to save the script file.
8. Build and deploy the solution to SQL Server. To do this, in the Build menu, select Build Solution
and then Deploy Solution.
Note: This step is not strictly necessary if you are going to debug the solution in Visual Studio.
When you choose Start from the Debug menu, Visual Studio automatically compiles and
deploys the code to the SQL Server database. In the next exercise, you will debug the assembly
and execute the user-defined function it contains.
Exercise 2: Debugging SQL CLR Objects
Previously, you created a new SQL CLR User Defined Function (UDF) and deployed it to SQL Server. In
this exercise, you will explore different features provided by Visual Studio to debug SQL CLR objects.
Task 1 - Setting up a Debugging Session in Visual Studio
In this task, you will verify and configure settings that affect your debugging session.
1. If not opened, start Microsoft Visual Studio 2008 from Start | All Programs | Microsoft Visual
Studio 2008 with administrative privileges. To do this, right-click the program shortcut and
select Run as administrator.
2. Open the solution that you completed in the previous exercise. Alternatively, you may use the
provided End.sln solution located under the Ex1-CreatingFunctions\End folder in the Source
folder of this lab.
Note: If you use the “End” solution, you will need to configure the database connection string
before you can proceed. To do this, right-click the project in Solution Explorer, select
Properties, and click the Database tab to open the database settings property page. In
Connection String, click Browse to open the Add Database Reference dialog, select the
SQLTrainingKitDB data connection that you configured during the first exercise and click OK.
3. In Solution Explorer, right-click Test.sql in the Test Scripts folder and select Set as Default
Debug Script. You can have multiple test script files in your solution and setting the default
debug script allows you to choose which one executes when you start your debugging session.
Configuring a default debug script in Visual Studio
4. Make sure that the database connection has been set up to allow SQL CLR debugging. To do
this, open the Server Explorer (if it is not already open) by pressing CTRL+W+L, expand the Data
Connections node and right-click the connection that points to the SQLTrainingKitDB database
that you created in the previous exercise. Make sure that the Allow SQL/CLR Debugging option
Enabling SQL CLR debugging on the connection
5. Verify that you have correctly deployed the SqlClassLibrary assembly and its source code to the
Note: In the previous exercise, the project deployment options specified the Deploy Code
To verify that Visual Studio copied the necessary files to SQL Server, select the SQLTrainingKitDB
data connection node in Server Explorer, expand the Functions node and check that the
IPAddressToNumber function appears under this node. In addition, expand the Assemblies
node, locate the SqlClassLibrary assembly and confirm that the source code files for this
assembly appear under this node. You will use the source code files deployed to SQL Server later
Viewing deployed objects in Server Explorer
Task 2 - Using Test Scripts to Debug SQL CLR Objects
In this task, you will debug a SQL CLR database object by executing a Transact-SQL script file that
performs the actions in the database that are required to start and test the object. You will also examine
the impact that debugging has on other queries running on a SQL Server instance while you are trying to
debug against it.
1. Open the test script file in the editor. To do this, double-click Test.sql in Solution Explorer.
2. Set a breakpoint on the first executable (non-commented) Transact-SQL statement in the file.
To do this, click the area on the left margin of the script editor window, next to the line of script
where this statement is located or press F9 with the cursor positioned on this line.
Setting breakpoints in the test script file
3. In the Debug menu, select Start Debugging to begin execution of the Test.sql script.
Note: Pressing F5 does not launch the debugger when the input focus is on a Transact-SQL
script file. You need to switch the focus to Solution Explorer or a C# code file to be able to
start debugging by using F5.
4. Depending on your current configuration, when debugging starts, Visual Studio might display
one or more messages prompting for firewall configuration settings required to enable remote
debugging. If you are debugging against a local instance of SQL Server, you can select the first
option labeled 'Cancel remote debugging'; otherwise, you will need to enable remote
debugging through the firewall. Select a suitable option and click OK to start the debugging
Firewall configuration with UAC enabled
Configuring the firewall for remote debugging
Note: The first time that you run the Remote Debugging Monitor, it must configure the
Windows Firewall on the computer where it is installed. This configuration is necessary to
enable remote debugging.
5. When you start debugging, the Transact-SQL script begins to execute and hits the breakpoint
Hitting a breakpoint in the test script
6. Press F11 to single-step so that the next executable statement is the one that calls the
Note: You can step through each Transact-SQL statement in the script file.
7. Press F11 again to execute the next statement.
Note: The Visual Studio debugger switches its debugging context from the script file to the
source code file that contains managed code for the IPAddressToNumber function, allowing
you to debug SQL CLR objects directly in Visual Studio.
Single-stepping through a SQL CLR user-defined function
8. Press F11 a few more times to step through part of the code in the function and then press F5
to resume execution and exit the function.
9. Press CTRL+W+O to open the Output window and see the output generated by the call to the
SQL CLR function. The SELECT statement in the Transact-SQL test script returns a single scalar
value denoted in the output window as Column1.
Viewing the output from the SQL CLR function
10. Start Microsoft SQL Server Management Studio from Start | All Programs | Microsoft SQL
Server 2008 R2 and connect to a Database Engine using the SqlServerTrainingKitAlias server
Connecting to the SQL Server instance
11. Press CTRL+N to open a new query window.
12. In the query window, enter the following Transact-SQL statement:
DECLARE @point Geography = Geography::Point(45, 200, 4326)
Note: The statement declares and initializes a variable that uses the Geography spatial data
type. SQL Server implements the Geography type as a managed type. When you execute the
following statement, it will run managed code on the SQL Server instance that you are
13. Press CTRL+E to execute the Transact-SQL statement.
Note: The query completes immediately. The result is not significant for the purposes of this
lab; you are using this statement solely for executing managed code.
14. Switch back to Visual Studio.
15. Make sure that the breakpoint in the T-SQL test script is still set on the first executable
statement in this file and that no breakpoints exist in the IPAddressToNumber function.
16. In the Debug menu, select Start Debugging to begin execution of the Test.sql script again. Wait
for execution to stop at the breakpoint.
17. Press F11 to step into the IPAddressToNumber managed code function. Note that you are now
debugging managed code and therefore all managed threads in the SQL Server instance are
18. Return to SQL Server Management Studio and press CTRL+E to execute the query once again.
Notice that this time the query does not complete immediately and that the status bar indicates
that it is still in progress.
Note: If execution stops at a breakpoint while debugging in Visual Studio, all managed threads
in the SQL Server instance that you are debugging are suspended and queries that use
managed code cannot proceed. You must ensure that no one else is using the SQL Server
instance while you are trying to debug against it.
19. In Visual Studio, press F5 to resume execution of the function and release all managed code
20. Return to SQL Server Management Studio and notice that the query is now able to complete.
Queries are blocked while managed code is being debugged
Task 3 - Attaching to the SQL Server Process to Debug SQL CLR Objects
Previously, you started a debugging session by launching a test script in Visual Studio that created and
used the SQL CLR database objects in your project. In certain situations, you may need to debug SQL CLR
objects created or triggered by other applications accessing the database. In this task, you will start a
debugging session by attaching the Visual Studio debugger to the SQL Server process and then set
breakpoints in the source code in your project. When the applications using the database access the
corresponding SQL CLR objects, execution stops at the breakpoints that you have defined and allows you
to debug the code.
1. Make sure that you are running Visual Studio as an administrator. This is required to attach to
the SQL Server process.
Running Visual Studio with administrative privileges
2. In the Debug menu, select Attach to Process.
3. In the Attach to Process dialog, make sure that Show processes from all users is selected. If you
are running through a remote desktop connection, also make sure that Show processes in all
sessions is selected.
4. In the list of available processes, select the SQL Server process (sqlservr.exe). Verify that the
Attach to list indicates automatic detection of the type of code to debug (denoted by the label
Automatic) and that the Managed code option is included in the list. If this is not the case,
ensure that you have selected the correct instance of the SQL Server process, click Select to
open the Select Code Type dialog, check that Automatically determine the type of code to
debug is selected, and click OK to close the dialog. Back in the Attach to Process dialog, click
Attach to debug the SQL Server process in Visual Studio.
Note: If you are running multiple instances of SQL Server, the available processes list will list
each one. If you are unsure about which instance to choose, open SQL Server Configuration
Manager and select the SQL Server Services node to display all running services. Identify the
correct SQL Server service instance by examining the instance name displayed in parenthesis,
next to the service type. Make a note of the Process ID for this instance. Back in Visual Studio,
locate the correct instance by matching the Process ID obtained previously with the value
displayed in the ID column of the Attach to Process dialog.
Attaching to the SQL Server process for debugging
Note: To attach to the SQL Server process, you require administrative privileges.
5. Depending on your SQL Server service account configuration, Visual Studio might prompt you
with a security warning. Click Attach to proceed.
Security warning when attaching to untrusted processes
6. In Solution Explorer, double-click IPHelpers.cs to open this file in the code editor.
7. Place a breakpoint on the IPAddressToNumber function. To do this, click the area on the left
margin of the code editor window, next to the line that contains the opening brace of this
method or press F9 with the cursor positioned on this line.
8. Start Microsoft SQL Server Management Studio from Start | All Programs | Microsoft SQL
Server 2008 R2 and connect to the SQL Server instance that is hosting the CLR managed code
9. Press CTRL+N to open a new query window.
10. In the query window, enter the following Transact-SQL statements that call the
IPAddressToNumber function. Press CTRL + E to execute these statements.
Note: When you execute the query in SQL Server Management Studio, the focus switches back
to Visual Studio and execution stops at the breakpoint that you set previously.
11. Press F11 to single-step through the lines in the function or F5 to resume execution.
Task 4 - Debugging SQL CLR Code without a Project
In this task, you will debug the SQL CLR function that you created in the previous exercise without
accessing the Visual Studio solution that you used to create it.
1. In Visual Studio, close the current solution. To do this, in the File menu, click Close Project.
2. In Server Explorer, expand the Data Connections node and select the SQLTrainingKitDB data
connection node. You should already have configured this data connection to allow SQL CLR
debugging in a previous task.
3. In the SQLTrainingKitDB data connection node, expand the Functions node, right-click the
IPAddressToNumber function and select Step Into Function to start debugging this function.
Debugging a function from Server Explorer
4. Depending on your current configuration, Visual Studio might display one or more messages
prompting for firewall configuration settings required to enable remote debugging. If you are
working against a local instance of SQL Server, you can select the first option labeled 'Cancel
remote debugging'; otherwise, you will need to enable remote debugging through the firewall.
Click OK to start the debugging session.
5. Enter the parameters for the IPAddressToNumber function. In the Run Function dialog, type a
valid IP address in the Value field and click OK. Notice that the IPHelpers.cs source code file
opens in the debugger.
Entering parameter data to execute the IPAddressToNumber function in the debugger
Note: The debugger retrieves the source code file from SQL Server, where it was stored when
the project was originally deployed. In task 3 of the previous exercise, you saw how the project
deployment settings included the Deploy Code option. This ensured that Visual Studio copied
the source code files in the project to SQL Server during deployment.
6. Press F11 to step through the code in the IPAddressToNumber method. Notice that the
debugging experience is unchanged even though the original project is not available.
7. Press F5 to continue execution and exit the function.
In this hands-on lab, you have examined CLR integration and seen how to enable this feature in SQL
Server 2008. With the CLR hosted in SQL Server, you can author stored procedures, triggers, user-
defined functions, user-defined types, and user-defined aggregates in managed code. During the lab,
you used Visual Studio to create a SQL Server project and implement a user-defined function using
managed code. You have seen how Visual Studio automatically deploys your managed code assemblies
to SQL Server. Finally, you explored the support in Visual Studio for debugging SQL CLR database objects.