Batch File For Executing Multiple SP's

Question

Post reply

Batch File For Executing Multiple SP's

Hakuna Matata

SSCertifiable

Points: 5577
More actions
May 5, 2011 at 8:55 am

#389450

Hi,
The scenario is that I have got around 30 SP's to be executed & my manager wants me to create a single batch file for all the SP's. Please suggest how can I do this. The database is currently running on SQL Server 2008 R2 Enterprise Edition.
Regards,
Nithin

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

MyDoggieJessie SSC-Forever Points: 44519 More actions · Answer 1

I use this all the time with great success:

Script #1 (saved as setup.bat)

set ServerName=YourServer

set DBName=YourDB

set sql_login=YouUser

set sql_passwd=YourPwd

Script #2 (saved as DeployScripts.bat)

@Echo Off

goto skipcomments

rem

:skipcomments

call setup.bat

if not "%UserName%"=="" goto ok

goto end

:ok

if exist *.outdel *.out

echo My Custom batch script deployer

echo working... DO NOT CLOSE

REM ************************************************

REM Description of scripts you're executing

REM ************************************************

osql -S %ServerName% -d %DBName% -U %sql_login% -P %sql_passwd% -n -i mysqlscript.sql -o mysqlscript.out

echo All Scripts have been started

:end

pause

All you need to do is:

1. Rename the setup.txt and DeployScripts.txt with a .bat extension

2. Edit the "Your%" in the set script.

3. Add as many .sql files as you want to the "osql..." section in the DeployScripts.bat" file

4. Add the *.sql files to the same directory as the *.bat files and double-5. Double-click the DeployScripts.bat file and you should be good to go!

The results of the executed statements/stored-procedures will be written to the *.out files

Example is attached

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Hakuna Matata SSCertifiable Points: 5577 More actions · Answer 2

Thanks a lot!!!!!!!! for the invaluable suggestion:-):-):-):-):-)

Syed Jahanzaib Bin hassan SSChampion Points: 11410 More actions · Answer 3

you have to use SQLCMD for multiple scripts

SQLCMD syntax

sqlcmd -s ipaddress or DNSname -U username -P password -i "f:\script\executor.sql" -o "f:\script\output.txt"

open notepad and write this command as your requirement and then save as a .bat file

Executer.sql file syntax is

:r "f:\script\print.sql"

Print 'Script Executed Successfully'

Print.sql file syntax is

go

print 'Jahanzaib'

go

For your requirement

:r "f:\script\sp1.sql"

:r "f:\script\sp2.sql"

:r "f:\script\sp3.sql"

:r "f:\script\sp4.sql"

...................

My Blog
www.aureus-salah.com

Orlando Colamatteo SSC Guru Points: 182293 More actions · Answer 4

kr.nithin (5/5/2011)
Hi,
The scenario is that I have got around 30 SP's to be executed & my manager wants me to create a single batch file for all the SP's. Please suggest how can I do this. The database is currently running on SQL Server 2008 R2 Enterprise Edition.
Regards,
Nithin

Why not create a stored procedure that calls the other 30 procedures in sequence and then call the one procedure through a scheduled SQL Server Agent Job? Then you don't need to mess with a batch file outside the database domain or any of the command line tools.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Hakuna Matata SSCertifiable Points: 5577 More actions · Answer 5

Thanks a lot for the time taken to answer my query:-):-):-):-)