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.
Please note that
ARITHABORT should be set before running the SQL query to be monitored, otherwise the subscription process will still fail as above.