Yesterday I added some indexes on a view in my MSSQL 2008 db. After that it seems like all the store procedures need to run with QUOTED_IDENTIFIER set to ON, even those that don't use the view in question.
Why is it so? Is this something I can configure on the db or do I have to update all my stored procedures to set the QUOTED_IDENTIFIER to ON? I think it is rather weird that this is required for the stored procedures not using the view.
Do these stored procedures relate to the base table(s) that the view is based upon? To quote from Creating Indexed Views:
After the clustered index is created, any connection that tries to modify the base data for the view must also have the same option settings required to create the index. SQL Server generates an error and rolls back any INSERT, UPDATE, or DELETE statement that will affect the result set of the view if the connection executing the statement does not have the correct option settings. For more information, see SET Options That Affect Results.
And it's kind of obvious, when you think about it - you're potentially going to be updating the contents of the view whenever you touch these base tables, and so you inherit the same responsibilities as when you created the index.
You can set the defaults at multiple levels:
Any application can explicitly override any default settings by executing a SET statement after it has connected to a server. The SET statement overrides all previous settings and can be used to turn options on and off dynamically as the application runs. The option settings are applicable only to the current connection session.
OLE DB and ODBC applications can specify the option settings that are in effect at connection time by specifying option settings in connection strings. The option settings are applicable only to the current connection session.
SET options specified for a SQL Server ODBC data source by using the ODBC application in Control Panel or the ODBC SQLConfigDataSource function.
Default settings for a database. You can specify these values by using ALTER DATABASE or the Object Explorer in SQL Server Management Studio.
Default settings for a server. You can specify these values by using either sp_configure or Object Explorer in SQL Server Management Studio to set the server configuration option named user options.