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.

You might also like...

Comments

Bruce Johnson I am the owner of a small application development consulting company that specialized in the design and implementation of Internet-based applications. While there are others who can make a web site...

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Perl - The only language that looks the same before and after RSA encryption.” - Keith Bostic