Using Message Queues in stored procedures

Ok, I'm stumped. I have a database which has a table that stores documents. Each document has a primary key. For years, I've been notifying an external program that a new document has arrived in the db by firing a trigger on the table. Trigger calls xp_cmdshell, which calls an external program which enqueues a message on a message queue.

This code, which has been running fine in SQL 2000 for ~5 years, completely crashes SQL 2005. No problem! I'll use the snazzy new CLR-embedded stored procedures. But oh no! I can't actually USE System.Messaging in my project in any way, shape, or form. Apparently it's not "safe" enough.

So here is my question:

- is there ANY way to use System.Messaging in a CLR stored procedure
- is there ANY OTHER way to use MSMQ in a CLR stored procedure
- is there any way to call an arbitrary external program in a CLR stored procedure

Answer this question

Using Message Queues in stored procedures

Buffy6
Did you use the SAC - Surface Area Configuration - tool to enable xp_commandshell so your original stored procedure works Using a CLR stored procedure would probably be more secure but if you want to use your current trigger, it should continue to work.
chinnybloke
Just did as suggested above, but what about these warnings
Should I give it a wait until MS ensures all is tested and well What kind of trouble could I get into

Thank you,
Lubomir

Warning: The Microsoft .Net frameworks assembly 'system.messaging, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.

Warning: The Microsoft .Net frameworks assembly 'system.configuration.install, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.

Warning: The Microsoft .Net frameworks assembly 'system.runtime.serialization.formatters.soap, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.

Warning: The Microsoft .Net frameworks assembly 'system.windows.forms, version=2.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.

Warning: The Microsoft .Net frameworks assembly 'system.drawing, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.

Warning: The Microsoft .Net frameworks assembly 'accessibility, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.

Warning: The Microsoft .Net frameworks assembly 'system.directoryservices, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.
AmitInnani
I was able to use System.Messaging in a SP after manually registering the DLL in SQL Server as UNSAFE
(on my machine, the command to do this is:
create assembly system_messaging from 'C:\WINNT\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll' with permission_set = unsafe )

Have you tried doing this Did you set Trustworthy to ON in your database to allow you to register unsafe assemblies If so, what error message did you see
SDS-Consulting
Whether you use it or not is your decision. Microsoft has done certification on parts of the dot Net framework to ensure that it will be well behaved and secure when running in SQL Server stored procedure or trigger. Anything that makes calls out of the SQL Server process is considered a security risk and this won't work unless you mark it as external or unsafe. In your case, you are calling out of SQL Server in either solution so you have to choose which one to use. The CLR stored procedure is definitely much less of a security risk than enabling xp_cmdshell so you are probably better off changing to use that.

The only way I could think of to avoid this completely would be to have your trigger put messages in a Service Broker queue and then write an external application that connects to SQL Server, reads the messages and does what your current program does. This would be faster, more reliable and would get you out of the issue with calling out of SQL Server from a trigger. If this sounds like more work than it's worth, then your CLR trigger is probably the second best solution.
Using Message Queues in stored procedures

Answer this question