SQL Server: Using .NET assemblies (CLR)

SQLCLR is a technology for hosting of the .NET CLR engine inside SQL Server. Adding an assembly can be a lifesaver sometimes, since the limitations of coding in TSQL stored procedures, triggers or functions are a lot and profound.

Just follow these steps:
1. Create a new class library using Studio
New Class Library Project


2. Add the following code

using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;

namespace TestCLR{
    public class TestCLRClass{
        [Microsoft.SqlServer.Server.SqlProcedure]
        public static void Pointless( string s, out SqlString sout ) {
            sout  = s + ".";
        }
        [Microsoft.SqlServer.Server.SqlProcedure]
        public static void PointlessWithAnotherWay( string s ) {
             SqlContext.Pipe.Send(s + ".");
        }
    }
}

3. After building the dll and placing somewhere reachable by SQL Server:

--Change the owner of the current database (must be sa and so do you!)
EXEC dbo.sp_changedbowner @loginame = N'sa', @map = false
GO

--CLR Integration Security (http://technet.microsoft.com/en-us/library/ms131071.aspx)
ALTER DATABASE YOUR_DB SET TRUSTWORTHY ON;

--Add the Messaging assembly. You can comment out the following lines, expect an error if "Messaging" is already there.
CREATE ASSEMBLY Messaging
AUTHORIZATION dbo
FROM 'C:\PATH_TO_FRAMEWORK\System.Messaging.dll'--e.g. C:\Windows\Microsoft.NET\Framework\v4.0.30319\System.Messaging.dll
WITH PERMISSION_SET = UNSAFE
GO

--Create your assembly
CREATE ASSEMBLY assembly_testCLR
AUTHORIZATION dbo
FROM 'C:\YOUR_PATH\ClassLibrary1.dll'--Your path to the assembly
WITH PERMISSION_SET = UNSAFE
GO

--Create a stored procedure to call it
CREATE PROCEDURE sp_testCLR
    @inS nvarchar(200),
    @outS nvarchar(MAX)OUTPUT
AS EXTERNAL NAME assembly_testCLR.[TestCLR.TestCLRClass].[Pointless]
GO

--Test it
DECLARE @msg varchar(MAX)
EXEC sp_testCLR 'Just a string without a dot', @msg output
PRINT @msg

Advertisements

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