One of the flagship features in SQL Server 2005 is the ability to write stored procedures using any language supported in .NET. This feature provides greater flexibility for developers to implement complex logic in CLR (Common Language Runtime), stored procedures which are not easily programmed using T-SQL stored procedure.
There are two parts to a CLR function – an assembly function and a function object in SQL itself. You cannot use it, however, until you enable CLR. The following steps should help to accomplish the task of invoking managed code from SQL Server.
Step 1
By default CLR integration is disabled in SQL Server 2005. To enable the managed code execution feature in SQL Server, execute the following in query editor window
EXEC sp_configure ‘clr enabled’, ‘1’
go
reconfigure;
go
“sp_configure” is a stored procedure that is available to change the server level settings. Depending on the change, some option settings may take effect on the fly by just reconfiguring while some might require the server to be stopped and started again.
You can also enable the CLR integration using Surface Area Configuration from the SQL Server 2005 configuration tools.
Step 2
Now we need to set database level permissions before deploying assemblies. A dirty and easy way is to set the trust worthy property of the database to “on”, which is set to “off” by default.
ALTER DATABASE SET TRUSTWORTHY ON
There are other ways of allowing external access or unsafe assemblies in the database. A more appropriate way would be to use keys for the assembly and grant access to them with permission settings in the database.
Step 3
Now it is time to deploy the assembly in the database. You would need to create assembly for every dll being referenced by the managed code before creating the assembly for the managed code.
CREATE ASSEMBLY
AUTHORIZATION dbo
FROM ‘ ’
WITH PERMISSION_SET = UNSAFE
GO
The path of the “dll” must be an absolute path, not a relative path. Do not use function overloading in the managed code as it is not supported by SQL.
Step 4
Create a function or procedure that interacts with the managed code.
CREATE PROCEDURE @param1 type,
@param2 type
AS EXTERNAL NAME .[.].
GO
You are now all set to call the stored procedure and invoke managed code.
