Skip to content

Instantly share code, notes, and snippets.

@JahsonKim
Last active October 19, 2020 09:05
Show Gist options
  • Save JahsonKim/d984b9d332f916fe8083c3ff56e01f94 to your computer and use it in GitHub Desktop.
Save JahsonKim/d984b9d332f916fe8083c3ff56e01f94 to your computer and use it in GitHub Desktop.
How to create a CLR procedure that invokes the clrExample.cs method.
CREATE PROCEDURE [dbo].[postData]
@postData NVARCHAR(4000),
@returnval NVARCHAR(2000) OUTPUT
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [CLRAssembly].[CLRExample].[postData]
--After creating the clrExample.cs class in visual studio generate a dll file that you add into your
--SQL server with the name CLRAssembly.
--this name depends on the name of your project its not neccessarily CLRAssembly.
--To add the assembly in your database expand the database->Programmatically->Assemblies->Right Click -> Add Assembly.
--Navigate to the location of your
--assembly file then click Ok to add the assembly.
--[CLRExample]-> name of the class in clrExample.cs
--[postData]->name of your method.
--In order to use your assembly you may need to set the UNSAFE/ UNRESTRICTED permission while adding it in your database.
--This requires
--asymetric key which you can generate using the following query.
USE MASTER
GO
CREATE
ASYMMETRIC KEY CLRAssembly
FROM EXECUTABLE FILE = N'PATH TO YOUR DLL \CLRAssembly.dll'
CREATE LOGIN [ClrPermissionsLogin]
FROM ASYMMETRIC KEY CLRAssembly;
GRANT UNSAFE ASSEMBLY TO [ClrPermissionsLogin];
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment