CODEWITHSUNDEEP
sql-serverdockerdockerfilesql-server-linux
Nick
Nick

Reputation: 1008

SQL Server in Docker CREATE INDEX failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’

I have a SQL Server Dockerfile with my import-data.sh importing *.sql files from a sql-data folder. Everything works if I run the *.sql files from a tool like Datagrip but the import fails with this error message when it's ran automatically.

Error message:

Msg 1934, Level 16, State 1, Line 4
CREATE INDEX 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.

Dockerfile

FROM microsoft/mssql-server-linux:2017-latest

RUN mkdir /sql-data/
EXPOSE 1433

COPY entrypoint.sh /usr/local/bin/
RUN chmod +x /usr/local/bin/entrypoint.sh

COPY import-data.sh /usr/src/app/
RUN chmod +x /usr/src/app/import-data.sh

# Copy SQL Scripts to sql-data for processing
COPY ./sql-data/*.sql /sql-data/

CMD /bin/bash /usr/local/bin/entrypoint.sh

entrypoint.sh

#!/bin/bash

#start SQL Server, start the script to create the DB and import the data, start the app
/usr/src/app/import-data.sh & /opt/mssql/bin/sqlservr

import-data.sh

#!/bin/bash
# wait for the SQL Server to come up https://github.com/twright-msft/mssql-node-docker-demo-app/issues/11
while [ ! -f /var/opt/mssql/log/errorlog ]
do
  sleep 2
done

## tail the error log for the startup dll and then quit
tail -f /var/opt/mssql/log/errorlog | while read LOGLINE
do
   [[ "${LOGLINE}" == *"Using 'xpstar.dll' version"* ]] && pkill -P $$ tail
done

echo "Running SQL Scripts"
# Scan for SQL files and load them in
for file in /sql-data/*.sql; do
    echo $file
    /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P $SA_PASSWORD -i $file 
done

/sql-data/setup.sql

IF NOT EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = 'Products')
BEGIN
    CREATE DATABASE Products;
END
GO

USE Products;
GO

Upvotes: 7

Views: 2751

Answers (2)

Dan Guzman
Dan Guzman

Reputation: 46261

The SQLCMD utility unfortunately defaults to QUOTED_IDENTIFIER OFF for backwards compatibility reasons. Specify the -I argument so that QUOTED_IDENTIFIER ON is used. 

/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P $SA_PASSWORD -i $file -I

Upvotes: 16

Nick
Nick

Reputation: 1008

Tools like SQL Server Management Studio and Datagrip have Quoted Identifier turned on by default. You must manually enable it in SQLCMD by modifying your SQL Scripts to SET QUOTED_IDENTIFIER ON

You would modify your setup.sql script like this:

/sql-data/setup.sql

SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = 'Products')
BEGIN
    CREATE DATABASE Products;
END
GO

USE Products;
GO

Upvotes: 2

Related Questions