Service Broker Demystified - Case-Sensitivity

Some SSB objects are case-sensitive, but which ones?  Shouldn't they all follow the case sensitivity of your server or database?  The answer:  No.  Only "exposed" SSB objects are case-sensitive.  This post will cover why.  

I've heard comments that people don't want to use Service Broker because it is too confusing. I started a blog series called Service Broker Demystified to dispel these false notions. SSB is actually very easy to use and understand.  Today we are going to cover why some SSB objects are case-sensitive.  But first let's take a look at why there is confusion.  

I created a repro script that you can use to follow along.  First we are going to create a new database that is case insensitive and enable SSB.  

Next we create a few standard SSB objects...message types, contracts, queues, and services.  All of this will work because we have matched the case of the objects such that a binary compare or case-sensitive search will find the objects.  

But what if we accidentally get the case of our object names incorrect?  In almost every case, your command will fail.  Here we specify the contract name incorrectly:  

...and we also specified the message type name incorrectly and also generated an error.  

Here is the single case where you can get the case-sensitivity wrong but the command will still succeed:  

Queue names are the single exception where case does NOT matter in SSB.  In the example above we are querying MYQUEUE and setting mYqUEUE as the storage for MyNewService.  In both cases that is legal.  The reason is simple:  all exposed SSB objects must follow case-sensitive rules.  And all SSB objects are exposed EXCEPT queue names.  An "exposed" object is an object that can be named during a BEGIN DIALOG or SEND statement.  Queues are the only SSB objects that are never known to the caller, they are hidden behind the service.  You can query a queue just like you query a table, but you cannot specify it while conversing.  Contracts, services, and message types form the public interface to your SSB implementation.  

Why does it work this way?

Service Broker is meant to send and receive messages across SQL Server instance boundaries.  In other words, I can send a message from my instance to an instance sitting on the other side of the world.  The problem is that I have no idea what the database collation of the Target service is.  The designers of SSB realized this and made it a rule that SSB object names must adhere to the least common denominator.  Therefore, SSB object names are case-sensitive...to be more specific the names must match when converted to binary.  Even if your SSB design is totally intra-database and you never deal with remote databases your object names must adhere to this rule.  

You have just read "Service Broker Demystified - Case-Sensitivity" on davewentzel.com. If you found this useful please feel free to subscribe to the RSS feed.