Click here to Skip to main content
13,141,576 members (34,717 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as


5 bookmarked
Posted 21 Jul 2013

SQL CLR Objects Quick Get Started

, 21 Jul 2013
Rate this:
Please Sign up or sign in to vote.
This tip contains the settings and configuration details to quickly get started with SQL CLR objects to create, build, deploy and test.


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.


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;
sp_configure 'clr enabled', 1;
EXEC sp_configure 'show advanced options' , '0';


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:


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:


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.


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


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.



This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

S.Sathik Ali
Technical Lead
India India
No Biography provided

You may also be interested in...


Comments and Discussions

-- There are no messages in this forum --
Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.170915.1 | Last Updated 22 Jul 2013
Article Copyright 2013 by S.Sathik Ali
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid