So I’ve been working on a project that utilizes filtered indexes and decided I needed to build an automated deployment routine. I had built a set of build scripts, and utilizing SQLCMD mode in SSMS to allow for include files, built a master script to perform the entire build.
During development and initial testing I ran this in SSMS and everything worked fine, but for automation purposes I used SQLCMD.EXE. As soon as I started using SQLCMD Is started to get errors indicating that the QUOTED_IDENTIFIER was incorrectly set for indexed views, filtered indexes etc.
Now I’m aware that indexed views among other objects are picky about their settings and the error message let me know that this was the case for filtered indexes as well. However, the question was, why hadn’t it come up before? In looking through the code I only found one reference where QUOTED_IDENTIFIER was set to OFF and that was in the create database script. Even after changing that the errors were still showing up.
It turns out that by default, a connection through SSMS sets QUOTED_IDENTIFIER to ON, while a connection through SQLCMD sets it to OFF. In SQLCMD it does this after each connect statement, so setting it at the beginning of a script may not cover it if you subsequently run a connect statement after it. The best practice is to run the set statements after each connect.
Reading through the Remarks section of the following MSDN article, SET QUOTED_IDENTIFIER (Transact-SQL), states that the defaults are set differently for different connection types (DB-Library, SQL Server Native Client, ODBC etc.) and thus indicates that SQLCMD and SSMS are probably using different connection types.