65.9K
CodeProject 正在变化。 阅读更多。
Home

从 Microsoft SQL Server 访问 MSMQ

Wojtek Jeziorczak
starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.95/5 (12投票s)

2007年3月26日

4分钟阅读

viewsIcon

132207

downloadIcon

2930

如何使用 CLR 集成在 SQL Server 中发送/接收 MSMQ 消息

问题

有时您需要通过 Microsoft SQL Server 发送/接收 MSMQ 数据。虽然 Microsoft SQL Server 2005 使用 MSMQ 进行复制作业，但 T-SQL 中没有访问 MSMQ 的功能。让我们来填补这个空白！

分析

我们有以下解决方案

  1. 扩展存储过程 - 这是处理此类任务的旧方法。您需要编写一个 C++ DLL，其中包含将从 T-SQL 调用 的方法。这通常不容易，在 Microsoft SQL Server 2005 中不推荐。
  2. xp_cmdshell 和命令行工具 - 您需要编写命令行工具来发送/接收/查看队列并使用 xp_cmdshell 调用它们。默认情况下，出于安全原因 xp_cmdshell 已禁用（如果恶意用户控制了有权使用 xp_cmdshell 的 SQL Server 登录名，她/他可以轻松地入侵操作系统）。第二点是您需要为 MSMQ 操作编写这些工具。
  3. Windows 服务和轮询 - 这是一个奇怪但可行的想法。Windows 服务（代理）轮询数据库表以获取要发送的消息，并监听队列以获取到达的消息，这些消息将被放入另一个表中。这看起来很复杂，但功能也很强大。
  4. CLR 程序集 - 此解决方案利用了 Microsoft SQL Server 2005 的新功能 - CLR 集成。换句话说，现在可以使用 C#、VB.NET 等编写存储过程。

我相信您可以在 Internet 上找到前两种解决方案，所以我们不会处理它们。第三种只是另一个带有 ADO.NET 内容的服务。幸运的是，还有最后但同样重要的，在我看来，是最有趣的解决方案。所以选择很简单——CLR 程序集。

解决方案

您将在本文源代码包中看到的所有代码，所有 T-SQL 脚本都在 SqlMSMQRegister.sql 文件中。

程序集

让我们看一下 .NET 程序集源代码。这是一个普通的类库项目，其中包含一个名为 SqlMSMQ 的类，该类包含三个方法 Send、Peek 和 Receive ，它们等同于 System.Messaging.MessageQueue Send、Peek 和 Receive 方法。所有方法都接受两个参数

  • queue - SqlString，包含 queue 路径，例如：myhost\private$\queue1
  • msg - SqlString，包含消息内容。当然，在 Peek 和 Receive 中，它是一个 out 参数

SqlString 类型是 SQL Server nvarchar 类型的 .NET 等效项，因此为了简单起见，此实现假定 MSMQ 队列中的文本消息。这里最重要的部分是 Microsoft.SqlServer.Server.SqlProcedure 方法属性 - 它们标记了哪些方法可以从 T-SQL 作为存储过程调用。我们的方法非常简单，只是一个 try-catch 内部带有 Send、Peek 或 Receive 。构建程序集后，我们就拥有了在 Microsoft SQL Server 中进行操作所需的一切。

SQL Server 2005

首先，我们必须在 Microsoft SQL Server 实例中启用 CLR 集成（默认禁用）。您可以使用 SQL Server Surface Area Configuration 工具（Surface Area Configuration for Features->Database Engine->CLR Integration）或调用 sp_configure （您必须是 'sysadmin' 或 'serveradmin'）

sp_configure 'clr enable', 1
GO

RECONFIGURE
GO

SQL Server 将 CLR 程序集保存在其内部。CREATE ASSEMBLY 语句将程序集上传到 SQL Server 并创建一个给定名称的数据库对象。

CREATE ASSEMBLY assembly_name
AUTHORIZATION role_or_user
FROM path_to_dll
WITH PERMISSION_SET = { SAFE | EXTERNAL_ACCESS | UNSAFE } 
GO

PERMISSION_SET 参数告诉 SQL Server 在程序集访问资源时应该有多严格。SAFE 是默认选项，也是最严格的——以 SAFE 权限执行的代码不能访问外部系统资源，例如文件、网络、环境变量或注册表。EXTERNAL_ACCESS 选项允许 CLR 代码访问文件、注册表、网络等资源。UNSAFE 选项允许程序集访问 SQL Server 内部和外部的所有资源，并调用非托管代码。在我们的例子中，我们必须使用 UNSAFE ，但在创建 SqlMSMQ 程序集之前，我们需要创建 SqlMSMQ 引用的其他程序集。

SQL Server 中注册的开箱即用的系统程序集包括

  • Microsoft.Visualbasic.dll
  • Mscorlib.dll
  • System.Data.dll
  • System.dll
  • System.Xml.dll
  • Microsoft.Visualc.dll
  • Custommarshallers.dll
  • System.Security.dll
  • System.Web.Services.dll
  • System.Data.SqlXml.dll.

在以 PERMISSION_SET = UNSAFE 注册程序集之前，数据库必须将 TRUSTWORTHY 选项设置为 ON

ALTER DATABASE AdventureWorks SET TRUSTWORTHY ON
GO

缺少的 System.Messaging 程序集可以使用以下代码创建

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

这可能需要一段时间，因为它还会创建 System.Messaging 中引用的其他程序集。由于这些 DLL 与 System.Messaging.dll 在同一目录中，因此它们可以被 SQL Server 自动注册。最终，我们可以创建 SqlMSMQ 程序集

CREATE ASSEMBLY SqlMSMQ
AUTHORIZATION dbo
FROM '{set path}\SqlMSMQ\Bin\SqlMSMQ.dll'
WITH PERMISSION_SET = UNSAFE
GO

最后要做的是创建引用 SqlMSMQ.dll 中方法的存储过程

CREATE PROCEDURE uspMSMQSend
@queue  nvarchar(200),
@msg    nvarchar(MAX)
AS EXTERNAL NAME SqlMSMQ.[WJeziorczak.Sql.SqlMSMQ].Send
GO

您可以在 SqlMSMQRegister.sql 中找到一个简单的测试脚本。

进一步开发

查看 CLR 集成的其他功能，例如托管触发器、函数和类型。代替文本消息，您可以创建一个自定义类型对象并通过 MSMQ 发送。在 SQL Server 2005 Books Online 上阅读更多内容。

© . All rights reserved.