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 Microsoft.SqlServer.Server;public partial class StoredProcedures
public static void StoredProcedure1(int sizeInMb)
sp = SqlContext.Pipe;
byte arr = new byte [1024 * 1024 * sizeInMb];
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.
CREATE PROCEDURE [dbo].[MMRCLR] @sizeInMB [int]
WITH EXECUTE AS CALLER AS EXTERNAL NAME [TRMMR].[StoredProcedures].[StoredProcedure1]
4. Execute the procedure.
exec dbo.MMRCLR @sizeInMB = 10