Starting Out with Service Broker
November 16, 2008
Problem: you have an application that needs to trigger some process via SQL Server but don't want your main process hung up waiting. So you decide to setup Service Broker in order to make an asynchronous call, with the receiving service doing your work for you. You've read all about it, and tried it out after hours, but it didn't work. What gives?
Solution: obviously, this situation occurred with your humble blogger. I had a few different things going on, but once it was solved, how sweet it was.
First thing is, what's happening? Anything? I couldn't see any sign that
anything was occurring. The SQLTeam blog
pointed out that there are a number of events that can be monitored in the
SQL Profiler. Thanks to the Profiler, I could see this error:
Could not obtain information about Windows NT group/user… .
Working from home, and apparently it was trying to validate me, even though I've
never had any other problems with the database. Found an MSDN
forum posting that helped me solve this by resetting the owner account to sa
instead of me.
Getting closer now. Services are firing, but nothing is happening. I created
the service with Activation ON, but the stored procedure tied to the service
queue just isn't being called. Maybe I missed something. Look to the
documentation, and there it is: the examples I had read somehow failed to
mention how to turn the service itself on (though they did include this
important activation on bit). ALTER QUEUE dbo.MyQueue WITH STATUS = ON;,
and try again. That does it. Service now working :-).