Starting Out with Service Broker
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 outthat
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 (dead link removed; SF 2025) 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.