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.

Solution 1

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

Solution 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?

Solution 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

Comments

[anonymous]

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?

[ZygD]

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

[anonymous]

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.

[anonymous]

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 :)