Create assembly to your SQL Database

Posted in on 4/2/2014

This article showing you how to create assemblies to your database, which asseblies be created as C# lanuage, so you could extend your database's functionality

1. Create SQL CLR Database Project

 

So, you could add any code here, like C#, you could even calling Web Service, so you database is able to call web service inside database script too.

2. Create User Defined Function File

Write your C# code on here, which you'll be call on your database

3. stub files of you SQL CLR project

 

4. User Defined Function Sample Code

This's code will be run as DLL on your Database

 public partial class UserDefinedFunctions
 {
     [Microsoft.SqlServer.Server.SqlFunction]
     public static SqlString FunctionTest()
     {
         // Put your code here
         return new SqlString("HelloWorld!");
     }
 };
 
5. Create Assembly Sample Script
 USE TestDB
 GO
 EXEC sp_configure <span'clr', 1;
 RECONFIGURE;
 alter database TestDB Set trustworthy on;
 GO
 
 USE TestDB
 GO
 CREATE ASSEMBLY [MyTestUDFFunc]
 AUTHORIZATION [dbo]
 FROM <span''C:\Test\SqlServerProject1.dll'
 WITH PERMISSION_SET = UNSAFE
 GO
 

6. When you run the script, you might encounter following error message from database

  • CREATE ASSEMBLY for assembly 'SqlServerProject1' failed because assembly 'SqlServerProject1' is not authorized for PERMISSION_SET = UNSAFE. The assembly is authorized when either of the following is true: the database owner (DBO) has UNSAFE ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission.
  • CREATE ASSEMBLY for assembly 'SqlServerProject1' failed because the assembly is built for an unsupported version of the Common Language Runtime.
  • The database owner SID recorded in the master database differs from the database owner SID recorded in database 'TestDB'. You should correct this situation by resetting the owner of database 'TestDB' using the ALTER AUTHORIZATION statement.

So the first one, if you already add Permission_set = unsafe like above sample script, you should be ok.

7. Change your .NET Build version to 3.5

The second unsupported version of the Common Language, I still don't know what's causing, it, but if you change your .NET version from 4.0(i used 4.0) to 3.5, it'll resove.

 

8. Add current database login user to your database

Last error message, go to your database's setting - Files, add your current login-user to this database's owner.

9. Verify your database's assemblies folder

If runs ok, you should be find your dll in the assemblies folder.

10. Sample Code: Add Function to your Assemblies on database
 USE [TestDB]
 GO
 CREATE FUNCTION dbo.fnTestFunc
 (
 )
 RETURNS NVARCHAR(MAX)
 AS
 EXTERNAL NAME [MyTestUDFFunc].[UserDefinedFunctions].[FunctionTest];
 GO
 
11. Test your result
 USE [TestDB]
 select dbo.fnTestFunc();
 
12. result

after run calling the function you created, you could see your result => hello world

13. following is all script you need to create assembliy to database
 USE [TestDB];
 GO
 
 -- drop functions which are using assemblies 
 IF  EXISTS (
 SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(<span')
 AND type in (<span', <span', <span', <span', <span'))
 DROP FUNCTION [dbo].[fnTestFunc]
 GO
 
 USE [TestDB]
 GO
 IF  EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = <span')
 DROP ASSEMBLY [MyTestUDFFunc]
 GO
 
 USE TestDB
 GO
 EXEC sp_configure <span'clr', 1;
 RECONFIGURE;
 alter database TestDB Set trustworthy on;
 GO
 
 USE TestDB
 GO
 CREATE ASSEMBLY [MyTestUDFFunc]
 AUTHORIZATION [dbo]
 FROM <span'
 WITH PERMISSION_SET = UNSAFE
 GO
 
 USE [TestDB]
 GO
 CREATE FUNCTION dbo.fnTestFunc
 (
 )
 RETURNS NVARCHAR(MAX)
 AS
 EXTERNAL NAME [MyTestUDFFunc].[UserDefinedFunctions].[FunctionTest];
 GO
 
 -- test the result
 USE [TestDB]
 select dbo.fnTestFunc();