Unanswered: Sending To MSMQ queue from a Clustered SQL Server
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)
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