sqlcmd does not close the connection in bash script while running multiple sqlcmd

Question

While running following query, I was able to get first query result but it does not continue to 2nd query. It seems that the connection doesn't stop once it's completed.

I was able to run similar query with the query in a .sql file and run them 1 by 1. I encounter an issue running all 7 in 1 bash script.

QUERY1 = SELECT * FROM SOMETABLE
QUERY2 = SELECT * FROM SOMETABLE
QUERY3 = SELECT * FROM SOMETABLE
QUERY4 = SELECT * FROM SOMETABLE
QUERY5 = SELECT * FROM SOMETABLE
QUERY6 = SELECT * FROM SOMETABLE
QUERY7 = SELECT * FROM SOMETABLE

sqlcmd -S $HOST -U $USER -P $PASSWORD -d $DATABASENAME -s $QUERY1 > 
QueryRun1.csv
sqlcmd -S $HOST -U $USER -P $PASSWORD -d $DATABASENAME -s $QUERY2 > QueryRun2.csv
sqlcmd -S $HOST -U $USER -P $PASSWORD -d $DATABASENAME -s $QUERY3 > QueryRun3.csv
sqlcmd -S $HOST -U $USER -P $PASSWORD -d $DATABASENAME -s $QUERY4 > QueryRun4.csv
sqlcmd -S $HOST -U $USER -P $PASSWORD -d $DATABASENAME -s $QUERY5 > QueryRun5.csv
sqlcmd -S $HOST -U $USER -P $PASSWORD -d $DATABASENAME -s $QUERY6 > QueryRun6.csv
sqlcmd -S $HOST -U $USER -P $PASSWORD -d $DATABASENAME -s $QUERY7 > QueryRun7.csv

This bash script does not fetch the 2nd query result and so.

Answer 1

I can't remember why, I wrote this a little bit ago, but I remember having problems with > with sqlcmd. I had better luck with the syntax below:

output=$(/opt/mssql-tools/bin/sqlcmd -U $Login -P $Password -i "$file")
echo "$output" > $log

In your case, that would mean something like this:

QUERY1="SELECT * FROM SOMETABLE"
QUERY2="SELECT * FROM SOMETABLE"
QUERY3="SELECT * FROM SOMETABLE"
QUERY4="SELECT * FROM SOMETABLE"
QUERY5="SELECT * FROM SOMETABLE"
QUERY6="SELECT * FROM SOMETABLE"
QUERY7="SELECT * FROM SOMETABLE"

output=$(sqlcmd -S $HOST -U $USER -P $PASSWORD -d $DATABASENAME -Q "$QUERY1")
echo "$output" > QueryRun1.csv
output=$(sqlcmd -S $HOST -U $USER -P $PASSWORD -d $DATABASENAME -Q "$QUERY2")
echo "$output" > QueryRun2.csv
output=$(sqlcmd -S $HOST -U $USER -P $PASSWORD -d $DATABASENAME -Q "$QUERY3")
echo "$output" > QueryRun3.csv
output=$(sqlcmd -S $HOST -U $USER -P $PASSWORD -d $DATABASENAME -Q "$QUERY4")
echo "$output" > QueryRun4.csv
output=$(sqlcmd -S $HOST -U $USER -P $PASSWORD -d $DATABASENAME -Q "$QUERY5")
echo "$output" > QueryRun5.csv
output=$(sqlcmd -S $HOST -U $USER -P $PASSWORD -d $DATABASENAME -Q "$QUERY6")
echo "$output" > QueryRun6.csv
output=$(sqlcmd -S $HOST -U $USER -P $PASSWORD -d $DATABASENAME -Q "$QUERY7")
echo "$output" > QueryRun7.csv