App receiving "Options" message from Service Broker

I have an app receiving messages from SQL Service Broker when data is updated. (Messages are located at http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlnotificationinfo.aspx )

When I run this app against a remote SQL Server, I receive the message "Updated" which I expect.

But when I run the same app against the local machine SQL Server, I receive the message "Options".

Does anyone know if there are SQL Server options that must be set to certain values?
I can't seem to find anything that troubleshoots this message... either from a SQLServer- or a .NET standpoint.

JFoushee wrote:

Does anyone know if there are SQL Server options that must be set to certain values?
I can't seem to find anything that troubleshoots this message... either from a SQLServer- or a .NET standpoint.

From http://msdn2.microsoft.com/en-us/library/ms181122.aspx:

SET Option Settings

When a SELECT statement is executed under a notification request, the connection that submits the request must have the options for the connection set as follows:

ANSI_NULLS ON

ANSI_PADDING ON

ANSI_WARNINGS ON

CONCAT_NULL_YIELDS_NULL ON

QUOTED_IDENTIFIER ON

NUMERIC_ROUNDABORT OFF

ARITHABORT ON|||

On my local machine,
sp_configure 'user options' returns 0 for config_value .

So I ran the enumeration for the options above...

ANSI_NULLS ON 32
ANSI_PADDING ON 16
ANSI_WARNINGS ON 8
CONCAT_NULL_YIELDS_NULL ON 4096
QUOTED_IDENTIFIER ON 256
NUMERIC_ROUNDABORT OFF
ARITHABORT ON 64

4474

And then I ran this against my local machine...

sp_configure 'user options', 4474

and the program suddenly works!

|||

The recommended way of fixing this is to actualy run the SET options from the user connection. This way the app sets it's needed settings overwritting whatever defaults are in the database/instance.

HTH,
~ Remus

|||

Agreed, I was using the example to explain this wasn't the app's fault.
It still manages to work against the remote server with no intervention. (Why?)

Thanks for your assistance.

|||

JFoushee wrote:

It still manages to work against the remote server with no intervention. (Why?)

sp_configure changes the global instance level settings. I'd guess that the remote server and local server are different at the database level settings (ALTER DATABASE ... SET ...)

HTH,
~ Remus

|||

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.

(I downloaded the pubs database from MS for the example and attached to both local and remote.)