SQL Server + CLR -> Stuff noted

There appears to be many limitations and rules to be followed while using the CLR to write an SQL object:

. While using the context connection object via SqlConnection("context connection=true"), make sure only one connection object with this context is open at any point in time. 'Using' should be your friend here.

. If you want to initialize application data which needs to be re-used each time the function/trigger is called, you would want to use static variables. SQL Server would let you define readonly static variables only. (This effectively means, that all initialization has to happen at the constructor).

. Want to access a different database from within a function/any other CLR object ? Make sure the assembly has EXTERNAL_ACCESS granted.

. When your code starts to use locking/objects which use locking (most trace listeners do) , SQL Server complains like mad. At this point, you have no way out other than make sure that the login you created with the asymmetric key on the master DB has got the UNSAFE access.

. To make sure that debugging works OK, make sure that the symbols file (.pdb) is added to the assembly with an alter assembly add file statement.

. If you are accessing a different DB, the transaction for the current context would not be use full there since CLR would not let you. You would need to force a new transactionscope in this case.

As can be seen, there requires lots of hacks to make the stuff working in the CLR environment. The first draft of the code you wrote is guaranteed to not work with SQL CLR :)

May the CLR be with you.