SqlDependency causes error in other application

Question

I have a project where I need to monitor changes in a 3rd party database.

SqlDependency seem like a good solution but it causes the following error in the 3rd party application.

INSERT failed because the following SET options have incorrect settings: 'ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_PADDING'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

(The application works fine when my test program below is not running)

What SET options does this refer to?

The only set operation I have done is ALTER DATABASE TestDb SET ENABLE_BROKER to enable notifications.

I also did:

CREATE QUEUE ContactChangeMessages;

CREATE SERVICE ContactChangeNotifications
    ON QUEUE ContactChangeMessages
    ([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]);    

Here is my Linqpad test code which works fine if I insert/update/delete records in management studio.

void Main() {
    const string cs = "Data Source=.;Initial Catalog=TestDb;Trusted_Connection=True";

    var are = new AutoResetEvent(false);
    using (var connection = new SqlConnection(cs)) {
        connection.Open();
        SqlDependency.Start(cs);
        using (var cmd = new SqlCommand()) {
        cmd.Connection = connection;
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = "SELECT orderNo FROM dbo.Orders WHERE ProductNo = '111'";

            var dep = new SqlDependency(cmd, null, 60);
    dep.OnChange += (s,e) => { 
                Console.WriteLine(e.Info);
                are.Set();
            };
            using (var reader = cmd.ExecuteReader()) {
                while (reader.Read()) {
                }
            }
            are.WaitOne();
            SqlDependency.Stop(cs);
        }
    }
}

I do not know, and can not change, how the 3rd part app connects to the database. I can run the sql profiler if more information is required.

Answer 1

It refers exactly to the SET options mentioned in the error message:

SET options have incorrect settings: 'ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_PADDING'.

The correct settings, along with other restrictions, are described in Creating a Query for Notification:

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

Note Note

Setting ANSI_WARNINGS to ON implicitly sets ARITHABORT to ON when the database compatibility level is set to 90. If the database compatibility level is set to 80 or earlier, the ARITHABORT option must explicitly be set to ON.

These settings are affected by:

• the current database settings, which can be viewed in sys.databases
• the session settings, which can be viewed in sys.dm_exec_sessions
• by procedure/trigger create settings, which can be viewed using OBJECTPROPERTY().

You need to find which property from the ones mentioned in the error message is non-conforming and why (probably is a database setting). Most likely is a 80 compatibility level set on the database.

Update. Nevermind that, you say that you can successfully create a query notification but then the application itself fails. The application must be explicitly setting one of these settings OFF on it's connection (you can validate by inspecting sys.dm_exec_sessions). You must contact the application vendor, seems like she is very explicitly (albeit probably unintentionally) making his application incompatible with query notifications.