Troubleshooting Query Notifications: Transact-SQL Statement Succeeded Without Notification, Failed with Notification
If a Transact-SQL statement succeeds when no notification subscription is active, but fails when a notification subscription is active, this often indicates that the SET options on the connection executing the statement are incompatible with query notifications. In this case, the text of the error returned includes the name of the incompatible option.
To correct the problem, ensure that connections that produce query notifications have SET options configured. For more information, see Planning for Notifications.