October 3, 2013 | Posted in: SQL Server

Updated: 28/06/2019 :: Consider the following URL to see it helps in resolving messages not being received on the target due to a likely connection issue or if you are seeing “Service Broker received an error message on this conversation. Service Broker will not transmit the message” on the initiator >>>


Original post:

Having probed SQL Server Service Broker for some time trying to diagnose a fault with it’s configuration, I soon found a tool that would do this for me in the tools folder under the installation directory called ssbdiagnose.exe. Below is an example of how to query the configuration of your service broker setup (it will diagnose and return any faults):

Run command prompt (I ran this from machine as I had SQL Service 2008 installed which this tool is part of):

cd C:\Program Files\Microsoft SQL Server\100\Tools\Binn

C:\Program Files\Microsoft SQL Server\100\Tools\Binn>ssbdiagnose -u username -p password -s “server ip” -d “databasename” CONFIGURATION FROM SERVICE ”
http://fromservice” TO SERVICE “http://toservice” ON CONTRACT “http://contract”

The results may look something like the following:

Microsoft SQL Server 10.50.1600.1
Service Broker Diagnostic Utility
D  29997     Database1      Service Broker GUID is identical to tha
t of database Database1 on server
D  29975     Database1 User dbo does not have SEND per
mission on service http://someservice
D  29964     Database2 The master key was not found
3 Errors, 0 Warnings

C:\Program Files\Microsoft SQL Server\100\Tools\Binn>

It’s also worth noting that you can run ssbdiagnose against a SQL Server 2005 instance.

Be the first to comment.

Leave a Reply


1 + twenty =

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>