4

I've implemented a mechanism with the help of SqlDependency that alerts me of any changes in a particular database table.

But this breaks my existing functionality where I'm updating the database table on which I've implemented SqlDependency.

I'm getting the following error:

UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options 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've found out that the proc that performs the update on the table has been created with QUOTED_IDENTIFIER set to OFF and this is the exact cause of the issue.

If I alter the proc with "SET QUOTED_IDENTIFIER ON" everything works perfect. But this is not a feasible solution as I'll have to search for all the procs and need to alter them.

What I want to know is there a generic way to call "SET QUOTED_IDENTIFIER ON" everytime before my proc is executed from the application's code.

My application is an ASP.Net 4 web app and I am using Microsoft Enterprise Library 5.0 for performing database operations. In which I'm using the following component: Microsoft.Practices.EnterpriseLibrary.Data.Database

Any alternate solutions to SqlDependency are also welcome.

[edited] Also is there any direct way in SQL Server 2008 to directly set QUOTED_IDENTIFIER to true without altering the proc. What I'm thinking is to write a script that will set QUOTED_IDENTIFIER to all the objects dependent on that table (using sp_depends).

Community
  • 1
  • 1
Adi
  • 123
  • 1
  • 2
  • 6
  • "call `SET QUOTED_IDENTIFIER ON` everytime before my proc is executed from the application's code." - that's **not** what you want. You've correctly identified that you need to change the stored procs: "When a stored procedure is created, the [`SET QUOTED_IDENTIFIER`](http://msdn.microsoft.com/en-us/library/ms174393.aspx) and `SET ANSI_NULLS` settings are captured and used for subsequent invocations of that stored procedure – Damien_The_Unbeliever Feb 16 '13 at 06:24
  • Here's a [similar question](http://stackoverflow.com/questions/2147489/change-the-ansi-nulls-setting-for-all-stored-procedures-in-the-database) from someone who wanted to change the `ANSI_NULLS` on all of their procedures (found by searching for "change stored procedure quoted_identifier setting") – Damien_The_Unbeliever Feb 16 '13 at 06:26
  • Thanks Damien. I have created a SQL script for doing the same now. Post your above comment as answer, I'll mark it and close this question. – Adi Feb 22 '13 at 09:40

1 Answers1

1

There is another way if you are still looking for it, admittedly it's not the most self-documenting.

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, go to properties, connections, and then select quoted identifiers from the list. It makes sql server set the quoted identifier option as on for all incoming connections.

jwhaley58
  • 973
  • 11
  • 24