Manually Deploying a CLR Stored Procedure

The previous post discussed using Visual Studio 2010 to build and deploy a CLR stored procedure to a SQL Server 2008 R2 database. This post looks at manual deployment, how to use T-SQL to create an assembly based on a CLR method and to create a stored procedure which uses it. I needed to do this as I’m using SQL Server ‘Denali’ CTP1 and at the time of writing Visual Studio 2010 does not know about this version so is not able to automatically deploy a stored procedure.

1. Create a DLL containing the CLR method you wish to call.

For example follow the same steps in the last post up to step 6 Build, but this time do not choose the Deploy option. This time I was looking at how memory is allocated by the server, so all my stored procedure does is allocate memory:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.
SqlProcedure]
public static void StoredProcedure1(int sizeInMb)
{
   SqlPipe sp;
   sp =
SqlContext.Pipe;
   byte[] arr = new byte [1024 * 1024 * sizeInMb];
   System.Threading.
Thread.Sleep(60000);
   sp.Send(sizeInMb +
” Mb allocated and held for 60 seconds”);
}
};

Note where the DLL is created.

2. Create an Assembly.

The following T-SQL will create an assembly for the DLL you built in step 1.

CREATE ASSEMBLY TRMMR FROM ‘c:\<my DLL path>\SQLServerProject1.dll’
WITH PERMISSION_SET = SAFE;

3. Create the Stored Procedure.

E.g.

CREATE PROCEDURE [dbo].[MMRCLR] @sizeInMB [int]
WITH EXECUTE AS CALLER AS EXTERNAL NAME [TRMMR].[StoredProcedures].[StoredProcedure1]
GO

4. Execute the procedure.

E.g.

exec dbo.MMRCLR @sizeInMB = 10
GO

 

Advertisements
This entry was posted in Databases. Bookmark the permalink.

One Response to Manually Deploying a CLR Stored Procedure

  1. Pingback: How to create a CLR Stored Procedure using C# and Visual Studio | | Yasser ShaikhYasser Shaikh

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s