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.
Well, according to sqlcmd(1), “This utility uses ODBC to execute Transact-SQL batches.” According to SET QUOTED_IDENTIFIER(tsql), “The SQL Server Native Client ODBC driver and SQL Server Native Client OLE DB Provider for SQL Server automatically set QUOTED_IDENTIFIER to ON when connecting.” and “The default for SET QUOTED_IDENTIFIER is OFF for connections from DB-Library applications.”
ReplyDeleteTherefore, I don’t think that the library sqlcmd uses to connect to SQL Server is at fault. It appears that sqlcmd is overriding its default connection settings, requesting QUOTED_IDENTIFIER to be OFF by default. I am annoyed that one must use sqlcmd’s -I option to request that a setting take its default value; fewer commandline options should use the ideal settings (QUOTED_IDENTIFIER, UTF-8 codepage (-i 65001)) by default.
My suggestion is to write your scripts as if SET QUOTED_IDENTIFIER is on my default (since it is for everything except for DB-Library clients which SQL Server will drop support for someday) and use sqlcmd’s -I option to make it behave more like ssms.