在SQL服务器中使用MSMQ组件实现网络内异步通讯

阿罗

几天前碰到这么一个问题，在公司里，如果有一个人出差，他想把他的部分权限委托给另外一个同事。一个比较容易想到的办法就是用一个隐藏的窗体定时向SQL服务器查询特定表，如果有新的委托记录，则在客户端处理。但是分析以后，认为这个方法虽然原理简单，但是在实施过程中有不少问题。主要的问题是，工作委托并不是频繁发生的事件，可能整天也就数起，为了这样一个功能，大大加重了服务器和网络的负担，非常的不经济。另外，如果某个人已经出差在外，而想变更委托时，实时处理变得要求太高。

后来想到用消息队列来解决这个问题。在查找资料时，找到这么一篇文章，很有针对性。后来我就按照他来进行一些试验，结论是完全可行。同时，也找到其他的一些文章，MSMQ应该有更广泛的应用价值。现在把我找到的资料和试验结果同大家分享，希望能起到抛砖引玉的效果。

这篇是关于如何在SQL服务器里使用一个MSMQ组件的例子：
http://www.tagconsulting.com/Show.asp?Id=1000

[此贴子已经被作者于2003-9-23 11:31:37编辑过]

阿罗

为了以后万一这个连接失效，现在把整篇文章拷贝下来：

                                    [B]Update MSMQ from SQL[/B]
  Introduction  

  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.

阿罗

由于我没有vb，是否请有条件的朋友传一个做好的activeX上来？

阿罗

更进一步参考资料：在sql服务器里使用activeX

下个星期计划找个vb6来进行试验。将逐步充实这个帖子的内容。

阿罗

这个是一个PDF文档，讲述如在SQL服务器里调用activeX组件。

[此贴子已经被作者于2003-9-21 23:42:44编辑过]

阿罗

我的实验过程：共4张图。

1) 将做好的dll注册。
2) 创建一个存储过程
3) 在查询分析器里试验存储过程
4) 检查消息队列

[此贴子已经被作者于2003-9-21 23:43:20编辑过]

阿罗

阿罗

阿罗

这个就是SQLMSMQ.dll

阿罗

如果还是不清楚msmq到底有何作用，可以参看一下连接：

http://www.yesky.com/SoftChannel/72342371928637440/20030506/1667016.shtml