Hi,

I was wondering if someone can help me create a loop in a batch file? What I'm doing is creating a batch file that will build a SQl 2005 DB from scripts. I've got the SQL scripts in a folder on the C drive.

I've created a batch file that will look in the directory with the scripts and put the list of names of the SQL files in a cmd file called myfile.cmd. i.e.

ADR.sql
ADTC.sql
BedMap.sql
Community.sql

The script then uses that myfile.cmd to make a new cmd file with the SQLCMD appended to the SQL script name to execute and create the DB. So what you GET is if you have 30 SQL scripts in the folder on C drive the cmd file will have 30 lines in it to build the DB. i.e.

SQLCMD -d DBName -i C:\Vault\Dev\DW\Database\Schema\ADR.sql -o C:\Logs\NightlyLog.txt
SQLCMD -d DBName -i C:\Vault\Dev\DW\Database\Schema\ADTC.sql -o C:\Logs\NightlyLog.txt
SQLCMD -d DBName -i C:\Vault\Dev\DW\Database\Schema\BedMap.sql -o C:\Logs\NightlyLog.txt
SQLCMD -d DBName -i C:\Vault\Dev\DW\Database\Schema\Community.sql -o C:\Logs\NightlyLog.txt
...ETC,etc..

This works! except I can't get it to log the output. It will log the output but only if there is just one line in the cmd file.

I was thinking the logging might work if I can loop through the SQL script names one line at a time instead of having them all in one cmd file.

Can anyone help me figure out how to loop through the SQL scripts? Here is a sample of the batch file I've created. That will build the DB but won't log if ther eis an error.

@echo off
REM
REM Create the command file with the names of all the SQl scripts
REM


dir C:\Vault\Dev\DW\Database\Schema /b >C:\Nightlybuild\Loggingtest\myfile.cmd


REM
REM Add the SQLCMD to the beginning of each line
REM



for /F "delims=" %%j in (myfile.cmd) do echo.SQLCMD -d DBName -i C:\Vault\Dev\DW\Database\Schema\%%j -o C:\Logs\NightlyLog.txt >> BuildSchema.cmd


REM
REM Delete the myfile.cmd
REM

DEL C:\Nightlybuild\Loggingtest\myfile.cmd


@exit
Code: [Select]@echo off
echo. > BuildSchema.txt
for /F "delims=" %%j in (myfile2.txt) do (
echo %%j
)
echo.
for /F "delims=" %%j in (myfile2.txt) do (

echo."SQLCMD -d DBName -i C:\Vault\Dev\DW\Database\Schema\%%j -o C:\Logs\NightlyLog.txt" >> BuildSchema.txt
)
type BuildSchema.txt


Out:

C:>loophow.bat
ADR.sql
ADTC.sql
BedMap.sql
Community.sql


"SQLCMD -d DBName -i C:\Vault\Dev\DW\Database\Schema\ADR.sql -o C:\Logs\NightlyLog.txt"
"SQLCMD -d DBName -i C:\Vault\Dev\DW\Database\Schema\ADTC.sql -o C:\Logs\NightlyLog.txt"
"SQLCMD -d DBName -i C:\Vault\Dev\DW\Database\Schema\BedMap.sql -o C:\Logs\NightlyLog.txt"
"SQLCMD -d DBName -i C:\Vault\Dev\DW\Database\Schema\Community.sql -o C:\Logs\NightlyLog.txt"

C:>Code: [Select]@echo off
>BuildSchema.cmd set /p =SQLCMD -d DBName <nul
for /f "delims=" %%j in ('dir /b C:\Vault\Dev\DW\Database\Schema\*.sql') do (
>>BuildSchema.cmd set /p =-i C:\Vault\Dev\DW\Database\Schema\%%j <nul
)
>>BuildSchema.cmd echo -o C:\Logs\NightlyLog.txtThanks CN-DOS and DanCollins for the RESPONSE. CN-DOS I used yours it worked great!
There was one down fall. There are some 500+ tables to create and the script was well over 256 characters to make the tables so it fails. I'll have to look for a way to cut it upI notice in your first post that all the sqlcmd executions output to the same log file. How does that work? Does each new log OVERWRITE or append to the existing log?

This is just a thought, but make each log name unique. You can always concatenate the logs into a single file later.

Try writing the for loop as:

Code: [Select]for /F "delims=" %%j in (myfile.cmd) do (
set/a count+=1
echo.SQLCMD -d DBName -i C:\Vault\Dev\DW\Database\Schema\%%j -o C:\Logs\NightlyLog!count!.txt >> BuildSchema.cmd
)

Be sure to insert setlocal enabledelayedexpansion as the second line in your batch file (directly after @echo off)

Who knows, it might actually work.