Results 1 to 1 of 1
  1. #1
    Join Date
    May 2004
    Posts
    1

    Angry Unanswered: Sending To MSMQ queue from a Clustered SQL Server

    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
    Last edited by fdmourao; 05-25-04 at 10:39.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •