My work recently upgraded our SQL Server edition from 2005 to 2012.
This has caused some of our stored procedures to stop working, where they reference views which were created with SET QUOTED_IDENTIFIER ON.
This is because these stored procedures themselves were created with SET QUOTED_IDENTIFIER OFF (see below).
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vw_User]
AS
SELECT UserId, LoginId, FirstName, LastName FROM "User" -- no real-world need for quoted identifiers here, just for the sake of this example
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROC [dbo].[getUserById]
@userId int
AS
SELECT LoginId, FirstName, LastName FROM [vw_User] WHERE UserId = @userId
GO
With SQL Server 2005 this was not a problem, but in SQL Server 2012 running any of these procs will generate the error:
SELECT 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.
Is there some relevant setting which may get changed as a result of such an upgrade, or some fundamental difference in the way that SQL Server 2012 handles the scope of the QUOTED_IDENTIFIER setting?
