Click here to Skip to main content
15,867,453 members
Articles / Database Development / SQL Server
Tip/Trick

Installing a CLR Stored Procedure on SQL Server

Rate me:
Please Sign up or sign in to vote.
4.91/5 (3 votes)
15 Jul 2015CPOL2 min read 25.6K   2  
The steps to install a CLR Stored Procedure in SQL Server

Introduction

This tip details how to install a .NET Common Language Runtime Stored Procedure on SQL Server. It does NOT cover writing the .NET code, just the installation in SQL Server.

There are several good articles on creating CLR stored procedures, so this one will only focus on the installation process.

STEP 1: Configure SQL Server

1a: Run this query to make sure the CLR is enabled in SQL Server:

SQL
select case [value]
        when 0 then 'You NEED to run the sp_configure.'
        else 'You do NOT need to run the sp_configure.'
        end
from sys.configurations
where name = 'clr enabled';
go

1b: IF you NEED to run sp_configure, continue, ELSE go to STEP 2:.

Run the following SQL. This will enable the .NET Framework (.NET 2.0 in SqlSvr 2008r2). The "reconfigure" commands requires SERVER admin privileges to run.

SQL
sp_configure @configname=clr_enabled, @configvalue=1
go
reconfigure
go

STEP 2: Grant the User, and Assembly "external access" Rights.

Login to the database as a user in the system admin role, OTHER THAN the user you are going to use to execute the rest of the process (e.g. use the "sa" system account). This is necessary because a user cannot grant these rights to themselves. Execute the following SQL:

SQL
use master;
grant external access assembly to [Domain\Login]; -- The domain\login of the original user.
go

-- This allows the CLR stored procedures to have external_access rights.
alter database {database name} set trustworthy on;
go

STEP 3: Get the .NET CLR Assembly

Compile the .NET CLR project in Visual Studio, or get an already compiled DLL file.

Copy the CLR.dll file to a location that can be referenced by the SQL Server machine as a file path.

STEP 4: Import the .NET assembly into SQL Server

Execute the following SQL. This imports the .NET assembly into SQL Server.

SQL
declare @dllPath nvarchar(255) = '{The file system path to your CLR DLL (e.g. C:\Temp\CLR.dll)}'
create assembly {choose assembly name} from @dllPath with permission_set = external_access;
go
  • Notes:
  • The "external_access" permission set is needed if you want to access resources outside of SQL Server, if you don't use "safe" instead.
  • You can avoid the problem of having the SQL Server account access the file system by converting the assembly to hex, and passing that instead.
    See the tip Installing a SQL Server CLR Stored Procedure as a Hex String for an example of turning a .dll file into a hex string for SQL Server.
  • If you receive an error that the database owner SID is different than in the master, execute the following SQL as a system admin:
    SQL
    alter authorization on database::{database name} to sa;
    

STEP 5: Create the Procedure in SQL Server

Execute the following SQL. This creates stored procedures from the methods in the assembly. Replace the (@msg nvarchar(max), @result nvarchar(max) output) parameters with the parameters and return defined in the .NET function.

SQL
create procedure {choose SP name} (@msg nvarchar(max), @result nvarchar(max) output)
as external name {choose Assembly name}._
[{.NET namespace}.{.NET class name}].{.NET function name};
go

STEP 6: Call the Stored Procedure

Executing the following will run the stored procedure (substitute the parameters for what you created in Step 5).

SQL
declare @res nvarchar(max);
exec {SP name chosen in Step 5} @msg = 'The msg', @result = @res output;
print @res;

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
-- There are no messages in this forum --