Update MSMQ from SQL

One of the problems that we recently faced was the need to post a message to MSMQ when the data in a SQL table was changed. An extensive search of the various resources that we have led us to this solution. And, although it seems to be complicated, it appears to be the only way, at the moment, to accomplish our goal.

First, we created a Visual Basic ActiveX DLL project that will actually talk to the Message Queue. We chose to expose a single method called Send. The code appears below.

Public Sub Send(QueuePath As String, Label As String, Body As String)

Dim msg As MSMQMessage
Dim QI As MSMQQueueInfo
Dim RequestQueue As MSMQQueue

Set QI = New MSMQQueueInfo
QI.PathName = QueuePath
Set RequestQueue = QI.Open(MQ_SEND_ACCESS, MQ_DENY_NONE)

Set msg = New MSMQMessage
msg.Label = Label
msg.Body = Body
msg.Send RequestQueue
Set msg = Nothing

RequestQueue.Close
Set msg = Nothing
Set RequestQueue = Nothing
Set QI = Nothing

End Sub

As you can see, this is relatively straightforward code. Once we have compiled and deployed this component onto the SQL Server machine, we create a stored procedure that will instantiate the object and call the method. The stored procedure that we wrote to do this can be found below.

CREATE PROCEDURE prcSendMSMQMessage
@vc_msmqpath varchar(255),
@vc_messagelabel varchar(255),
@vc_messagebody varchar(1000)

AS

DECLARE @int_msmqqueue INT
DECLARE @int_result INT

-- Create the SQLMSMQ.CSQLSendMessage Object.
EXECUTE @int_result = sp_OACreate 'SQLMSMQ.CSQLSendMessage', @int_msmqqueue OUT, 1
IF @int_result <> 0 GOTO ErrorHandler

-- Send the message using the Send method
EXECUTE @int_result = sp_OAMethod @int_msmqqueue, 'Send', NULL, @vc_msmqpath, @vc_messagelabel, @vc_messagebody
IF @int_result <> 0 GOTO ErrorHandler

GOTO DestroyObjects

ErrorHandler:

DECLARE @vc_source varchar(53), @vc_description VARCHAR(200)
EXECUTE sp_OAGetErrorInfo @int_msmqqueue, @vc_source OUT, @vc_description OUT, NULL, NULL
RAISERROR(@vc_description, 16, 1)

GOTO DestroyObjects

DestroyObjects:
-- Destroy the SQLMSMQ.CSQLSendMessage object.
EXECUTE @int_result = sp_OADestroy @int_msmqqueue

RETURN

Now the obvious question is, why don't we just use the sp_OACreate method to instantiate the Message Queue objects and manipulate them directly. A good question that is, too. The reason for jumping through these hoops is one of data types. The Body property of the MSMQMessage object has a data type of variant. Unfortunately, SQL does not have such a data type. And trying to assign variables of any type to Body using the sp_OASetProperty method results in a 'The parameter is incorrect' error message. So we are reduced to creating the Visual Basic DLL component to act as a stub. A klude? Yes. But we're more concerned with results than elegance. Especially when the elegant way doesn't work.