SQL Server Trigger - Send Message to Queue

Question

Is it possible on a CLR trigger in SQL Server 2005 to send a message to a queue via MSMQ?

I am using the SQL Server Project type, but System.Messaging does not appear as a reference I can add.

---

Basically I need some type of action to take place (printing) when a row is written to the table. The device generating the row is a hand held scanner that can only do basic operations -- one of which is writing to SQL server over odbc. The initial idea was to just poll the table, grab records, print records, delete records. This will probably work fine, but it seemed like a good case and excuse to learn about message queues.

Shull

Answer 1

This might work for you: http://support.microsoft.com/kb/555070

Sublimate

Answer 2

Yes, it's possible.

I wouldn't do it in a trigger though: the TXN will stay open longer, it's resource intensive, what if it hangs etc.

Can you update via a stored proc?

Or push a row into a polling table monitored by a SQL agent job that writes to the queue?

Rambort

I think stored proc is workable, but I'm still unsure the road block to me accessing System.Messaging from SQL Server Projects. I'm googling this and getting mixed signals -- System.Messaging is untrusted so it SQL Server won't work with it, or is this wrong? – Shull

Sorry, I've not used it myself. Have you looked at Service broker, part of SQL Server 2005? – Rambort

I've briefly read about it. I'm completely naive about this stuff, but it seemed Service Broker would let the trigger add a message to a Service Broker Queue, but then my program would have to poll that Queue, at which point I might as well just poll the table. – Shull

Service Broker + SqlDependency does seem like something to further explore though. In the mean time I have it working by simply polling the table for additions and removing them once processed. Simple and easy, but my messaging itch left to be scratched :) – Shull

Answer 3

If that assembly is untrusted, you can still access it from SQL Server - it's just not available natively, and will have to be imported manually and marked as "Untrusted" itself. I ran into this same problem with System.DirectoryServices some time ago.

This guy has the same question as you with regards to System.DirectoryServices, but doing a CREATE ASSEMBLY statement in the same way should allow you to access System.Messaging:

http://www.mydatabasesupport.com/forums/ms-sqlserver/218655-system-directoryservices-allowable-clr.html

Kobe

Answer 4

Here are some new ideas on how this old issue could be handled: http://nginn.org/blog/?p=376

Nginn-messagebus is my message queuing project based on SQL server and targeted at .Net applications that rely on SQL server.

Tweeddale

Answer 5

It's hard to do transactionally via push technology.

The only transactional option to do so is to use WCF bridge, and it requires in turn using SQL Server 2008, since 2012 the dynamic background compilation of the WSDL based assemblies blocked by SQL Server hosted CLR, and I have never cracked how to force compile these assemblies in such a way as to avoid references to assemblies prohibited by hosted CLR policies.

The only choice I found working ( perhaps due to my failing to figure out the workaround ) is to use HttpClient RESTFull style network client with CLR integrated procedure working off of SQL Server Broker Activated procedure. It works really well with only one trouble, RESTFull does not support transactional out of the box. So, if you need a guaranteed delivery of the message you will need to have a check call somewhere along the message flow.

In fact to protect integrity of MSMQ operations I inserted an transactional WCF service between my RESTFull and MSMQ, and on MSMQ used trigger, which in turn has policy driven transactional data processing. Please note, MSMQ trigger requires installing MSMQ trigger feature. I have opted to exe based trigger as alternate approach is to use COM based DLL, and I didn't feel like using COM since free threaded DLL requires implementation complex C++ application, and apartment threaded, which is relatively easy to design in C# with CCW, imposes limit on application scale. In the end RESTFull call could be considered "near transactional" since it is performed in the context of transaction, and unless you have some serious error, such as missing to trap an error condition ( basically miss to implement try / catch ) and throw, when raising error condition is required, you will have the benefit of reliable commit / rollback. However, reinforcing with check call and reasonable timeout, when missing data considered equal to absence of commit, to ensure reliability of message delivery, is nice to have.

Phare