How to add a CLR Assembly to SQL Server 2005

• First set the database to be compatible with CLR

EXEC sp_configure @configname = 'clr enabled', @configvalue = 1
RECONFIGURE WITH OVERRIDE
GO

• Set database to allow unsafe assemblies required for XML Serialisation

ALTER DATABASE CREDITDB SET TRUSTWORTHY ON

• Drop assembly if exists

DROP ASSEMBLY [xxx.XmlSerializers]
go
DROP ASSEMBLY xxx
go

• Create assembly

CREATE ASSEMBLY xxx
FROM 'C:\xx\xx.dll'
WITH PERMISSION_SET = UNSAFE
go
CREATE ASSEMBLY [xxx.XmlSerializers]
FROM 'C:\xx\xx.dll'
WITH PERMISSION_SET = SAFE
go

• Setup function to call assembly function

CREATE FUNCTION Bar(@Bar int) RETURNS bit 
AS EXTERNAL NAME xxx.[xxx].Bar
go
CREATE FUNCTION Foo(@Foo int) RETURNS bit 
AS EXTERNAL NAME xxx.[xxx].Foo
go

Back to SQL Server 2005