Friday, March 30, 2012

Receiving queue does not fire stored procedure

I am doing my first SSB application where two different servers send messages to each other. The logic was all previously tested on a single server between two databases and it worked OK.

The problem I am having is that when the message is received at the target server (I see this in profiler), the stored procedure associated with the queue does not fire.

I see an acknowledgment fire back to the initiator, but it is like the target server does nothing with the initial message.

Any ideas on how I can further troubleshoot this? FWIW, I used the setup tool provided by RemusResanu to set up the routes and service bindings.

Thanks for any help!
John
Here is what my target server shows in profiler

Event Class/Sub Class

Broker:Conversation Group 1 - Create
Broker:Conversation 12 - Dialog Created
Broker:Conversation 6 - Received Sequenced Message
Broker:Remote Message Acknowledgement 3 - Message with Acknowledgement Received
Broker:Message Classify 2 - Remote
|||Also, when I query the queue, I can see all of the messages just sitting there. They are reaching the right place, just nothing happens from there.

Please help!
John

Code Snippet


CREATE QUEUE [dbo].[SiteChangeNotifyReceiveQueue] WITH STATUS = ON , RETENTION = ON , ACTIVATION ( STATUS = ON , PROCEDURE_NAME = [dbo].[SiteChangeQueueReader] , MAX_QUEUE_READERS = 10 , EXECUTE AS N'sqlservices' ) ON [PRIMARY]


|||Have you tried to execute the procedure manually?
Can the user 'sqlservices' be impersonated? I.e. executing EXECUTE AS USER='sqlservices' works fine w/o errors?
Do you see any errors in the ERRORLOG file or in the NT application event log (eventvwr.exe)?|||Yes, I just tried that and it worked fine. In fact, it seemed to go in to the queue and process all of the pending messages.

So, here is the really strange part:

Now, everything works fine.

After I ran the SP manually, my messages come through and the SP fires by itself again.

I would really like it if someone could give me an explanation of this. Is this part of Service Broker enforcing the order of transactions- there were errors in my SP at one time. Perhaps the SSB was not processing the later messages until the old ones went through?

I don't t like it when I can't explain how things went from broken to working!
|||If the situation repeats please look at sys.dm_broker_queue_monitors and tell us what is the state of the queue with the problem.

No comments:

Post a Comment