Made in USA: Enterprise Application Services

sql server Archives • Ayoka - Made in USA Enterprise Application Services

Invoking Managed Code from SQL Server

September 1, 2009

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’

“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.


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.

FROM ‘ ’

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

You are now all set to call the stored procedure and invoke managed code.

SQL Server Best Practices – Ayoka Lunch n’ Learn Series

May 7, 2009

About once a month or so, us developers here at Ayoka like to get together over lunch to discuss various software development topics in a series we call Ayoka Lunch n’ Learn. This month our resident SQL Server expert, Steve Chang discussed SQL Server best practices. You can check out the the four part video series below. You can also download the complete PowerPoint presentation here: SQL Server Best Practices – Ayoka Lunch n’ Learn.

Part 1

Part 2

Part 3

Part 4