Error Sending To MSMQ queue from a Clustered SQL Server
From: fdmourao (anonymous_at_discussions.microsoft.com)
Date: 05/25/04
- Next message: Gregory A. Larsen: "Re: Returning SQL servers & DB's"
- Previous message: Joe Celko: "Re: Temp files in Stored Procedures"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 25 May 2004 09:31:04 -0700
Hi there,
I had this simple task to do: "To have a trigger that sends a message to a queue under certain circunstances...".
My solution was:
the trigger calls a stored procedure, which in turn makes the following call - "exec master..xp_cmdshell CScript <script_path>\SendToQueue.vbs <queue><label><message>".
The SendToQueue.vbs script has basically the following lines:
...
Set qinfo = CreateObject("MSMQ.MSMQQueueInfo")
qinfo.FormatName = "DIRECT=OS:" & Wscript.Arguments(0)
Set qSend = qinfo.Open(2, 0) ' Send access, deny none
Set mSend = CreateObject("MSMQ.MSMQMessage")
mSend.Body = Wscript.Arguments(2)
mSend.Label = Wscript.Arguments(1)
mSend.Send qSend, 3 'MQ_SINGLE_MESSAGE
qSend.Close
....
This implementation worked just fine on a non-clustered environment. But when I try to migrate this solution to a clustered environment
( - Windows 2000 Advanced Server;
- SQL Server 2000 Enterprise Edition installed on a two node cluster;
- MSMQ installed locally;)
The stored procedure which calls "xp_cmdshell" gives the following error:
"MSMQQueueInfo: The Message Queueing service is not available" when trying to execute the line "Set qSend = qinfo.Open(2, 0)".
I tried everything to workaround this problem, but still with no success:
I tried to migrate this solution to a similar cluster environment, but with MSMQ installed on cluster instead of locally and still get the same error.
The queue I want to send the messages to, has "full control" permissions to everyone. I tried to send to a local queue, to cluster queue, and still nothing...
I tried to use "sp_OACreate", "sp_OAMethod", etc... to avoid using xp_cmdshell but unfortunelly I ended up with dead end: a Microsoft Bug - I can't assign the property Body of the "MSMQ.MSMQMessage" object.
The weard thing is that if I execute a DTS with an ActiveX Script Task which does the same as the script above. It works fine.
If I try to execute the script from the command prompt it also works fine.
Thus, I think this may have something to do with permissions on the "master"
DataBase or the "xp_cmdshell" sp itself...
Can anyone help me with this problem?
Thanx in advance
- Next message: Gregory A. Larsen: "Re: Returning SQL servers & DB's"
- Previous message: Joe Celko: "Re: Temp files in Stored Procedures"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
