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
Execute the above
script in the destination database.
How to create CLR stored procedure in visual studio 2010 in vb.net.
Output parameters of CLR stored procedure in vb.net.
How to create CLR stored procedure in visual studio 2010 in vb.net.
Output parameters of CLR stored procedure in vb.net.
4 comments:
There are also slide carousel design results for numerous queries which are positioned actually higher and produce a whole new degree of interaction.
Bay Area web design
Whether somebody pursuit of his vital thing, hence he or she desires to be accessible that at length, hence that thing is maintained over here.
user experience consultant
The superb highly informative blog I’m about to share this with all my contacts.
digital product design company
I'm also visiting this site regularly, this web site is really nice and the users are genuinely sharing good thoughts.
UX firms
Post a Comment