SQL Scripts for CLR Assemblies

Here are SQL Scripts for CLR assemblies on Microsoft SQL Server.

Assumptions: assembly's name: "SQLCLR.dll".In the dll, you have an aggregate function: "ConcateAll" which concatenate all string. The dll is in database server's c:\temp directory.

To enable CLR on SQL Server:

	sp_configure 'clr enabled', 1
	GO
	RECONFIGURE
	GO

view raw gistfile1.sql hosted with ❤ by GitHub

To clean up (remove the CLR function and assembly from database)

	IF EXISTS (SELECT * FROM sys.assembly_modules WHERE assembly_class='ConcateAll')
	BEGIN
	DROP AGGREGATE dbo.ConcateAll
	END
	IF EXISTS (SELECT * FROM sys.assemblies WHERE name='SQLCLR')
	BEGIN
	DROP ASSEMBLY [SQLCLR]
	END

view raw gistfile1.sql hosted with ❤ by GitHub

To upload assembly to SQL Server

	CREATE ASSEMBLY [SQLCLR]
	FROM 'c:\temp\SQLCLR.dll'
	GO

view raw gistfile1.sql hosted with ❤ by GitHub

Create aggregate function based on CLR assembly

	CREATE AGGREGATE dbo.ConcatAll(@input NVARCHAR(4000))
	RETURNS NVARCHAR(4000)
	EXTERNAL NAME SQLCLR.ConcatAll

view raw gistfile1.sql hosted with ❤ by GitHub

Grant execution permission to user

GRANT EXECUTE ON ConcateAll To my_user

view raw gistfile1.sql hosted with ❤ by GitHub