Hello

I am trying to use sqlcmd to run a set of sql statements. If I use -i switch and input a file with sql statements, it is working fine, but I am trying to use the sql statements using -q to avoid the creation of the input file. But I am not having luck with -q, can someone let me know if putting multiple lines of code is possible in -q switch like below?

A simple restore command like below. If I use the whole restore command in single line it works fine like below:

sqlcmd -E -S servername -d master -Q "restore database bestst_test from disk='E:\Backup\test\bestst\bestst_20101222.bak' with move 'BESMgmt415_data' to 'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\bestst_test.mdf',move 'BESMgmt415_log' to 'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\bestst_test_log.ldf' "

but if I split the restore command into 3 lines like below, it fails, can someone let me know how to use the multiple line feature in sqlcmd -q switch?

sqlcmd -E -S servername -d master -Q "restore database bestst_test from disk='E:\Backup\test\bestst\bestst_20101222.bak'
with move 'BESMgmt415_data' to 'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\bestst_test.mdf',
move 'BESMgmt415_log' to 'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\bestst_test_log.ldf' "

I figured it out myself with the help of a friend.

Spanning the same command across multiple lines is not possible, my main intention in asking this question was to see if 2 statements can be executed using just one -q switch like 'restore db1,restore db2' in one -q swith, turns out we can do it by separating them with semi-colon, but everything has got to be in one line like below:

sqlcmd -E -S servername -d master -Q " restore db1;restoredb2;"

Satya

 

 

• Marked as answer by Satya Krishna Wednesday, December 29, 2010 10:07 PM

Well actually there is a way, in command prompt you can you the carat character (^) to indicate that there is more lines. So in your restore case:

sqlcmd -E -S servername -d master -Q ^
"restore database bestst_test from disk='E:\Backup\test\bestst\bestst_20101222.bak' ^
with move 'BESMgmt415_data' to 'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\bestst_test.mdf', ^
move 'BESMgmt415_log' to 'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\bestst_test_log.ldf' "

I must say, it is abit of a hassle (since there are several restrictions to how you need to break the line), and you actually better off using the input file for this.

Thank you

Lucas

• Marked as answer by Satya Krishna Thursday, December 30, 2010 4:38 PM

Thanks Lucas, it worked, that is what I was looking for

I was calling SQLCMD with multi-line SQL statements from SQL Server using xp_cmdshell.  This worked, but I had to remove the char(10) from the line breaks and use char(13) alone to separate lines.