Queue MSMQ Messages from SQL Server

原创 2004年10月20日 07:35:00

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.


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.

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. 
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.                 

Message Queuing(MSMQ)学习之旅(二)——创建Queue

1.前言 应用程序创建的Queue根据你是否希望被其他程序找到可分为Public Queue和Private Queue,Public Queue会在directory service中注册,有...
  • gjw198276
  • gjw198276
  • 2013年03月18日 16:02
  • 2595

windows 消息队列Microsoft Message Queue (MSMQ)实例

windows 消息队列 实现:发送消息、接收消息、多线程监听消息
  • zss793648186
  • zss793648186
  • 2016年07月06日 22:37
  • 1200

微软的 MSMQ (MessageQueue)的使用,一些理解,以及尝试

电脑MESSAGE信息     MSMQ 1.建立MQ服务  MessageQueue myQueue = new MessageQueue(string path); Path路...
  • huangyi003
  • huangyi003
  • 2017年03月09日 16:47
  • 402

在Windows Server 2008安装Wincc时,提示需要安装消息队列(MSMQ)

在 Windows Server 2008 or Windows Server 2008 R2 上安装消息队列 4.0 在服务器管理器中,单击“功能”。 在“功能摘要”下的右窗格中,单击“...
  • zibinghanmo
  • zibinghanmo
  • 2015年09月09日 10:40
  • 1547


MSMQ的VB操作代码/步骤 1、安装MSMQ,获得Microsoft Message Queue 2.0 Objtec Libary 在VB中添加类型库引用2、声明需要用到的变量 Dim msgqu...
  • shanhe
  • shanhe
  • 2004年06月30日 23:36
  • 3347

微软消息队列:SQL Service Broker PK MSMQ

微软为开发人员提供了两种消息队列:MSMQ和SQL Service Broker。这两种消息队列在很多功能上类似。但它们在一些重要领域的差异也许会影响我们的选择。其中MSMQ是一项非常成熟的技术,而S...
  • cxzhq2002
  • cxzhq2002
  • 2012年03月07日 14:51
  • 615


1.在 Windows Server 2008 or Windows Server 2008 R2 上安装消息队列 4.0 在服务器管理器中,单击“功能”。 在“功能摘要”下的右窗...
  • daiyue945
  • daiyue945
  • 2011年08月25日 10:26
  • 7802

今天遇到的奇葩问题:Validation error messages from TagLibraryValidator for fmt

Validation error messages from TagLibraryValidator for fmt
  • JavaAlpha
  • JavaAlpha
  • 2015年08月27日 22:40
  • 5997


想必大家都知道Oracle的等待时间分为两种,一种我们称之为“空闲等待事件”,另外一种称之为“非空闲等待事件”。 “空闲等待事件”——作为DBA可以不用过分的关注这类等待事件。 “非空闲等待事件”...
  • huaishu
  • huaishu
  • 2013年04月17日 23:01
  • 667


1.因为其它的安装包我都已经安装好了,这里只下载pymssql依赖包 pip install pymssql 2.编写连接代码import os,pymssqlserver="#######"...
  • koko2015c
  • koko2015c
  • 2017年03月22日 09:58
  • 2447
您举报文章:Queue MSMQ Messages from SQL Server