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.

Posted with : Tech, Microsoft SQL Server and other databases, SQL Server