multiple sqlcmd commands in a batch file

Question

I have multiple sqlcmd commands to run at a time from a batch file. Each statement when executed separately works perfectly. But if I give like a 5 sqlsmd commands in a bat file it only executes the first one and then I see 1> in my command line screen and nothing happens. How to resolve this?

Note: CALL statement before each line didn't work That is the only solution i found in my reseach

example of what i'm doing::

sqlcmd -S servername-q "EXEC msdb.dbo.prc_Backup @BackupType = 'L'       ,@DBName = 'db' ,@BackupDir = 'J:\MSSQL10.MSSQLSERVER\MSSQL\Backup\db2dba' ,@RetainHours = 47       , @create_sub_dir = 0      " >> C:\tlogresult.txt
sqlcmd -S servername -q "EXEC msdb.dbo.prc_Backup @BackupType = 'L'      ,@DBName = 'DB2RuntimeMigration' ,@BackupDir = 'J:\MSSQL10.MSSQLSERVER\MSSQL\Backup\DB2RuntimeMigration' ,@RetainHours = 47         , @create_sub_dir = 0      " >> C:\tlogresult.txt

what is the output when you run it from a command line so you can see the output? is there a pause, waiting for a response from you? — Bruce Dunwiddie
– Bruce Dunwiddie, Commented Mar 24, 2016 at 18:14
@BruceDunwiddie In the output file, i only see the result of first command execution. In command window i dont see any input that's directed to me. just a line blinks after the first command.. then starts waiting for something.. for what i'm not able to figure it out. — RMu
– RMu, Commented Mar 24, 2016 at 18:18
If you can add the equivalent of the Backup STATS output then you should see the progress indicated or errors in the tlogresults.txt file — Sql Surfer
– Sql Surfer, Commented Mar 24, 2016 at 18:21
is there a reason you are running them in diff 5 sqlcmd ? .. you can run in the one sqlcmd with all backup sql statements.. — Hiten004
– Hiten004, Commented Mar 24, 2016 at 18:22
@SqlSurfer the first tlog is succeeding without any issues. The second line is not being picked up at all — RMu
– RMu, Commented Mar 24, 2016 at 18:22

Paul Soscia · Accepted Answer · 2017-07-08 11:36:08Z

3

Try capital Q

don't use [-q "cmdline query"]

but instead use [-Q "cmdline query" and exit]

answered Jul 8, 2017 at 11:36

Paul Soscia

312 bronze badges

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

Comments

Bruce Dunwiddie · Accepted Answer · 2016-03-24 21:48:59Z

https://msdn.microsoft.com/en-us/library/ms180944.aspx

When the command is executed without input files or queries, sqlcmd connects to the specified instance of SQL Server and then displays a new line with a 1> followed by a blinking underscore that is named the sqlcmd prompt. The 1 signifies that this is the first line of a Transact-SQL statement, and the sqlcmd prompt is the point at which the Transact-SQL statement will start when you type it in.

At the sqlcmd prompt, you can type both Transact-SQL statements and sqlcmd commands, such as GO and EXIT. Each Transact-SQL statement is put in a buffer called the statement cache. These statements are sent to SQL Server after you type the GO command and press ENTER. To exit sqlcmd, type EXIT or QUIT at the start of a new line.

...

Use the -o option to output to a file instead of piping the output to a file, and that should prevent you from entering the interactive mode.

or try

sqlcmd -S servername-q "EXEC msdb.dbo.prc_Backup @BackupType = 'L'       ,@DBName = 'db' ,@BackupDir = 'J:\MSSQL10.MSSQLSERVER\MSSQL\Backup\db2dba' ,@RetainHours = 47       , @create_sub_dir = 0      " >> C:\tlogresult.txt
exit
sqlcmd -S servername -q "EXEC msdb.dbo.prc_Backup @BackupType = 'L'      ,@DBName = 'DB2RuntimeMigration' ,@BackupDir = 'J:\MSSQL10.MSSQLSERVER\MSSQL\Backup\DB2RuntimeMigration' ,@RetainHours = 47         , @create_sub_dir = 0      " >> C:\tlogresult.txt"
exit

I want to append the output file as we need to verify logs for like 20,000 tlog backups. Any work around to append output file?
The documentation page above states that you can use the EXIT or QUIT statements to exit the interactive mode. Try putting one of those between your sqlcmd calls if the -o isn't working to append.
That link is for interactive sqlcmd without -q parameter.I just don't know how to fix it. If you could provide me with some sample script that would be great.
edited the answer to include what I'm suggesting that you try.

RMu · Accepted Answer · 2016-03-30 18:52:21Z

0

I'm not able to run it via bat file. What i am able to do is to run the commands with QUIT at the end of each command from the command line.

answered Mar 30, 2016 at 18:52

RMu

8974 gold badges18 silver badges48 bronze badges

Collectives™ on Stack Overflow

multiple sqlcmd commands in a batch file

3 Answers 3

Comments

4 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

Comments

Related