none
App receiving "Options" message from Service Broker

    Question

  • 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.

    Thursday, November 02, 2006 3:09 PM

Answers

  •  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

    HTH,
    ~Remus

    Thursday, November 02, 2006 4:36 PM
    Moderator

All replies

  •  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

    HTH,
    ~Remus

    Thursday, November 02, 2006 4:36 PM
    Moderator
  • 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!

    Thursday, November 02, 2006 6:18 PM
  • 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

    Thursday, November 02, 2006 6:50 PM
    Moderator
  • 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.

    Thursday, November 02, 2006 7:20 PM
  •  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

    Thursday, November 02, 2006 9:51 PM
    Moderator
  • 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.)

    Friday, November 03, 2006 1:28 PM