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.

Answer 1

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?

Answer 2

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