在MS Sql Server中访问MSMQ - 微软消息队列

在工作中需要在CLR Trigger中向MSMQ发送消息，所遇到了一些问题。当然现在都已经解决，参考的这篇文章，http://www.codeproject.com/KB/database/SqlMSMQ.aspx

翻译总结如下：

有时候需要从Microsoft SQL Server中使用MSMQ来发送和接收数据，虽然SQL Server 2005使用MSMQ用来完成一些复制任务，但是T-SQL并没有提供相关接口来访问MSMQ。

可以有以下几种解决办法：

1、扩展存储过程，用来解决这种问题的古老办法，您可以写一个C++ DLL包含需要从T-SQL中调用的方法，一般来说比较困难并且SQL Server2005不推荐采取这种办法。

2、xp_cmdshell和命令行工具-你可以写一个命令行工具用来向队列中发送、接收数据，然后使用xp_cmdshell来调用这个命令行工具。由于安全方面原因，xp_cmdshell默认是禁用的。

3、windows Service和polling，一个另类但可行的解决办法.windows service(proxy)从一个数据表中“拉”数据并且发送到消息队列，同时监听队列，接收消息后插入另一表中。看上去有些复杂但也很有效。

4、CLR 程序集-这个解决方案利用了Microsoft SQL Server 2005的新特性-CLR 集成，换句话说，现在可以使用C#、VB.NET等来编写存储过程、触发器等。

第2种解决办法示例代码：
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

queuethunder.exe命令行工具中可以使用args(0)参数来获取发送的消息并送到队列中去。

第4种解决办法：
A、在SQL Server实例中启用CLR 集成
sp_configure 'clr enable', 1
GO

RECONFIGURE
GO

B、使用PERMISSION_SET = UNSAFE 来注册一个程序集之前，数据库必须设置 TRUSTWORTHY 参数为ON:
ALTER DATABASE AdventureWorks SET TRUSTWORTHY ON
GO

C、注册程序集
CREATE ASSEMBLY Messaging
AUTHORIZATION dbo
FROM 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll'
WITH PERMISSION_SET = UNSAFE
GO

PERMISSION_SET参数告诉SQL Server如何限制一个程序集访问资源，SAFE 是默认选项，并且有最严格的限制-具有SAFE权限的程序集中执行的代码不能访问外部的系统资源比如文件、网络、环境变量或者注册表等。 EXTERNAL_ACCESS参数允许CLR代码存取文件、注册表、网络等资源，UNSAFE 参数允许一个程序集访问所有外部资源和SQL Server内部资源并且可以调用不可管代码。
注册程序集时候可能会持续一段时间，因为会同时注册System.Messaging引用到的其他程序集，既然这些DLL都位于和System.Messaging.dll同一目录中，所以SQL Server可以自动注册这些被引用的程序集。

D、注册自己编写的CLR程序集
CREATE ASSEMBLY SqlMSMQ
AUTHORIZATION dbo
FROM '{set path}\SqlMSMQ\Bin\SqlMSMQ.dll'
WITH PERMISSION_SET = UNSAFE
GO

E、最后创建引用方法位于SqlMSMS.dll中的存储过程
CREATE PROCEDURE uspMSMQSend
@queue nvarchar(200),
@msg    nvarchar(MAX)
AS EXTERNAL NAME SqlMSMQ.[WJeziorczak.Sql.SqlMSMQ].Send
GO

EXTERNAL NAME后面的SqlMSMQ为上一步注册的程序集名，[WJeziorczak.Sql.SqlMSMQ]为名字空间和类名，Send为方法。

Ok!现在就可以在CLR PROCEDURE或者CLR Trigger中访问MSMQ了。

BTW:

MSDN上关于部署CLR数据库对象的介绍：

CREATE ASSEMBLY HelloWorld from 'c:\helloworld.dll' WITH PERMISSION_SET = SAFE

1. The procedure, function, aggregate, user-defined type, or trigger must then be created in the instance of SQL Server. If the HelloWorld assembly contains a method named HelloWorld in the Procedures class, the following Transact-SQL can be added to the query to create a procedure called hello in SQL Server 2005.

CREATE PROCEDURE hello
AS
EXTERNAL NAME HelloWorld.Procedures.HelloWorld