SQL Server trigger - sending a message to the queue

Question

Is it possible that a CLR trigger in SQL Server 2005 will send a message to the queue through MSMQ?

I am using the SQL Server project type, but System.Messaging does not appear as a link that I can add.

---

Basically, I need some kind of action (printing) when a row is written to a table. The device that generates the string is a hand scanner that can perform only basic operations - one of which writes to the SQL server on top of odbc. The original idea was to simply query the table, capture the records, print the records, delete the records. This will probably work fine, but it seemed like a good example and an excuse to learn about message queues.

Answer 1

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

Answer 2

Yes it is possible.

I would not do this at startup: TXN will remain open longer, it is resource intensive, what if it freezes, etc.

Can you update using a stored procedure?

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

Answer 3

If this assembly is not trusted, you can still access it from SQL Server - it is simply unavailable initially and must be imported manually and marked as "Invalid". I ran into this problem with System.DirectoryServices some time ago.

This guy has the same question as System.DirectoryServices, but executing the 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

Answer 4

Here are some new ideas on how to deal with this old problem: http://nginn.org/blog/?p=376

Nginn-messagebus is my SQL Server-based message queuing project and is for .NET applications that rely on SQL Server.

Answer 5

This is difficult to do transactionally using push technology.

The only transactional option for this is to use the WCF bridge, and it, in turn, requires the use of SQL Server 2008, since since 2012, dynamic background compilation of WSDL-based assemblies has been blocked by the CLR hosted on SQL Server, and I never tried to force Compile these assemblies in such a way as to avoid references to assemblies that are prohibited by hosted CLR policies.

The only choice I found working (possibly due to the fact that I could not find a workaround) was to use an HttpClient RESTFull-style network client with an integrated CLR procedure running on SQL Server Broker Activated. It really works well with only one problem, RESTFull does not support transactional out of the box. Thus, if you need guaranteed message delivery, you will need a control call somewhere in the message flow.

In fact, to protect the integrity of MSMQ operations, I inserted a WCF transactional service between my RESTFull and MSMQ, and in MSMQ I used a trigger, which in turn has policy-driven transactional processing. Please note that to start MSMQ, you need to install the MSMQ start function. I chose an exe-based trigger, since an alternative approach is to use a COM-based DLL, and I didn’t feel like using COM, since a free multi-threaded DLL requires the implementation of a complex C ++ application and multi-threaded, which is relatively easy to design in C # with CCW will be flat threaded, and this imposes some limitations on the scale of application. In the end, a RESTFull call can be thought of as "almost transactional," because it is executed in the context of a transaction, and if you don't have serious errors, such as skipping, to catch the error condition (basically, skip the try / catch implementation) and throw, when you need to raise the error condition, you get the advantage of reliable commit / rollback. However, to ensure reliable message delivery, it is desirable to have reinforcement with a test call and a reasonable wait time when missing data is considered equal to no commit.