SQL Server – SQLCMD – Create Table Error – QUOTED_IDENTIFIER Error (Msg 1934)

Background

Error when the command file pasted below is ran.

The error reads “failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER‘ 


Code


Rem This command file re-creates Database Schema objects
Rem  This scripts were created by ScriptDB.exe (Elsasoft.com)

setlocal

rem -E Integrated Security

SET SCMD=sqlcmd -SDBServer -dsales -E -I

for %%d in (Schemas\*.sql) do %SCMD% -i%%d
for %%d in (Tables\*.sql) do %SCMD% -i%%d
for %%d in (Views\*.sql) do %SCMD% -i%%d
for %%d in (Programmability\StoredProcedures\*.sql) do %CMD% -i%%d
for %%d in (Programmability\Functions\*.sql) do %SCMD% -i%%d

endlocal

Error Message


Msg 1934, Level 16, State 1, Server DBServer, Line 2
CREATE TABLE 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.

 

TroubleShooting

The problem is caused by the fact the sqlcmd executable posts this command “set quoted_identifier off SET TEXTSIZE 4096” upon launching.

BTW, “SQL Server Profiler” was used to confirm this setting.

 

Remediation

 

Outline

  1. Add -I (enable Quoted Identifiers) as one of the arguments to sqlcmd.

Code


setlocal

rem -I Enable Quoted Identifier
rem -E Integrated Security

SET SCMD=sqlcmd -SDBServer -dsales -E <strong>-I</strong>

for %%d in (Schemas\*.sql) do %SCMD% -i%%d
for %%d in (Tables\*.sql) do %SCMD% -i%%d
for %%d in (Views\*.sql) do %SCMD% -i%%d
for %%d in (Programmability\StoredProcedures\*.sql) do %CMD% -i%%d
for %%d in (Programmability\Functions\*.sql) do %SCMD% -i%%d

endlocal

 

Suggested Readings:

  1. How to execute multiple scripts on multiple sql servers
    Link
  2. Sqlcmd Utility
    Link

One thought on “SQL Server – SQLCMD – Create Table Error – QUOTED_IDENTIFIER Error (Msg 1934)

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s