When working with batch scripts to execute multiple SQL scripts, it’s often helpful to log not only the results but also the filenames of the executed scripts. This can make it easier to track which script produced which output. In this blog post, we’ll walk through how to modify a batch script to include the SQL script filenames in the generated CSV file.

Original Batch Script

Let’s start with the original batch script that executes SQL scripts and logs the results to a CSV file:

@ECHO OFF

REM Folder where the queries are located
set "specific_folder=C:\Path\To\Your\SQLScripts\"

cls

set STARTTIME=%TIME%

for /r "%specific_folder%" %%F in (*.sql) do (
    ECHO %%F Started
    sqlcmd /S myserver /d mydatabase -E -i"%%F" >> "_SQL_OutputLog_%DATE:~10,4%%DATE:~4,2%%DATE:~7,2%.csv" -W -s, -h -1
    ECHO %%F Completed
)

set ENDTIME=%TIME%

for /F "tokens=1-4 delims=:.," %%a in ("%STARTTIME%") do (
    set /A "start=(((%%a*60)+1%%b %% 100)*60+1%%c %% 100)*100+1%%d %% 100"
)

for /F "tokens=1-4 delims=:.," %%a in ("%ENDTIME%") do (
    IF %ENDTIME% GTR %STARTTIME% set /A "end=(((%%a*60)+1%%b %% 100)*60+1%%c %% 100)*100+1%%d %% 100"
    IF %ENDTIME% LSS %STARTTIME% set /A "end=((((%%a+24)*60)+1%%b %% 100)*60+1%%c %% 100)*100+1%%d %% 100"
)

exit

Modifying the Script to Include Filenames

To include the SQL script filenames in the CSV output, we need to modify the script to append the filename to the CSV file along with the results of the SQL execution. Here is the modified script:

@ECHO OFF

REM Folder where the queries are located
set "specific_folder=C:\Path\To\Your\SQLScripts\"

cls

set STARTTIME=%TIME%

(
    FOR /R "%specific_folder%" %%F IN (*.sql) DO (
        ECHO %%F Started
        ECHO "%%F" >> "_SQL_OutputLog_%DATE:~10,4%%DATE:~4,2%%DATE:~7,2%.csv"
        sqlcmd /S myserver /d mydatabase -E -i"%%F" >> "_SQL_OutputLog_%DATE:~10,4%%DATE:~4,2%%DATE:~7,2%.csv" -W -s, -h -1
        ECHO %%F Completed
    )
)

set ENDTIME=%TIME%

for /F "tokens=1-4 delims=:.," %%a in ("%STARTTIME%") do (
    set /A "start=(((%%a*60)+1%%b %% 100)*60+1%%c %% 100)*100+1%%d %% 100"
)

for /F "tokens=1-4 delims=:.," %%a in ("%ENDTIME%") do (
    IF %ENDTIME% GTR %STARTTIME% set /A "end=(((%%a*60)+1%%b %% 100)*60+1%%c %% 100)*100+1%%d %% 100"
    IF %ENDTIME% LSS %STARTTIME% set /A "end=((((%%a+24)*60)+1%%b %% 100)*60+1%%c %% 100)*100+1%%d %% 100"
)

exit

Explanation

1. Adding Filename to CSV:
   • ECHO "%%F" >> "_SQL_OutputLog_%DATE:~10,4%%DATE:~4,2%%DATE:~7,2%.csv": This line appends the filename of the SQL script to the CSV file before executing the script.
2. Executing SQL Script:
   • sqlcmd /S myserver /d mydatabase -E -i"%%F" >> "_SQL_OutputLog_%DATE:~10,4%%DATE:~4,2%%DATE:~7,2%.csv" -W -s, -h -1: This line executes the SQL script and appends the result to the same CSV file.

With these changes, the CSV file will now contain both the filename and the result of each SQL script, making it easier to identify which result corresponds to which script.

Conclusion

By making a few modifications to the batch script, we can enhance our logging mechanism to include the filenames of the SQL scripts being executed. This improvement aids in better tracking and debugging of SQL script executions.