Reference .NET Assembly from a SQL Server Stored procedure or function

Question

Is it possible to reference a .NET Assembly from a SQL Server Stored procedure or function, or otherwise access the clr code from SQL Server?

EDIT Whilst this solution will require to be somewhat generic, I am fairly confident expecting SQL 2005+

Answer 1

It depends on your version of SQL Server. SQL Server 2005 and higher supports CLR Stored Procedures. If you have an older version, you need to register the Assembly as a COM class (using attributes on the objects/methods/assembly), and then registering it using regasm. Then you can call it like any other COM Object.

http://dn.codegear.com/article/32754

SQL 6.5 is a bit buggy though (leaks memory occasionally), so you might need to register it as a COM+ Component (in my experience). That might not stop the memory leaks, but it can help prevent the "Class not found" errors. I'm not exactly sure why it occurs in 6.5

http://msdn.microsoft.com/en-us/library/ms189763.aspx