Installation of CLR Procedure on SQL Server

by Ryan Atkins PR Manager

Basic

Installation of .NET common language run time store procedure on the SQL Server. Creating the SQL procedure by compiling stored procedure items to different SQL server projects. After you deploy to a system running SQL Server, stored procedure created and then managed are executed like any other stored procedure

Installation Process:

· CLR stored procedure and its step by step creation

Step 1: Configuration of SQL Server

1.1 Run the following query to ensure that CLR is enabled in SQL Server

1.2 If sp_configure needs to run, continue, Else goto Step 2

The following SQL needs to run that enables the .NET framework (.NET 2.0 in SqlSvr 2008r2). “Reconfigure” command requires Server admin privileges for running.

Step 2: Grant user and assembly “external access” rights

As a user, login to database in system admin role, other than that you may execute the rest of the process. It is necessary as the user is unable to grant these rights to themselves. Execute the SQL code:

Step 3: Get .Net CLR assembly

Accumulate the .Net CLR project in Visual Studio or get the already compiled DLL file.

Copy CLR.dll file to a location which may be referenced by SQL Server machine as file path.

Step 4: In SQL Server, import .Net assembly

·The “external_access permission is required in order to access resources outside of SQL Server, if you are not using “safe” instead.

· You may refrain the problem of SQL Server account access of the file system by transforming the assembly to hex and passing it.

If an error occurs other that the database owner SID is distinct in the master, then execute the SQL as system admin.

Step 5: Creating SQL Server procedure

Execute the SQL. Stored programs are created by this method in the assembly. Replace _@msg nvarchar(max), @result nvarchar(max) output) parameters and return defined on .NET function.