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.