Post reply

Service Broker, can''t send a message

  • March 9, 2007 at 10:57 am

    #176506

    I'm trying to get service broker working. I have SS 2005 Developer SP2a on XP Pro. I already ran ALTER DATABASE dbname SET ENABLE_BROKER

    DDL:

    CREATE

    QUEUE PlcMessageQueue WITH STATUS = ON

    CREATE SERVICE PlcMessenger ON QUEUE PlcMessageQueue

    Test SQL:

    DECLARE

    @conversation AS UNIQUEIDENTIFIER;

    BEGIN DIALOG @conversation FROM SERVICE PlcMessenger TO SERVICE 'PlcMessenger';

    SEND ON CONVERSATION @conversation ('1');

    END CONVERSATION @conversation;

    RECEIVE * FROM PlcMessageQueue;

    The queue is empty.

    There are no errors reported. I've tried many variations on this script. This is the most simplified I can make it. Microsoft's documentation is vague and I'm having trouble finding examples of actual usage on the web.

    Any ideas?

  • March 9, 2007 at 2:21 pm

    #694195

    I'm closer to solving this problem but I'm still having issues.

    I discovered where the errors are.

    SELECT * FROM sys.transmission_queue

    First, although no routing is required by the BEGIN DIALOG statement, routing is apparently required to actually send a message. I changed it to:

    BEGIN

    DIALOG @conversation FROM SERVICE PlcMessenger TO SERVICE 'PlcMessenger', 'CURRENT DATABASE';

    Now, I'm getting an error (only reported in transmission_queue) that I'm not sure about:

    Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.

    I'm connecting through Windows authentication using an account that has all the rights of sa. The user dbo created all the objects and is running all the queries. How can I not have permission?

    What kind of "principal" do I need and what do I do with it?

  • March 9, 2007 at 3:44 pm

    #694202

    I got it to work!! There are so many little gotchas with Service Broker. I've made a huge mess of my schema trying all sorts of manipulations. Anyway, this is what happened.

    The database was restored from an instance of SQL Server 2000 from another organization, then had the compatibility level set to 90. I did this a while ago. "dbo" didn't map to a user that still existed in the current system. I fixed that with the following:

    ALTER

    AUTHORIZATION ON DATABASE::dbname TO [sa];

    I would never have found that issue myself. It was on a Service Broker forum:

    http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=91&SiteID=1

    Then it still didn't work. I had to add the default contract to the target service. And, I had to use brackets because default is a reserved word.

    ALTER

    SERVICE testService (ADD CONTRACT [DEFAULT])

    I finally have a test message that made it from one service to another. It only took me two work days! Examples of working Service Broker code would be really useful. Maybe when I'm done with this app I can take my own advice and find a place to post it. Hmmm.

    So I solved my own problem (so far). I just wanted to post the solution for anyone who was wondering what the problem was.

Viewing 3 posts - 1 through 2 (of 2 total)

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