Feb 12, 2014

How to transfer or create CLR stored procedure from one database to other database in sql server

We can transfer or move or create CLR stored procedures from one database or from one sql server to other sql server (Other location):

Step 1: In the destination database where you want to create CLR stored procedure executes following sql queries:

sp_configure 'clr enabled', 1
GO

RECONFIGURE
GO

ALTER DATABASE DestinationDB SET TRUSTWORTHY ON
EXEC dbo.sp_changedbowner @loginame = N'sa', @map = false

Note: Replace the DestinationDB by your database name.

Step 2: Right click on assembly of your stored procedure in the source database and choose Script Assembly As -> Create To -> New Query Editor Window


Step 3: You will get create assembly script something like this:

CREATE ASSEMBLY [DemoCLR]
AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004...
WITH PERMISSION_SET = UNSAFE

GO

ALTER ASSEMBLY [DemoCLR]
ADD FILE FROM 0x4D6963726F
AS N'bin\Debug\DemoCLR.pdb'

GO

ALTER ASSEMBLY [DemoCLR]
ADD FILE FROM 0xEFBBBF496D706F727...
AS N'My Project\AssemblyInfo.vb'

GO

ALTER ASSEMBLY [DemoCLR]
ADD FILE FROM 0xEFBBBF496D706F72...
AS N'uspGetEmployeeInfo.vb'

GO

EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyProjectRoot', @value=N'F:\Projects\DemoCLR\DemoCLR' , @level0type=N'ASSEMBLY',@level0name=N'DemoCLR'
GO

From above script find out path of .dll file of CLR stored procedure. In above script it shown in bold characters. In this example it is:   F:\Projects\DemoCLR\DemoCLR . Copy the .dll  file (In this example DemoCLR.dll)  from that path and copy it destination sql server where you want to create clr procedure. Edit the path in above script according to path of dll file in destination sql server in above script and execute the above script to create assembly.

Step 4: In the same way in SSMS generate the create stored procedure script of your CLR stored procedure. It will look something this:

CREATE PROCEDURE [dbo].[uspGetEmployeeInfo]
    @StatusMsg [nvarchar](4000) OUTPUT
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [DemoCLR].[DemoCLR.StoredProcedures].[uspGetEmployeeInfo]
GO

EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFile', @value=N'uspGetEmployeeInfo.vb' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'uspGetEmployeeInfo'
GO

EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFileLine', @value=N'14' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'uspGetEmployeeInfo'
GO

No comments:

Post a Comment