SQL CLR Objects Quick Get Started

Introduction

While searching the web to get started with SQL CLR coding, I was not able to find a really quick start guide. Rather, I found many very good detailed articles discussing different scenarios. So I thought of collating the information from across the web to provide a real simple quick start guide on SQL CLR for beginners.

Background

There was a requirement in our project to have a call to the web services on each DML operations. So I searched the web for best possible solutions to this and concluded with SQL CLR as it offers object-oriented capabilities to our SQL objects. But writing, debugging, testing, deploying SQL CLR objects required some additional settings and configurations in both SQL server project and database server side. This simple tip will cover these settings and configurations section in short.

This document contains the settings and configuration details to create, build, deploy and test the SQL CLR objects.

SQL Server Configuration

Enabling CLR Integration

By default, CLR is disabled in SQL Server. Enable CLR execution by executing the following query:

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

Reference: http://msdn.microsoft.com/en-us/library/ms131048(v=sql.90).aspx 

Set Database as Trustworthy

Your database will be accessing "unsafe/external" assemblies. In order to prevent security exceptions, you have to mark your database as "trustworthy" by executing the following query:

ALTER DATABASE <Database_Name> SET TRUSTWORTHY ON
RECONFIGURE

To check trustworthy status:

SELECT name, is_trustworthy_on FROM SYS.DATABASES WHERE name = <Database_Name> 

Database owner needs EXTERNAL ACCESS

To be allowed to create an assembly with external access, the database owner needs to have EXTERNAL ACCESS ASSEMBLY permission. If that’s not the case, you will see the following error when trying to create the assembly:

CREATE ASSEMBLY failed 

So to avoid this error, make sure that your DB owner has sufficient permissions. The following script makes sa the owner of the active database:

EXEC sp_changedbowner 'sa', false; 

Building the Assembly

Before we build our assembly, there are a couple of project settings that need modification.

1. We need to change the Permission Level to External Access/External (based on Visual Studio version). This is mandatory to call web service from code.
2. Also, check the database connection string (modify if required)
3. If you want to develop SQL CLR assemblies for SQL Server 2005 or SQL Server 2008, you must have the .NET Framework version 3.5 installed on your development computer. SQL Server 2005 and SQL Server 2008 require that SQL CLR assemblies target version 2.0, 3.0, or 3.5 of the .NET Framework.

Reference: http://msdn.microsoft.com/en-us/library/84b1se47(v=vs.100).aspx

Deploying the Assembly

Follow the steps below to deploy a CLR SQL assembly on the database.

Right-click the project and select ‘Deploy’.

When the Assembly has been deployed, it is visible in ‘Microsoft SQL Server Management Studio’.

• Connect to your local server
• Expand the database name that you are using
• Expand the Programmability Folder
• The Triggers are located in the ‘Database Triggers’ Folder
• Stored Procedures are located in the ‘Stored Procedures’ Folder
• The Assembly is stored in the ‘Assemblies’ Folder

Reference: http://www.codeproject.com/Articles/277654/SQL-CLR-Stored-Procedures-Triggers-Functions-The-D 

Points of Interest

• To remove the assembly, all the Stored Procedures and Triggers located in the Assembly should be dropped / deleted before the assembly can be removed.
• Using the right-click create menu on the Stored Procedure/Trigger/Functions/Assembly will generate Text that contains the Hex version of the compiled assembly and/or the original source or debug information. This will allow the CLR SQL object to be installed on another server with a single script.
• Perform an Action (Insert / Update / Delete in ‘Microsoft SQL Server Management Studio’) that will cause your trigger to fire.

Reference: http://msdn.microsoft.com/en-us/library/84b1se47(v=vs.100).aspx