An Introduction to the Service Broker

  • Hello, great article, compact & clear. On my june CTP version, it didn't seem to work without the following change (maybe update the article ? )

    DECLARE @conversationHandle UNIQUEIDENTIFIER

    DECLARE @message NVARCHAR(100)

    BEGIN

      BEGIN TRANSACTION;

      BEGIN DIALOG @conversationHandle

            FROM SERVICE Sender

            TO SERVICE 'Receiver'

            ON CONTRACT HelloContract WITH ENCRYPTION=OFF, LIFETIME= 600;

      -- Send a message on the conversation

      SET @message = N'Hello, World';

      SEND  ON CONVERSATION @conversationHandle

            MESSAGE TYPE HelloMessage (@message)

      COMMIT TRANSACTION

    END

    GO

    regards,

    steph

     

  • Hello

    I too am having problems getting this sample to work.

    I created a db called TestDb.

    I then pasted the sample service broker code (with your ENCRYPTION change)

    into the mgmt studio. I modified the USE statement at the top.

    There is never anything in the ReceiverQueue.

    Everyone tries their sample code against the AdventureWorks db. I suspect that

    db has some property set that we are not being told about.

    Could you try the sample code after you create a new database.

     

    Thanks.

  • Hi,

    I tried your example but could not able to receive the message back. Pelase guide what I am doing wrong. Receive statement does not return any data back.

    USE master;

    GO

    CREATE ENDPOINT BrokerEndpoint

    STATE = STARTED

    AS TCP ( LISTENER_PORT = 4037 )

    FOR SERVICE_BROKER ( AUTHENTICATION = WINDOWS ) ;

    Go

    Use MySampleDB

    Go

    Create MESSAGE TYPE HelloMessage VALIDATION = None

    GO

    Create CONTRACT HelloContract ( HelloMessage SENT BY INITIATOR )

    GO

    Create Queue SenderQueue

    GO

    Create QUEUE ReceiverQueue

    GO

    Create Service Sender ON QUEUE SenderQueue

    GO

    Create Service Receiver ON QUEUE ReceiverQueue (HelloContract)

    GO

    DECLARE @conversationHandle UNIQUEIDENTIFIER

    DECLARE @message nvarchar(100)

    Begin

    Begin Transaction;

    Begin Dialog @conversationHandle

    From Service Sender

    TO Service 'Receiver'

    ON CONTRACT HelloContract

    Set @Message = N'Hellow, World';

    Send ON Conversation @conversationHandle MESSAGE TYPE HelloMessage (@message)

    Commit Transaction

    End

    Go

    Receive Convert(Nvarchar(max),message_body) as message

    From ReceiverQueue

    GO

    Select * from SenderQueue

    Select * from ReceiverQueue

    Select * from dbo.ServiceBrokerQueue

  • hbatra -

    I have just developed a C# app that does exactly what you explained (ie, writes from an MSMQ queue to a SQL Server table).  Do you mind sharing how you run this app?  As a scheduled task, a job in SQL Server, a service???  Just wondering.  Thanks.

    Steve

  • Nice article, but I'm not getting anything when I execute posted code.

    Does anybody have an idea why?

    Thanks

  • I couldn't get any result too.

    Anyway, thanks for sharing your exp.

    Patrick

  • I was not getting any results either in a new database until I ran this script to enable the serice broker in my database:

    ALTER DATABASE [DatabaseNameHere] SET ENABLE_BROKER

    from 2005 books online:

    ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/ac7e4c7c-e52f-4883-8f3c-9336cc77a9c8.htm

    hope that helps....

  • It is enabled in my database, and still nothing.

  • Here are the steps that worked (finally) for me:

    1. Login as 'sa' on your local sql 2005 server.
    2. Create a new database
    3. In a new query window, run this script: ALTER DATABASE [DatabaseNameHere] SET ENABLE_BROKER (should return: Command(s) completed successfully.)
    4. Copy and paste script from 'Intro...to Service Broker' article into a new query window.
    5. Make sure you're in the newly created database and comment out 'USE AdentureWorks' line at the top.
    6. Add this snippet (from a previous post) ... WITH ENCRYPTION=OFF, LIFETIME= 600; to the end of line #37. (the section that starts with BEGIN DIALOG @conversationHandle ....ON CONTRACT HelloContract [here])
    7. Run entire script on your newly created database and it should return the 'Hello world' message.

    If not, do a select * from sys.transmission_queue to see what errors were generated.

    Hope that helps....

  • I finally got this to work.  How do I get rid of messages in sys.transmission_queue? 

  • Great article, but something went wrong when I ran the sample code - it returns an empty message.

    ??

    BR

    Peter Pirker

  • Hi Srinivas...this was an excellent pice of code... can I pls. have your mail id?? I've some further queries to be clarified..my mail ids are : kbagchi@careindia.org & kingshukbagchi@rediffmail.com .

    Thnaks is advance,

    Kingshuk.

  • Here are the steps that worked (finally) for me:

    Login as 'sa' on your local sql 2005 server.

    Create a new database

    In a new query window, run this script: ALTER DATABASE [DatabaseNameHere] SET ENABLE_BROKER (should return: Command(s) completed successfully.)

    Copy and paste script from 'Intro...to Service Broker' article into a new query window.

    Make sure you're in the newly created database and comment out 'USE AdentureWorks' line at the top.

    Add this snippet (from a previous post) ... WITH ENCRYPTION=OFF, LIFETIME= 600; to the end of line #37. (the section that starts with BEGIN DIALOG @conversationHandle ....ON CONTRACT HelloContract [here])

    Run entire script on your newly created database and it should return the 'Hello world' message.

    If not, do a select * from sys.transmission_queue to see what errors were generated.

    Hope that helps....

    I ran the following:

    ALTER DATABASE AdventureWorks SET ENABLE_BROKER

    Then, after running the following, I still get no results:

    -- We will use adventure works as the sample database

    USE AdventureWorks

    GO

    -- First, we need to create a message type. Note that our message type is

    -- very simple and allowed any type of content

    CREATE MESSAGE TYPE HelloMessage

    VALIDATION = NONE

    GO

    -- Once the message type has been created, we need to create a contract

    -- that specifies who can send what types of messages

    CREATE CONTRACT HelloContract

    (HelloMessage SENT BY INITIATOR)

    GO

    -- The communication is between two endpoints. Thus, we need two queues to

    -- hold messages

    CREATE QUEUE SenderQueue

    CREATE QUEUE ReceiverQueue

    GO

    -- Create the required services and bind them to be above created queues

    CREATE SERVICE Sender

    ON QUEUE SenderQueue

    CREATE SERVICE Receiver

    ON QUEUE ReceiverQueue (HelloContract)

    GO

    -- At this point, we can begin the conversation between the two services by

    -- sending messages

    DECLARE @conversationHandle UNIQUEIDENTIFIER

    DECLARE @message NVARCHAR(100)

    BEGIN

    BEGIN TRANSACTION;

    BEGIN DIALOG @conversationHandle

    FROM SERVICE Sender

    TO SERVICE 'Receiver'

    ON CONTRACT HelloContract

    WITH ENCRYPTION=OFF, LIFETIME= 600;

    -- Send a message on the conversation

    SET @message = N'Hello, World';

    SEND ON CONVERSATION @conversationHandle

    MESSAGE TYPE HelloMessage (@message)

    COMMIT TRANSACTION

    END

    GO

    -- Receive a message from the queue

    RECEIVE CONVERT(NVARCHAR(max), message_body) AS message

    FROM ReceiverQueue;

    -- Cleanup

    DROP SERVICE Sender

    DROP SERVICE Receiver

    DROP QUEUE SenderQueue

    DROP QUEUE ReceiverQueue

    DROP CONTRACT HelloContract

    DROP MESSAGE TYPE HelloMessage

    GO

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • Here are the steps that worked (finally) for me:

    Login as 'sa' on your local sql 2005 server.

    Create a new database

    In a new query window, run this script: ALTER DATABASE [DatabaseNameHere] SET ENABLE_BROKER (should return: Command(s) completed successfully.)

    Copy and paste script from 'Intro...to Service Broker' article into a new query window.

    Make sure you're in the newly created database and comment out 'USE AdentureWorks' line at the top.

    Add this snippet (from a previous post) ... WITH ENCRYPTION=OFF, LIFETIME= 600; to the end of line #37. (the section that starts with BEGIN DIALOG @conversationHandle ....ON CONTRACT HelloContract [here])

    Run entire script on your newly created database and it should return the 'Hello world' message.

    If not, do a select * from sys.transmission_queue to see what errors were generated.

    Hope that helps....

    Thanks, it does work on a new db, but why does it not work on AdventureWorks, even after enabling the Service Broker??

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • This has actually worked for me... have you done "alter database [database name] set enable_broker"?

    Kingshuk

Viewing 15 posts - 16 through 30 (of 34 total)

You must be logged in to reply to this topic. Login to reply