Worldwide implementation of QUOTED_IDENTIFIER
I have implemented a mechanism using SqlDependency that alerts me to any changes to a specific database table.
But it breaks my existing functionality when I update the database table on which I have implemented SqlDependency.
I am getting the following error:
UPDATE failed because the following SET parameters are invalid Settings: QUOTED_IDENTIFIER. Make sure the SET parameters are correct for use with indexed views and / or indexes on computed columns and / or filtered indexes and / or query notifications and / or XML data type methods and / or spatial index operations.
This is similar to " sqldependency-causes-error-in-other-application ".
I found out that the proc that does the update on the table was created with QUOTED_IDENTIFIER set to OFF and that is the exact cause of the problem.
If I change the proc with SET QUOTED_IDENTIFIER ON everything works fine. But this is not a feasible solution, since I will have to search for all processes and change them.
I want to know if there is a general way to call SET SET QUOTED_IDENTIFIER ON every time before my proc is executed from the application code.
My application is an ASP.Net 4 web application and I am using Microsoft Enterprise Library 5.0 to perform database operations. In which I am using the following component: Microsoft.Practices.EnterpriseLibrary.Data.Database
Any SqlDependency alternatives are also welcome.
[edit] There is also a direct way in SQL Server 2008 to directly set QUOTED_IDENTIFIER to true without changing the proc. I think this is to write a script that will set QUOTED_IDENTIFIER for all objects that depend on this table (using sp_depends).
source to share
There is another way if you are still looking for it, admittedly not the most self-documenting one.
EXEC sys.sp_configure N'user options', N'256'
GO
RECONFIGURE WITH OVERRIDE
GO
This is what SSMS does if you right click on the database, navigate to properties, connections, and then select selected ids from the list. It forces sql server to set the quoted id parameter as enabled for all incoming connections.
source to share