An Idea can change your life.....

Sunday, November 30, 2008

sql server execute all files in a folder using sqlcmd

Step 1. Create a sql file (proc.sql) and palace it here C:\SqlFiles\01_database\proc.sql

create procedure procName
as
begin
select * from DimProduct
end

Step2. Create a sql file(proc1.sql) and place it here C :\SqlFiles\proc1.sql

create procedure procName1
as
begin
select * from DimProduct
end

step3.Create a BatchCommand file(ExecuteSqlFiles.bat) with following text in it and place it here C:\SqlFiles\ExecuteSqlFiles.bat

for /R %%X in (*.SQL) do SQLCMD -S Server -d Database -U UserID -P Password -I -i "%%X" >> ResultScript.txt

Step4. Run the ExecuteSqlFiles.bat (i.e by clicking on it)

A file (C:\SqlFiles\ResultScript.txt) if not exist is created and the errors encounterd while executing the sql files is written to the file.

2 comments:

Kiran banda said...

This approach is very helpful, esp. when you have got a bunch of sql scripts (say stored procs) that need to be run on ur dev/staging environments.

But, there is one issue that needs to be considered - if proc.sql is invoking proc1.sql, then you might run into an error,since proc1.sql gets executed after proc.sql !!(not sure if you get an error or a warning. Better if you can read the names of the files from a file that can be input to your batch file :-)

mvkinfo said...

Kiran Thanks for your valuable feedback.
I have checked the scenario mentioned above it will throw a warning which can be ignored.