Subtle SqlDependency Notification Issue

While working on a SQL Server change notification library (for cache invalidation) recently, the SqlDependency change notification handler kept receiving a notification with SqlNotificationEventArgs.Info = Options, SqlNotificationEventArgs.Source = Statement and SqlNotificationEventArgs.Type = Subscribe thus failing with the subscription process. Using the lead SqlNotificationEventArgs.Info = Options, further diagnosis revealed that one of the SET options required for SQL Server query notification was not correct. The offending option was ARITHABORT, which was set to OFF (connection default) but should be ON. Except this, other SET options were correctly set by default however.

The obvious solution is to explicitly turn on ARITHABORT on the connection that would be used for notification subscription.

Subtle SqlDependency Error

Please note that ARITHABORT should be set before running the SQL query to be monitored, otherwise the subscription process will still fail as above.