Using .NET CLR Based Methods In SQL Server

Posted: June 11, 2013 in SQLServer
Tags: ,

— Please change the database name according to environment
ALTER DATABASE <DBNAME> SET TRUSTWORTHY ON;
GO
declare @user varchar(50)
SELECT @user = quotename(SL.Name)
FROM master..sysdatabases SD inner join master..syslogins SL
on SD.SID = SL.SID
Where SD.Name = DB_NAME()
exec(‘exec sp_changedbowner ‘ + @user)
GO

IF EXISTS (SELECT name FROM sysobjects WHERE name = ‘SP_Name’)
DROP Procedure SP_Name;
go

IF EXISTS (SELECT name FROM sys.assemblies WHERE name = ‘CLRInterface’)
DROP ASSEMBLY CLRInterface;
go

— Please change the path according environment
CREATE ASSEMBLY CLRInterface¬†FROM ‘C:\AssemblyName.dll’
WITH PERMISSION_SET = UNSAFE;
GO

CREATE Procedure SP_Name(@ConfigName nVarchar(100))
AS EXTERNAL NAME InterfaceName.ClassName.[MethodName];
go

EXEC sp_configure ‘show advanced options’ , ‘1’;
reconfigure;

EXEC sp_configure ‘clr enabled’ , ‘1’ ;
reconfigure;

EXEC sp_configure ‘show advanced options’ , ‘0’;
reconfigure;
GO

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