error in creating view in docker mssql database

Question

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.

Answer 1

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;