= Sql2msmq - How To = [[PageOutline]] == Introduction == The first thing you need to understand is that Sql2msmq relies upon: "Microsoft .NET Framework common language runtime (CLR) integration with Microsoft SQL Server, and the SQL Server database engine process execution environment." That's a bit of a mouthful, but it's a pretty simple concept. Microsoft has linked the .NET runtime into their SQL server product. This allows developers to create assemblies which can be called from within the context of SQL Server itself. This is very similar to the old fashioned extended stored procedure API that used to be available for the same purpose. The advantage of this new functionality is that it is managed code, so it's more robust (it probably won't bring down your SQL Server), and developers can take advantage of the rich .NET runtime class library for functionality. === Introducing Sql2msmq to MS SQL Server === In order to be able to use Sql2msmq the SQL Server needs to know it exists. In order to ''inform'' SQL Server we need to register the assembly with it explictely. Before we can do that we need to register a few of Sql2msmq's dependencies. In fact there is really only one dependency that SqlServer doesn't already ''know'' about - the ''MS Message Queue'' assembly named "System.Messaging". There's another hurdle before we get to that stage though. In order to register System.Messaging we'll need configure SQL server in such a way that it will allow us to register it. If you are not a seasoned Microsoft SQL Server Database Administrator a lot of this is going to look like so much gibberish; '''''it certainly does to me, even now'''''. There is a huge amount of documentation which may help you to discover a better way of doing this, but these are the steps I took when developing the assembly. In this example, the database which I wish to register the assembly against is named 'fortitude', don't ask why an assembly would be registered against a single database, and not the entire server - it's loaded into the server address space, after all. Here I am assuming that you are logged on to your Sql Server, and are capable of running SQL statements. 1.) make sure we're not doing anything to anyone elses database environment {{{ use [fortitude] }}} 2.) Ensure that CLR integration is enabled (there seems to be some confusion about this) {{{ sp_configure 'clr enable', 1 }}} 3.) Now - we address a strange issue which I simply do not understand, but know how to mitigate: Sql Server seems to require enormous amounts of memory in order to register assemblies. In this case my server has 4 Gig of RAM, so we'll enable it all {{{ sp_configure 'max server memory', 4096 RECONFIGURE }}} 4.) Then we inform SQL Server it can trust the ''fortitude'' database. {{{ ALTER DATABASE fortitude SET trustworthy ON }}} 5.) Now we can register the MS Message Queue assembly - note we need to declare it unsafe for some inexplicable reason. {{{ CREATE ASSEMBLY Messaging AUTHORIZATION dbo FROM 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll' -- obviously path may vary WITH PERMISSION_SET = UNSAFE }}} 6.) Finally, we can register Sql2msmq - don't ask why we don't need the AUTHORIZATION part {{{ CREATE ASSEMBLY sql2msmq FROM 'C:\SQL\TriggerAssemblies\sql2msmq.dll' -- obviously path may vary WITH PERMISSION_SET = UNSAFE }}} If you feel uncomfortable with all that, or you would simply prefer to use another method of enabling the registration of the assemblies, you might wish to read [http://www.sqlclr.net/Articles/tabid/54/articleType/ArticleView/articleId/9/Default.aspx this] I haven't used that method, but it seems like it might be a better way of doing things. One can only hope that some day Microsoft will simplify the amazing muddle that is their security model. If you encounter loads of weird errors which indicate permission issues, please blame Microsoft, not me. Sql2msmq does not use any unsafe code - it's all managed C#. I must confess that I missed a step - that's becuase I'm not sure what its purpose is, but it overcame a weird permission error. I am pretty sure this would ''normally'' not be required by a user with dbo type permissions {{{ EXEC dbo.sp_changedbowner @loginame = N'sa', @map = false }}} == Really using Sql2msmq == Now we're ready to actually use the assembly. To use the assembly we need to associate one or more triggers against it. By default, I register the trigger for three even types: insert, modify, delete === Configuring a trigger === Here I assume a table name of 'dummy_t' {{{ create trigger msmq_trig on [dummy_t] for insert, update, delete -- we want to know about all events NOT FOR REPLICATION as external name sql2msmq.msmqTriggers.msmqTrigger -- DO NOT CHANGE THIS - it's the code that runs inside the assembly GO }}} Note the '' sql2msmq.msmqTriggers.msmqTrigger '' - '''do not change''' this. ''sql2msmq.msmqTriggers.msmqTrigger '' is the exported class from the sql2msmq assembly that will be called when the trigger is fired by Sql Server. === Testing your configuration === Now, you probably would like some indication that things are as they should be. Luckily, the trigger assembly can communicate back to a client through the magic of ''pipes''. All that is needed is a simple insert, update or delete operation on the table Now for something slightly confusing: The '''FIRST''' time you run a data manipulation operation against dummy_t, the Message Queue will need to be created. '''''DONT MAKE IT YOURSELF''''' sql2msmq will make one for you. Here is some sample output from the trigger assembly for trivial update on my dummy_t table {{{ Enter Trigger Open Connection Get Instance Details ServerName = [MY-SQL-SERVER] DBName = [fortitude] TableName = [dummy_t] Trigger Type [Update] Load INSERTED Load DELETED In SendToMQ with quene name - MY-SQL-SERVER\fortitude.dummy_t.Update Create Queue DANGER - Failed to create Message Queue [MY-SQL-SERVER\fortitude.dummy_t.Update] within specified timeout (1 row(s) affected) }}} First of all it's great that we don't see '''''any errors''''' on the output. That's a good indication that everything went well. Now let's look at what got sent back. ''Enter Trigger'' - this is the first indication that sql2msmq has been called as a result of the trigger against the table. ''Open Connection'' - this indicated that sql2msmq has opened a connection to the Message Queue server. The next few lines are sql2msmq's discovery of the server name, database name, table name and table operation Then we see this: ''In SendToMQ with quene name - MY-SQL-SERVER\fortitude.dummy_t.Update'' - The queue name is composed of the server it resides upon, then the database name followed by the table name, then finally the data operation that caused the trigger to be called. Now here comes the confusion: ''Create Queue'' ''DANGER - Failed to create Message Queue [MY-SQL-SERVER\fortitude.dummy_t.Update] within specified timeout'' What you are seeing here is that sql2msmq has tried to create the queue but an internal timeout has expired while waiting for the queue to be visible. This seems to happen almost no matter how long the timeout is, and because we don't want to have the SQL operation abort, we simply report the fact the timeout expired and report success. Now if you run the same SQL statement again, you should see something very similar except in place of the 'DANGER - ....'' message you will see {{{ With Queue -- snip -- 9648 -- snip -- 2008-09-29T11:18:41.553+01:00 2008-09-29T11:18:41.57+01:00 9648 -- snip -- 2008-09-29T11:18:41.553+01:00 2008-09-29T11:18:41.57+01:00 Send Message (1 row(s) affected) }}} Notice the 'With Queue' - this indicates that the queue was successfully opened. Then what you see is the trigger data set. In this case, because we performed an update we see the original row, then the new row. (this is how an Update trigger behaves, it provides a inserted and a deleted table each with a single row) The Xml that is sent is trivially converted into a System.Data.DataTable by way of this code: {{{ System.IO.StringReader sr = new System.IO.StringReader(text); DataTable tbl = new DataTable(); tbl.ReadXml(sr); }}} where ''text'' is the XML payload == Programmatic Example == Here is a trivial sample C# code snippet which illustrates how to receive trigger messages from sql2msmq {{{ #!cpp const int timeout = 120 // 2 minutes public void Func() { MessageQueue queue = new MessageQueue("MY-SQL-SERVER\fortitude.dummy_t.Update", QueueAccessMode.Receive); ReceiveCompletedEventHandler cb = new ReceiveCompletedEventHandler(MessageHandler); queue.ReceiveCompleted += cb; queue.BeginReceive(TimeSpan.FromSeconds(Convert.ToDouble(timeout)), queue); queue.ReceiveCompleted -= cb; queue.Close(); } // Provides an event handler for the ReceiveCompleted event. private static void MessageHandler( object sender, ReceiveCompletedEventArgs e) { try { // we stored the queue in the state object parameter of the constructor MessageQueue queue = (MessageQueue)e.AsyncResult.AsyncState; // End the asynchronous receive operation. Message msg = queue.EndReceive(e.AsyncResult); // sql2msmq sends the message as XML msg.Formatter = new XmlMessageFormatter( new Type[]{typeof(string)} ); // get the buffer out of the message String text = (String)msg.Body; // and convert it to a DataTable System.IO.StringReader sr = new System.IO.StringReader(text); DataTable tbl = new DataTable(); tbl.ReadXml(sr); // Display the data in the DataTable foreach(DataColumn col in tbl.Columns) { Console.Write("{0}\t\t", col.ColumnName); } Console.Write("\r\n"); foreach(DataRow row in tbl.Rows) { foreach(DataColumn col in tbl.Columns) { Console.Write("{0}\t\t", row[col.Ordinal].ToString()); } Console.Write("\r\n"); } // reinitialise the async callback so we can receive another message queue.BeginReceive(TimeSpan.FromSeconds(Convert.ToDouble(timeout)), queue); } catch (Exception x) { Console.WriteLine(x.ToString()); } } }}} == Caveats == Don't leave triggers associated with message queues that you are not monitoring. Failure to perform destructive reads or purges on messages in the queue may lead to reduced server performance, or ultimately more severe resource errors which may ultimately result in your server being unavailable or even crashing. MSMQ allows limits to queue size to be set; it is essential that you understand the impact of accumulating messages on the queue. === Trivial solution === Unregister the Sql2msmq assembly trigger when no readers are operational.