SQL CLR and .NET 4.0

Question

I know SQL 2008 doesn't support .NET 4.0.

I have several assemblies, all compiled for .NET 4.0. I need a SQL trigger to kick off a routine in one of these assemblies.

Is there any way (without getting WCF or other forms of inter-process communication involved). to get the SQL CLR to kick off this routine? Maybe I could have a 2.0 targeted assembly that kicks off the 4.0 code somehow? I know that .NET 4.0 allows multiple runtime versions to be hosted in the same process, but I'm not sure how I might leverage this.

As a fallback, I could use a COM wrapper to kick off the .NET 4.0 code...but I'd prefer not to.

http://stackoverflow.com/questions/2781624/sqlclr-using-the-wrong-version-of-the-net-framework — Mitch Wheat, Mar 10 '11 at 00:34
Did you ever get this to work as i have a similar issue: http://stackoverflow.com/questions/7314771/clr-stored-procedure-needs-to-call-net-assembly — MartinaL, Sep 06 '11 at 05:29

score 1 · Answer 1 · answered Oct 20 '11 at 21:50

If you relax the security of a SQL CLR Trigger, it's possible to write a file to disk aka a 'Message Queue' and either use a FileSystemMonitor or official Message Queue Monitor to process the required task using any .NET version. This avoids the complexity of multiple .NET framework versions and the risk of the direct risk of the command shell and writing an XML or other structured data to the file allows the passing of required data fields.

For security don't make this system so flexible it can call any program defined in the file. I would suggest only using the file to pass parameters to known programs.

score 0 · Answer 2 · answered Oct 18 '11 at 20:34

0

I would make a very simple console app. It would reference the binaries needed. It would then be simple to kick of the method by calling the console app with arguments which would pass it to the method you want to invoke.

answered Oct 18 '11 at 20:34

Asken

6,897
9
40
70

This has security problems associated with it. – Jeff Oct 19 '11 at 04:47
what kind of problems? your servers are available to the "wrong" people or a problem with the server user calling the console app? – Asken Oct 19 '11 at 06:06
Our deployed clients will not support enabling xp_cmdshell. – Jeff Oct 19 '11 at 13:53
Showstopper for sure... And the assemblies _have_ to be 4.0 before denali? – Asken Oct 20 '11 at 11:45

SQL CLR and .NET 4.0

2 Answers2