Waqar Farooq Janjua: Creating a SQL CLR Stored Procedure using VS 2010 and Sql Server 2008

SQL CLR give us the ability to write code in .Net Framework. In Visual Studio 2005,8 and 10 there is a database project namely SQL CLR Database project as shown in the below image.

Before creating the project please change the .net framework version to .net Framework 3.5 from the drop down menu.
After choosing the template you have to choose a database in which you will deploy the solution. The following window will open when you press OK button.

We will use Northwind database. Press the OK button. The following window will open

To enable the debugging press the Yes button otherwise No. Now the project is created. The project consists of following folders and files.

Now from the project menu select stored procedure to add a code file.

Add the stored procedure and give a name to it. We gave a name sp_WriteFile to it. We use the File Class method WriteAllText to write a file to the Sytem. The complete code consists of only one line as shown in the below image.

The sp has two parameters FileName and FileContent. Now go to project properties select Database tab and set the Permission Level to external or Unsafe. By default permission level is set to Safe.

We changed the permission level because we have to access the File System. In Safe permission level we can 't access the file system.
Read more about the permission levels at MSDN

Now deploy the solution.

When you deploy the project the following error may appears.

//C:\Code\CLRProject\CLRProject\bin\Debug\CLRProject.sql(106-106): Deploy error SQL01268: .Net SqlClient Data Provider: Msg 10327, Level 14, State 1, Line 1 CREATE ASSEMBLY for assembly 'CLRProject' failed because assembly 'CLRProject' is not authorized for PERMISSION_SET = EXTERNAL_ACCESS. The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission.  
// An error occurred while the batch was being executed.  
// Build FAILED.

As the message explains, SQLCLR code with the EXTERNAL_ACCESS permission set must either be signed or the database must be given the TRUSTWORTHY attribute. Let's make the database TRUSTWORTHY with this ALTER DATABASE statement:

ALTER DATABASE Northwind SET TRUSTWORTHY ON

Another problem is of database ownership as mentioned in the message "database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission" so change the owner of the database to sa. Run the following command

EXEC sp_changedbowner sa

CLR Integration must be enabled in order to use SQL CLR objects. By Default CLR Intergration is disabled so we first have to enable it.
Run the following script to enable Sql CLR integation.

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

Now again deploy the solution. After deployment open the Sql Server Management Studio. Select Northwind database and open the stored procedures folder and the assemblies folder. See the below image.