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.

The -s argument is the column separator. Perhaps you meant to specify -Q (for query), which should be enclosed in quotes. Also specify -I to ensure quoted identifiers are used. — Dan Guzman
– Dan Guzman, Commented Jun 3, 2019 at 10:14

Thom A · Accepted Answer · 2019-06-03 10:22:03Z

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

answered Jun 3, 2019 at 10:22

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Wei Jun Over a year ago

This does help but probably I need to look into refining my query.It give me some error like "Incorrect syntax near 'SELECT * FROM SOMETABLE'. I was able to run this query on SSMS but not the SQLCMD hmmm.

Thom A Over a year ago

Then the problem is your query @WeiJun. You've overly simplified the question, and suggested the problem is something else.T he problem is your statements, not bash or sqlcmd, it seems.

Wei Jun Over a year ago

okay I managed to get the query. This approach works. Thank you very much.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related