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

Yasser Shaikh
Nov 21, 2012 · 2 min read

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 warningsCLR Stored Procedures -> C:\Yasser\MyClrDemo\bin\Debug\MyClrDemo.dllThis 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 = SAFE2. Deploying CLR Stored ProcedureNow 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 procedurepublic 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 helloASEXTERNAL NAME HelloWorldAssembly.HelloWorldClass.HelloWorldMethodHope this helps :)Further Reading :

Yasser Shaikh's blog

It's supposed to be automatic, but actually you have to push this button.

Yasser Shaikh

Written by

Yasser Shaikh's blog

It's supposed to be automatic, but actually you have to push this button.

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade