SQLCMD command, How to save output into log file

11,118

Solution 1

If you need the output into one common file then you should use the @Abhishek 's answer.

If you need the output into a separate log file for an each input sql file then you can use -o parameter of sqlcmd command. Your bat file could look like this:

for %%G in (*.sql) do sqlcmd /S <servername> /d <dbname> -E -i"%%G" -o C:\logs\%%G.log
pause

In this case for

1.sql

2.sql

you will get:

1.sql.log

2.sql.log

Solution 2

You are seeking Command Redirection.

As per your example -

for %f in (*.sql) do sqlcmd /S <servername> /d <dbname> /E /i "%f" >> sql.log 2>&1

once the execution of the sql script is done the output will be redirected to and appends the command output to the end of file (here sql.log) without deleting the information that is already in the file (>>) and redirects STDERR (2) into STDOUT handle(1) - 2>&1

More information here and here.

Share:
11,118

Related videos on Youtube

dolly_do
Author by

dolly_do

Updated on June 04, 2022

Comments

  • dolly_do
    dolly_do almost 2 years

    The following question has helped me solving the problem of executing multiple SQL Scripts located in file. Run all SQL files in a directory However, I did not get how to redirect the output into a separate log file. Someone suggested the following script but since I don't understand it, it did not work and I can't find out the error.

    for %f in (*.sql) do sqlcmd /S <servername> /d <dbname> /E /i "%f" >> sql.log 2>&1) 
    
  • dolly_do
    dolly_do about 6 years
    I must be missing something, since it does not work for me. I did exactly the stated but no logs are saved in my directory
  • Aacini
    Aacini over 4 years
    You may get the same result via: for %%G in (*.sql) do (sqlcmd /S <servername> /d <dbname> -E -i "%%G" > C:\logs\%%~nG.log) In this case, for file1.sql and file2.sql you will get file1.log and file2.log