SQL Server Trigger - Send Message to Queue

views:

1507

answers:

+2 Q:

SQL Server Trigger - Send Message to Queue

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.

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

eglasius 2009-02-23 19:48:20

That link is old as dirt.

skb 2010-04-24 20:49:25

+2 A:

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?

gbn 2009-02-23 19:52:38

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?

eyston 2009-02-23 20:40:46

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

gbn 2009-02-24 04:08:37

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.

eyston 2009-02-24 16:29:40

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

eyston 2009-02-24 16:31:47

+2 A:

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

rwmnau 2009-02-25 20:32:19

ansaurus

tags:

views:

answers:

SQL Server Trigger - Send Message to Queue

related questions