Queue MSMQ Messages from SQL Server

15 Seconds : Queue MSMQ Messages from SQL Server

15 Seconds : Queue MSMQ Messages from SQL Server

09/02 / 2004

If you’ve ever found yourself scratching your head, wondering how to queue a message from MS SQL Server, then you might find yourself writing your own plumbing. (Microsoft will most certainly be dealing with this boggle in future version). And I don’t know how your boss operates, but I usually need it done like — yesterday.

Using some of my, "Nothing is impossible with Visual Studio .NET" theory, we will create a console application to queue a message in Microsoft Message Queuing (MSMQ). Using the magic of an extended stored procedure, we can call our console application from an MS SQL trigger. We create our table in Enterprise Manager and then use the trigger to call our console application. You could also call a COM object for this example, but I choose a console application because calling in SQL syntax is straight forward.

Message queues are very cool because you can queue a message to a queue server, and it will remain there waiting to be processed. There are systems in place to make sure that message gets to where it belongs. If for some reason the message doesn’t arrive, it will be sent again until it arrives at the intended destination. So for valuable mail-like checks from the lotto commission — we want to ensure those messages get to where they need to go. So, what we want to do is build a process to check the queue and then process the message in it.

Message queues are very underutilized. But Visual Studio .NET makes the process of creating and using this technology much simpler. You can configure queues on your machine by right clicking on My Computer and then selecting Manage. Under the Services and Applications node you can find the "Message Queuing Node". Note: if you don’t see a "Messaging Queuing Node" then you will need to install it from your windows CD.

To tell you the truth, it actually makes me feel warm inside when I fire up VisualStudio .NET. It is also moments like these that I know why Microsoft added the Win32 Console Application project type. It’s a command shell application and looks like an MS-DOS window. (We’ll never be without it). And since we have access to the .NET Framework from within our console application, we can use the Messaging classes to communicate with the queue server.

Installing a queue server is pretty straightforward, but I’ve found it can be difficult to write to Public queues from a Windows 2000 client if you are on an Active Directory (AD) network-that is, unless you have the AD installed on the domain controller. (The look on the network guy’s face when you talk about touching the AD is priceless). We’re fortunate that Windows XP and Microsoft Windows Server 2003 present no issues with writing to queues on other servers. If you are using Windows 2000, you can use Private queues pretty easily. You can certainly use Public Queues with a 2000 client, but there may be some more footwork involved.

On the SQL side, we’ve set-up a trigger within our table of e-mail addresses. Upon updating a record, it will get the ID of the changed record and then call the console application by using xp_cmdshell. xp_cmdshell is an extended stored procedure. This is a special DLL written in Microsoft Visual C++ .NET. It is comes standard with MSSQL Server. In SP3 for MSSQL Server you need to give the user calling this stored procedure rights to call console applications. For the purpose of this application, I made the SQL user a sysadmin. This will call our application and pass the updated ID of the record that we want to work with.

CREATE TRIGGER GO ON [dbo].[email] 

DECLARE @cmd sysname
Select @ID = ID from inserted 

SET @cmd = 'c:\temp\queuethunder.exe ' + cast(@ID as varchar(10))
EXEC master..xp_cmdshell @cmd

When creating a console application in Visual Studio, the Sub Main routine is the start-up code for the application. It assesses the parameters passed to it in the args parameter. (These are actual command line parameters passed to the application). This code then looks at the ID of the record we passed from our SQL trigger, opens up a queue, and then inserts into the queue (as an object) the ID only.

  Sub Main(ByVal args() As String)
        'Dim sQueuePath As String = "your_machine_name\private$\happy"
        'Dim sQueuePath As String = "your_machine_name\happy"
        Dim sQueuePath As String = "Calvinlapxp4\happy"
        Dim myEnumerator As System.Messaging.MessageEnumerator
        Dim myQueue As New System.Messaging.MessageQueue(sQueuePath)
        Dim oHappyQueue As System.Messaging.MessageQueue = GetQueue(sQueuePath)
        Dim oObject As String

            oObject = args(0)

            'Send id to queue


        Catch ex As Exception
            Console.WriteLine("System Error " + ex.Message)
            Console.WriteLine("System Error " + ex.Source)
            Console.WriteLine("Sub-System Error " + ex.InnerException.Message)

        End Try

        oHappyQueue = Nothing
    End Sub

On the server side, we pick things back up in the queue to validate the e-mail addresses. First we’ll need to create another console application and call it ProcessQueue. The Sub Main code will open the queue, cycle through each message, and then process it. Because the messages are queued, our SQL process runs asynchronously with this process. This means that SQL will be finished processing when the validate e-mail function is concurrently being processed in a different process.

Dim sQueuePath As String = "Calvinlapxp4\happy"
            Dim myEnumerator As System.Messaging.MessageEnumerator
            Dim myQueue As New System.Messaging.MessageQueue(sQueuePath)
            Dim sResult As String
            Dim oProjectThundercom As New com.projectthunder.www.ValidateEmail
            Dim oData As New DataAccessLayer.DataAccess.SQLServer("Server", 
"database", "username", "password")

            'Get enumerator so we can go through the queue
            myEnumerator = myQueue.GetMessageEnumerator()

            'we are going through each message so we can so we can get the value 
from the message then want to remove it so it isn't processed again
            While myEnumerator.MoveNext()

                'resulting id from e-mail record
                sResult = StringFromMessage(myEnumerator.Current)
                Dim semail As String

                'go get email address from record
                Dim ods As DataSet = oData.runSQLDataSet("select * from email 
where id = '" + sResult + "'")
                If ods.Tables(0).Rows.Count > 0 Then
                    semail = ods.Tables(0).Rows(0).Item("email")

                    Console.WriteLine("Record not found " + sResult)
                End If

                'Going out to get the result of testing the e-mail address
                Dim statusresult As String = 

                'update the record with result 
                oData.runSQLDataSet("update email set emailstatus = '" + 
statusresult + "' where id = '" + sResult + "'")

                'remove the message from the queue so we don't process it again

                ods = Nothing

            End While
            myQueue = Nothing
            oProjectThundercom = Nothing
            oData = Nothing

        Catch ex As Exception
            Console.WriteLine("System Error Message " + ex.Message)
            Console.WriteLine("System Error Source " + ex.Source)
            Console.WriteLine("System Error Trace " + ex.StackTrace)
            Console.WriteLine("Sub-System Error " + ex.InnerException.Message)

        End Try

You can use this process to trigger off the insertion of new records that you want to be part of a calculation once the transaction is done. You may trigger off a new order record, but until the order details are written, you can’t get a total. Or, you may trigger updates to other databases, instead of using SQL Server Link Server. In MSSQL you can link a different server then access the tables and data from that server by prefixing the object with the server name. You can also call the queue process from a Stored Procedure as part of a business process, such as sending a fax or an e-mail update to a manager. Sometimes doing simple tasks from the DBMS layer can notify issues or take care of important rules regardless of how the data was updated.

This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s