SQL Service Broker Compatibility Level Issue

Spent about 3 hours troubleshooting why SQL Service Broker wouldn't work in production when it was working fine on two dev boxes with nearly identical configurations. The only indication there was an issue was that as soon as I'd create a Subscription the OnChange event would fire.

The SqlNotificationEventArgs contained the following:
Type:Subscribe
Source:Statement
Info:Options

Finally got a clue as to what this meant from these two sources:

1) The quote below from this page at MSDN clued me in that my TSQL was not valid (which meant there must be more to the story since the same statement works in dev)

Statement : The Transact-SQL statement is not valid for notifications; for example, a SELECT statement that could not be notified or a non-SELECT statement was executed.

2) I focused in on why SQL Server didn't like my "SET OPTIONS" and found the quote below from this page

I ran a compare between the two databases.

The local one, in 80-compatibility, needed the various SET options.

The remote one, in 90-compatibility, needed nothing extra to work.

When I changed the local one to 90-compatibility, the program magically worked, with or without the SET options.

Then I realized the Production environment had been upgraded from SQL 2000 in the past and it was likely I was running in "80-compatibility". Sure enough I was, and changing to "90-compatibility" fixed my issues as well!

3 hours down the drain but learned a little more about Service Broker debugging and troubleshooting.

Service Broker Troubleshooting by Jeremy Kadlec was excellent and so was Using and Monitoring SQL 2005 Query Notification by Sanchan Sahai Saxena

Note to self: Use SQL Server Profiler more.