0

I got a problem with running sql script in docker image. I'm building a dockerfile, which looks like this:

FROM mcr.microsoft.com/mssql/server:2019-latest

USER root
RUN mkdir -p /app/config
WORKDIR /app/config

# Copy all the scripts to create tables, views, etc...
COPY sample_data/ /app/config

EXPOSE 1433

ENV ACCEPT_EULA=Y \
    MSSQL_SA_PASSWORD=SuperDup3Rsecre7 \
    MSSQL_PID=Express

RUN chmod +x /app/config/entrypoint.sh
RUN chmod +x /app/config/create_db.sh

ENTRYPOINT ["./entrypoint.sh"]

Here are my entrypoint.sh and create_db.sh

# entrypoint.sh
#!/bin/bash

# Start the script to create the DB
/app/config/create_db.sh &

# Start SQL Server
/opt/mssql/bin/sqlservr

# create_db.sh
DBSTATUS=1
ERRCODE=1
i=0

while [[ $DBSTATUS -ne 0 ]] && [[ $i -lt 60 ]] && [[ $ERRCODE -ne 0 ]]; do
    i=$i+1
    DBSTATUS=$(/opt/mssql-tools/bin/sqlcmd -h -1 -t 1 -U sa -P $MSSQL_SA_PASSWORD -Q "SET NOCOUNT ON; Select SUM(state) from sys.databases")
    ERRCODE=$?
    sleep 1
done

if [[ $DBSTATUS -ne 0 || $ERRCODE -ne 0 ]]; then 
    echo "SQL Server took more than 60 seconds to start up or one or more databases are not in an ONLINE state"
    exit 1
fi

# Run the setup script to create the DB and the schema in the DB
/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P $MSSQL_SA_PASSWORD -d master -i "ddl_001_tables.sql"
/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P $MSSQL_SA_PASSWORD -d master -i "ddl_002_views.sql"

The first script which creates all the tables works fine, but the second script with views (just a simple views to highlight the problem)

-- ddl_002_views.sql
create view new_view1 as 
select TOP 1 name from sys.tables;

create view new_view2 as
select TOP 1 name from sys.tables;

is failing because of:

Msg 156, Level 15, State 1, Server 6f49cec5c8ab, Procedure new_view1, Line 4
Incorrect syntax near the keyword 'create'.

When I'm executing script ddl_002_views.sql through IDE (in my case DBeaver) it works fine.

Anyone know what am I doing wrong or am I missing something? Thanks in advance.

Improve this question
3
  • 2
    I might be wrong but add a GO; between two create commands. Commented Feb 16, 2024 at 10:17
  • 2
    Create view requires it to be the only code in the batch. As above speaker wrote, you might get lucky if you add: GO on own line between each view, it should be without ; though. GO is the standard batch separator in various sql server tools Commented Feb 16, 2024 at 10:19
  • 2
    It's not GO; @HamidMayeli . GO is not a T-SQL operator, and those should not be statement terminated. Doing so will, infact, cause an error ("Incorrect syntax near 'GO'."), as it (GO) will no longer be seen as a batch separator by the tool you are using (such as SSMS, sqlcmd, and ADS). Commented Feb 16, 2024 at 10:32

1 Answer 1

Reset to default
2

It's not related to docker, you cannot tailgate the create statements. You need to add a GO between the them.

create view new_view1 as 
select TOP 1 name from sys.tables;

GO

create view new_view2 as
select TOP 1 name from sys.tables;
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Seems like GO; helped partially becuase after adding GO; I'm getting: 'CREATE VIEW' must be the first statement in a query batch.
@otemek, can't you separated them into two files?
well, I have ~200 views in one file... that's why I'm struggling with this. If there is no way to keep them in a one file - then I will probably have to split them :)
Give a GO a try without ;.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.