Categories
Uncategorized

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:YasserMyClrDemobinDebugMyClrDemo.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:YasserMyClrDemobinDebugMyClrDemo.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 :