SQL Server – Notes on Creating a CLR Trigger

Here are my notes on creating a CLR trigger into SQL Server 2008. If you have any questions about it, just post a comment and I’ll reply. I just wanted my notes on this one online since I’m sure others run into the same issues I did.

Base Tutorial

http://www.c-sharpcorner.com/uploadfile/37db1d/create-your-first-clr-trigger-in-sql-server-2008-using-C-Sharp/

Need to be sa or atleast db_admin to run some of the commands like “reconfigure”

.DLL needs to be .Net 3.5, not 4.0

Fix the calls out to work
http://www.sql-server-performance.com/forum/threads/clr-function-and-full-trust-question.24555/

Explains the serializable XML calls to the web services and how to fix it.
http://blogs.msdn.com/b/sqlclr/archive/2006/10/18/dynamic-assembly-loading.aspx

More reading on xml not getting serialized web services
http://social.msdn.microsoft.com/Forums/en-US/sqlnetfx/thread/e0467b0c-fcb8-420f-abb7-b130d4edb469/

My own question about not being able to access “inserted” table @ stackexchange
http://stackoverflow.com/questions/11121478/sql-trigger-using-asp-net-clr

Point here… namespace and class name both matter. Don’t just pick something arbitrary.
Vague article that got me going but didn’t address all these issues
http://www.c-sharpcorner.com/uploadfile/37db1d/create-your-first-clr-trigger-in-sql-server-2008-using-C-Sharp/

SQL Commands to allow CLR to work
EXEC sp_configure ‘clr enabled’ , ‘1’ ;
reconfigure;
EXEC sp_configure ‘show advanced options’ , ‘0’ ;
reconfigure;

reconfigure will only work with high SQL credientials (sa or db_admin)

Create ASSEMBLY etrition_train_assembly
FROM ‘C:CLR-TriggerTrain_DBCLR-Trigger-eTrition-Synergy-35.dll’
WITH PERMISSION_SET = UNSAFE

The only permission set that worked for me was “unsafe” external would allow email but not web services communicating with another server.

External_Access did not let the web services talk.

Also when you need to add a web service to a .dll and the option to web service isn’t listed, add service reference, click advanced in the bottom corner, then click add web service.

If you can’t access the “inserted” table, make sure the namespace & Class for your .net project are set to CLRTrigger.