How to manually deploy a CLR Stored procedure to SQL Server Mangement Studio

Before we start this article, I have listed a few related post which you would like to check out…

There are many reasons why you would manually want to deploy a CLR stored procedure to SQL Server Management Studio. There are two parts to deploying any CLR Stored Procedure

  • Part I : Deploying Assembly
  • Part II : Deploying CLR Stored Procedure

1. Deploying Assembly

Once your CLR stored procedure is ready, build the project(Shift + F6). You should get the following in your output window(Ctrl + W, O).

Compile complete -- 0 errors, 1 warnings

CLR Stored Procedures -> C:\Yasser\MyClrDemo\bin\Debug\MyClrDemo.dll  

This is the path where your assembly is located, use this path to CREATE ASSEMBLY as follows, remember to set the PERMISSION_SET to ‘SAFE’

CREATE ASSEMBLY HelloWorldAssembly from 'C:\Yasser\MyClrDemo\bin\Debug\MyClrDemo.dll'

WITH PERMISSION_SET = SAFE  

2. Deploying CLR Stored Procedure

Now that we have our assembly created(HelloWorldAssembly), next step is to create a stored procedure which uses this assembly.

Now consider we have the following CLR Stored procedure

public class HelloWorldClass  
{  
[Microsoft.SqlServer.Server.SqlProcedure]  
public static void HelloWorldMethod()  
{  
SqlContext.Pipe.Send("Hello world!\n");  
}  
}  

So keeping the above classname, stored proc name and assembly name in mind below is how your ‘create procedure’ should be like.

CREATE PROCEDURE hello

AS

EXTERNAL NAME HelloWorldAssembly.HelloWorldClass.HelloWorldMethod

Hope this helps :)

Further Reading :