往消息队列传数据的存储过程

 ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

 

CREATE PROCEDURE [dbo].[SendMSMQMessage]
 @FormatName nvarchar(255),  --队列FormatName
 @Label nvarchar(250) --消息标签
AS
BEGIN
 SET NOCOUNT ON;

 DECLARE @MSMQInfo int,@MSMQ int, @Message int;
 DECLARE @HR int;
 DECLARE @Property nvarchar(255);
 DECLARE @Return nvarchar(255);
 DECLARE @Source nvarchar(255), @Desc nvarchar(255);

 -- 创建MSMQ对象
 EXEC @HR = sp_OACreate N'MSMQ.MSMQQueueInfo',
  @MSMQInfo OUT;
 IF @HR <> 0
  GOTO CLEANUP
 ELSE
 -- 创建成功
 BEGIN
  -- 设置FormatName属性.
  EXEC @HR = sp_OASetProperty @MSMQInfo,
   N'FormatName',@FormatName;
  IF @HR <> 0 GOTO CLEANUP

  -- 调Open方法打开队列（写入）
  EXEC @HR = sp_OAMethod @MSMQInfo,
   N'Open',@MSMQ OUT,2,0;
  IF @HR <> 0 GOTO CLEANUP

  -- 创建MSMQMessage对象
  EXEC @HR = sp_OACreate N'MSMQ.MSMQMessage',
   @Message OUT;
  IF @HR <> 0 GOTO CLEANUP

  -- 设置Message的Label属性
  EXEC @HR = sp_OASetProperty @Message,
   N'Label',@Label;
  IF @HR <> 0 GOTO CLEANUP

  -- 设置Message的Body属性
  --EXEC @HR = sp_OASetProperty @Message,
  -- N'Body',@Body;
  --IF @HR <> 0 GOTO CLEANUP

  -- 发送消息
  EXEC @HR = sp_OAMethod @Message,
   N'Send',NULL,@MSMQ;
  IF @HR <> 0 GOTO CLEANUP
 END

CLEANUP:
 IF @HR <> 0
 BEGIN
  -- 报告错误信息.
  EXEC sp_OAGetErrorInfo Null,
   @Source OUT,
   @Desc OUT;
  RAISERROR (@Desc,10,1);
 END

END_ROUTINE:
 IF @MSMQ IS NOT NULL
 BEGIN
  -- 关闭队列
  EXEC @HR = sp_OAMethod @MSMQ,
   N'Close';
  IF @HR <> 0
  BEGIN
   SET @MSMQ=NULL;
   GOTO CLEANUP;
  END
 END
 -- 销毁对象. 
 if @Message IS NOT NULL
  EXEC @HR = sp_OADestroy @Message;
 if @MSMQInfo IS NOT NULL
  EXEC @HR = sp_OADestroy @MSMQInfo;
 RETURN;
END

Exec dbo.SendMSMQMessage 'DIRECT=OS:192.168.10.125\private$\MQToData',@label

Exec dbo.SendMSMQMessage 'DIRECT=OS:192.168.10.125\private$\MQToData',@label