I am developing an application with SQL Dependency feature.
My problem is when I stop application (SqlDependency.Stop() method is executed) and I try to run stored procedure with ANSI_NULLS and QUOTED_IDENTIFIER set to OFF, I'm getting error:
UPDATE failed because the following SET options have incorrect settings: 'ANSI_NULLS, QUOTED_IDENTIFIER'...
Stored procedure is making an update on table which was watched by SqlDependency, I do not want to change it because it is from third-party software.
SqlDependency uses 'sa' user to connect to DB also I tried other user with no luck.
For now I must restart SQL Server to make it work, but this is unacceptable.
I tried to run this query and kill all subscription:
KILL QUERY NOTIFICATION SUBSCRIPTION @Id
but I still get same error.
Can someone help me with resolving this problem ?