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]
FOR UPDATE
AS
DECLARE @cmd sysname
DECLARE @ID int
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
Try
oObject = args(0)
'Send id to queue
oHappyQueue.Send(oObject)
oHappyQueue.Close()
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")
Else
Console.WriteLine("Record not found " + sResult)
End If
'Going out to get the result of testing the e-mail address
Dim statusresult As String =
oProjectThundercom.ChatMailServer(semail)
'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
myEnumerator.RemoveCurrent()
ods = Nothing
End While
myQueue.Close()
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.
