Fixing SqlDependency Query Notification subscription failure: info="set options" database_id="0" in SQL Server 2012

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.

image

Fix: Microsoft OneNote 2013 on Windows 8 keeps on asking for a password to sync a notebook to a SharePoint site.

Microsoft OneNote 2013 on Windows 8 kept on asking for a password to sync a notebook to a local SharePoint site.

This was caused by a Windows 2008 domain controller not pushing domain policy settings to a Windows 8 client.

The Windows 8 client didn’t have the SharePoint site added to the local intranet zone.

 

To fix this problem I had to manually add the http://*.mydomain.com and https://*.mydomain.com to the local intranet zone:

 

Control panel > Internet Options > tab:Security > Local Intranet > Sites > Advanced

Uncheck "Automatically detect intranet network" and add your intranet sites:

 

image

Fix: Forms authentication timeout after 20 minutes, even if timeout is set to 1440 minutes

If you are using ASP .NET with forms authentication and set the forms authentication to 1440 minutes, this authentication will by default expire after 20 minutes, when the server gets no request during these 20 minutes. This is controlled by the application pool setting [Idle Time-out (minutes)]:

 

image

 

Settings this value to 1440 and setting the forms authentication timeout in the web.config to 1440, solved my problem.

<authentication mode="Forms">
<forms loginUrl="~/Account/Login.aspx" timeout="1440" protection="All" defaultUrl="Default.aspx"/>
</authentication>

Found my solution in one of the comments on:

http://www.codeproject.com/Questions/163203/Forms-Authentication-and-or-Session-timeout

 

Notes

  • Changes to application pool settings are stored in the applicationhost.config file, stored at C:\Windows\System32\inetsrv\config\applicationHost.config