Results 1 to 3 of 3

Thread: Service Broker

  1. #1
    Join Date
    Apr 2010
    Posts
    5

    Question Unanswered: Service Broker

    I just can't seem to get any messages to show up in the destination queue. I've set this up before but for some reason I can't get it to work this time. I'm using SQL version 9.00.4262.00 Microsoft SQL Server Standard Edition (64-bit). The databases are on the same server. I'm going blind looking at this. Can someone please help me. Here is my code. . . .

    USE MASTER
    GO

    --Source Database
    ALTER DATABASE IdentityDev SET ENABLE_BROKER
    GO

    --Destination Database
    ALTER DATABASE IdentityServiceBrokerDev SET ENABLE_BROKER
    GO

    ALTER DATABASE IdentityDev SET TRUSTWORTHY ON;
    GO

    ALTER DATABASE IdentityServiceBrokerDev SET TRUSTWORTHY ON;
    GO

    USE IdentityServiceBrokerDev;
    GO

    CREATE MESSAGE TYPE [//BothDB/Identity/RequestMessage]
    VALIDATION = NONE;
    CREATE MESSAGE TYPE [//BothDB/Identity/ReplyMessage]
    VALIDATION = NONE;
    GO


    CREATE CONTRACT [//BothDB/Identity/SimpleContract]
    ([//BothDB/Identity/RequestMessage]
    SENT BY INITIATOR,
    [//BothDB/Identity/ReplyMessage]
    SENT BY TARGET
    );
    GO


    CREATE QUEUE IdentityTargetQueue;

    CREATE SERVICE [//TgtDB/Identity/TargetService]
    ON QUEUE IdentityTargetQueue
    ([//BothDB/Identity/SimpleContract]);
    GO

    USE IdentityDev;
    GO

    CREATE MESSAGE TYPE [//BothDB/Identity/RequestMessage]
    VALIDATION = NONE;
    CREATE MESSAGE TYPE [//BothDB/Identity/ReplyMessage]
    VALIDATION = NONE;
    GO


    CREATE CONTRACT [//BothDB/Identity/SimpleContract]
    ([//BothDB/Identity/RequestMessage]
    SENT BY INITIATOR,
    [//BothDB/Identity/ReplyMessage]
    SENT BY TARGET
    );
    GO


    CREATE QUEUE IdentityInitiatorQueue;

    CREATE SERVICE [//InitDB/Identity/InitiatorService]
    ON QUEUE IdentityInitiatorQueue;
    GO

    ----------------------------------------------------


    USE IdentityDev;
    GO


    DECLARE @InitDlgHandle UNIQUEIDENTIFIER;
    DECLARE @RequestMsg NVARCHAR(100);

    BEGIN TRANSACTION;

    BEGIN DIALOG CONVERSATION @InitDlgHandle
    FROM SERVICE [//InitDB/Identity/InitiatorService]
    TO SERVICE '//TgtDB/Identity/TargetService'
    ON CONTRACT [//BothDB/Identity/SimpleContract]
    WITH ENCRYPTION=OFF;

    SELECT @InitDlgHandle

    SELECT @RequestMsg =
    '<RequestMsg>Message for Target service.</RequestMsg>';


    SEND ON CONVERSATION @InitDlgHandle
    MESSAGE TYPE [//BothDB/Identity/RequestMessage]
    (@RequestMsg);

    SELECT @RequestMsg AS SentRequestMsg;

    COMMIT TRANSACTION;
    GO

    ---------------------------

    --THIS IS THE PART THAT DOESN'T WORK. DOESN'T SEEM TO BE ANYTHING IN THE QUEUE.

    USE IdentityServiceBrokerDev;
    GO

    DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER;
    DECLARE @RecvReqMsg NVARCHAR(100);
    DECLARE @RecvReqMsgName sysname;

    BEGIN TRANSACTION;

    WAITFOR
    ( RECEIVE TOP(1)
    @RecvReqDlgHandle = conversation_handle,
    @RecvReqMsg = message_body,
    @RecvReqMsgName = message_type_name
    FROM IdentityTargetQueue
    ), TIMEOUT 1000;

    SELECT @RecvReqMsg AS ReceivedRequestMsg;

    IF @RecvReqMsgName =
    '//BothDB/Identity/RequestMessage'
    BEGIN
    DECLARE @ReplyMsg NVARCHAR(100);
    SELECT @ReplyMsg =
    '<ReplyMsg>Message for Initiator service.</ReplyMsg>';

    SEND ON CONVERSATION @RecvReqDlgHandle
    MESSAGE TYPE
    [//BothDB/Identity/ReplyMessage] (@ReplyMsg);

    END CONVERSATION @RecvReqDlgHandle;
    END

    SELECT @ReplyMsg AS SentReplyMsg;

    COMMIT TRANSACTION;
    GO

    --------------------------------------------

    USE IdentityDev;
    GO

    DECLARE @RecvReplyMsg NVARCHAR(100);
    DECLARE @RecvReplyDlgHandle UNIQUEIDENTIFIER;

    BEGIN TRANSACTION;

    WAITFOR
    ( RECEIVE TOP(1)
    @RecvReplyDlgHandle = conversation_handle,
    @RecvReplyMsg = message_body
    FROM IdentityInitiatorQueue
    ), TIMEOUT 1000;

    END CONVERSATION @RecvReplyDlgHandle;

    -- Display recieved request.
    SELECT @RecvReplyMsg AS ReceivedReplyMsg;

    COMMIT TRANSACTION;
    GO

    ----------------------------------------------------

  2. #2
    Join Date
    Nov 2005
    Posts
    122
    If service broker is unable to deliver the message for any reason, you will find it in the sys.transmission_queue catalog view. There should be a row for each message that service broker is unable to deliver, along with a error message telling you the reason.

    By the way, have you created a master key in master and the two databases? Follow this link for instructions: CREATE MASTER KEY (Transact-SQL)

    rusanu.com has many good service broker tutorials. Check them out.

  3. #3
    Join Date
    Apr 2010
    Posts
    5
    Quote Originally Posted by kaffenils View Post
    If service broker is unable to deliver the message for any reason, you will find it in the sys.transmission_queue catalog view. There should be a row for each message that service broker is unable to deliver, along with a error message telling you the reason.

    By the way, have you created a master key in master and the two databases? Follow this link for instructions: CREATE MASTER KEY (Transact-SQL)

    rusanu.com has many good service broker tutorials. Check them out.
    Thanks. I had already figured it out but you suggestions were right on. It was a master key issue. Thanks again.

Posting Permissions

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