Knowledgebase

Registering a query for Query Notification requires the database connection on which the subscription is made to have the correct SET OPTIONS enabled:

ANSI_NULLS ON
ANSI_PADDING ON
ANSI_WARNINGS ON
CONCAT_NULL_YIELDS_NULL ON
QUOTED_IDENTIFIER ON
NUMERIC_ROUNDABORT OFF
ARITHABORT ON

In my case the SET OPTION ARITHABORT was set to OFF.

When tracing the Microsoft SQL Server 2012 database with the SQL Server Profile I noticed the following record:

<qnev:QNEvent xmlns:qnev="http://schemas.microsoft.com/SQL/Notifications/QueryNotificationProfiler">
    <qnev:EventText>subscription fired</qnev:EventText>
    <qnev:SubscriptionID>0</qnev:SubscriptionID>
    <qnev:NotificationMsg>
        &lt;qn:QueryNotification
        xmlns:qn="http://schemas.microsoft.com/SQL/Notifications/QueryNotification"
        id="0"
        type="subscribe"
        source="statement"
          info="set options"
        database_id="0"
        sid="0x01"&gt;
        &lt;qn:Message&gt;
        62ed343a-0149-4774-8dd1-b3da8f5a4840;
        fc208372-6da2-4083-9866-7ac9fb9e5d38&lt;
        /qn:Message&gt;&lt;
        /qn:QueryNotification&gt;
    </qnev:NotificationMsg>
    <qnev:BrokerDlg>BFCF056A-C16B-E211-B799-005056A36108</qnev:BrokerDlg>
</qnev:QNEvent>

And when I enabled more events in the trace, I found a record just before this subscription failure message:

— network protocol: TCP/IP
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed

So now I knew the SET OPTION arithabort was wrong.

Solution

Setting the Database option Arithmetic Abort Enabled to True fixed the problem.