Tags: | Categories: Blog Posted by admin on 1/11/2010 12:42 PM | Comments (0)


Back in July last year I posted how to create and deploy a CLR Trigger for MS SQL to add your business logic in the database, so when somebody inserts a new row, you can do some work. Great if you control the MS SQL Server. You can read the blog post here:

http://alpascual.com/blog/sql-2008-clr-triggers-use-a-net-class-library-in-sql-using-wpf/

I have been lately receiving lots of emails and questions about that post, that tells me one thing, I don’t have readers, there are only people using Bing or Google looking how to do that. No problem let me fix the main concern for many people. When installing dependent dlls on MS SQL it may fail as you are required to add another dll before, so the order was very important, to avoid the problem you can just copy all dlls to a temp location, as MS SQL looks for dependencies in the same directory than the dll you are trying to insert on the databse.

Create the MS SQL table on the database if you haven’t.

Copy all the dlls referenced on the CLR trigger DLL into a temp directory. This are the recommended list you going to need.

'C:\Windows\Microsoft.NET\Framework\v3.0\Windows Communication Foundation\SMDiagnostics.dll'

'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\System.Runtime.Serialization.dll' 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.5\System.Data.Linq.dll'

'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.5\system.core.dll'

'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\windowsbase.dll' 'C:\Windows\Microsoft.NET\Framework\v3.0\WPF\PresentationCFFRasterizer.dll' 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Drawing.dll'

'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\PresentationCore.dll' 'C:\Windows\Microsoft.NET\Framework\v3.0\WPF\PresentationHostDLL.dll' 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Windows.Forms.dll' 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.DirectoryServices.dll'

'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\PresentationFramework.dll' 'C:\Windows\Microsoft.NET\Framework\v3.0\WPF\PresentationUI.dll' 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Web.dll' 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll'

'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\System.IdentityModel.dll'

'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\System.IdentityModel.Selectors.dll' 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\System.ServiceModel.dll' 'C:\Windows\Microsoft.NET\Framework\v3.0\Windows Communication Foundation\Microsoft.Transactions.Bridge.dll'

'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.5\System.Web.Extensions.dll'

'C:\Program Files\WPF Toolkit\v3.5.40128.1\wpftoolkit.dll'

Then run the SQL script to set up the database and to enable clr:

--------------------------------------

USE DatabaseName
GO

ALTER DATABASE DatabaseName SET TRUSTWORTHY ON

GRANT UNSAFE ASSEMBLY to public

EXEC sp_configure 'show advanced options' , '1';

go

reconfigure;

go

EXEC sp_configure 'clr enabled' , '1'

go

reconfigure;

-------------------------------------

Do this per each assembly you copy into a temp directory:

--------------------------------------

CREATE ASSEMBLY [SMDiagnostics]  FROM 'C:\mypath\SMDiagnostics.dll'  WITH permission_set = unsafe

-------------------------------------

Add the CLR trigger into the database and create the trigger

-------------------------------------

CREATE ASSEMBLY TriggerName
FROM 'C:\Path\TriggerName.dll'
WITH permission_set = unsafe
GO

CREATE TRIGGER TriggerName
ON TableName
FOR INSERT
AS EXTERNAL NAME Namespace.HandleTrigger
GO

Hope this is much better than the previous version of the post.

Cheers

Al

blog comments powered by Disqus