How to create Batch file to execute sql scripts
Question
Thursday, October 7, 2010 11:35 AM
Hi friends,
i want to create batch file to execute all my .sql scripts.
I have all table ( all table scripts in single file ) ,Udds ( all udds in single file ) ,Stored procedures( separate file for each SPs ),Functions ( Separate file for each Functions ),Triggers and views scripts in .SQL file.
can anybody tell me how to create batch file for executing all these scripts in sql server ?.
while executing, it should ask Database name,server name, password. if these details are given then it should execute my all scripts in given database
, if any error thrown then that error and procedure name alone have to move to separate log file..
Please help me if this possible or any other easy way to do this..
Thanks - Ravi
All replies (15)
Thursday, December 16, 2010 1:54 PM âś…Answered | 2 votes
Hi friends,
I got this output by these codes and running good,
@echo off
cls
set /p SName=Server Name :
set /p UName=User Name :
set /p Pwd=Password :
set /p DbName=Database Name :
set /p choice=ARE YOU SURE TO EXECUTE SCRIPTS in %DbName% (y/n) ?
if '%choice%'=='y' goto begin
goto end
:begin
if exist _Deploy.txt del _Deploy.txt
@echo on
sqlcmd -S %SName% -U %UName% -P %Pwd% -d %DbName% -I -i "UDDs"\UDDs.sql >> _Deploy.txt 2>&1
@notepad _Deploy.txt
:end
Thank you very much friends
Thanks - Ravi
Thursday, October 7, 2010 11:42 AM
http://dimantdatabasesolutions.blogspot.com/2009/01/executing-multiple-script-file-via.htmlBest Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
Friday, October 8, 2010 1:41 PM
Hi,
You can use 'copy nul' oe echo command to create a batch file using xp_cmdshell(if you want to create a batch file dynamically).
@echo off
set /p sname= Please enter the servername:
set /p dbname= Please enter the databasename:
set /p pwd= Please enter the password:
sqlcmd -E -S %sname% -D %dbname% -P %PWD% -i "C:\location\your query.sql" -b)
HTH.
-Naren
-Naren.
Wednesday, October 13, 2010 2:01 PM
Dear naren,
Thank u but it is not working.. nothing happening.. I gave my update query file path in that input file location but it is not executing..
so can u please tell me clearly ?
Thanks - Ravi
Tuesday, October 19, 2010 11:19 AM
Hi Ravi,
The same i'm able to use using windows authentication . Can you please tell me what exactly you are doing. Are going to use windows auth or sql auth. If it's sql auth the instead of -E use -U and give the username.
HTH.
-Naren.
-Naren.
Tuesday, October 19, 2010 11:55 AM
Any progress Ravi?Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
Wednesday, October 20, 2010 10:43 AM
Naren,
I gave like this,
@echo off
set /p sname= Please enter the servername:
set /p dbname= Please enter the databasename:
set /p Username= Please enter the User Name:
set /p pwd= Please enter the password:
sqlcmd -U %Username% -S %sname% -d %dbname% -P %PWD% -i "C:\A.sql" -o "C:\err.txt"
but it is not executing this A.sql which contains one udpate query. can u please tell me what is the mistake here ?
Thanks - Ravi
Wednesday, October 20, 2010 10:50 AM
Uri,
I would like to create .bat file.. I've planned to give this .bat file to implementation team. Implementation team just they'll execute .bat file then all scripts should update in client DB..
Is there any way to do this ? please help me if u have any idea..
Thanks - Ravi
Friday, October 22, 2010 5:44 AM
Naren,
I gave like this,
@echo off
set /p sname= Please enter the servername:
set /p dbname= Please enter the databasename:
set /p Username= Please enter the User Name:
set /p pwd= Please enter the password:sqlcmd -U %Username% -S %sname% -d %dbname% -P %PWD% -i "C:\A.sql" -o "C:\err.txt"
but it is not executing this A.sql which contains one udpate query. can u please tell me what is the mistake here ?
Thanks - Ravi
This should work.
See what message do you get in err.txt
You can also test first the command line by entering the parameters like:
sqlcmd -U MaryS -S MYSERVER .....
Kalman Toth SQL SERVER 2012 & BI TRAINING
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012
Saturday, October 23, 2010 4:10 PM
Hi Ravi,
You can try this utility to generate your batch files.
http://www.codeproject.com/KB/database/osqlScript.aspx
Thanks,
Bala
Thanks, G Balamurugan
Friday, November 19, 2010 12:48 PM
Hi Mr.Ravi
Very Good Evening
set /p srv= Enter SQL Server Name:
set /p usr= Enter User Name :
set /p pwd= Enter Password :
set /p dbs= Enter Database Name :
echo. > "ResultLog.log"
echo Result of Script 1.Update_EffectiveDate_Bill_PayContractProductRates>> "ResultLog.log"
echo >> "ResultLog.log"
sqlcmd -U %usr% -P %pwd% -S %srv% -d %dbs% -i "D:\janaki\WithdrawNominationDuplication.sql" >> "ResultLog.log"
sqlcmd -U %usr% -P %pwd% -S %srv% -d %dbs% -i "D:\janaki\DB_Scripts.sql" >> "ResultLog.log"
this script will work for batch file creation definitely
since i have tried first time and i got executed fine
take care
Regards
Bhuvana
Friday, May 11, 2012 10:47 AM
Hi Ravi/naren,
Can you please tell me what is UDD here. I am able to implement a script written below but now the issue is i want to meke it generic so as all the scripts in one single folder executes and all the messages with the script name should go to the err.txt. Please let me know what changes i need to make.
@echo off
cls
set /p SName=Please enter the servername:
set /p DbName= Please enter the databasename:
set /p choice=ARE YOU SURE TO EXECUTE SCRIPTS in %DbName% (y/n) ?
if '%choice%'=='y' goto begin
goto end
:begin
if exist C:\Scripts\err.txt del C:\Scripts\err.txt
@echo on
sqlcmd -S %SName% -d %DbName% -i "C:\Scripts\Query1.sql" -o "C:\Scripts\err.txt"
:end
Monday, July 22, 2013 9:16 AM
@ECHO OFF
Cls
IF "%1" == "" GOTO ERROR
IF "%2" == "" GOTO ERROR
IF "%5" == "" GOTO 4PARA
@echo on
:4PARA
:: predefined variables used throughout deployment
@set DBServer=Server Name
@set LogFile=log.log
echo Process Started %DATE% , %TIME%
echo Process Started %DATE% , %TIME% >> %LogFile%
echo #### Processing for 10000_xyz.sql Started @ %DATE% %TIME% >> %LogFile%
SQLCMD -U SA -P 99 -S %DBServer% -d OGP -i 10000_xyz.sql -f 65001 >> %LogFile%
if %ERRORLEVEL% NEQ 0 GOTO ERROR1
echo Processing Ended @ %DATE% %TIME% ### >> %LogFile%
echo >> %LogFile%
echo Process ended %DATE% , %TIME%
echo Process ended %DATE% , %TIME% >> %LogFile%
GOTO Return
:ERROR1
ECHO Error Raised
GOTO Return >> %LogFile%
:ERROR
ECHO.
ECHO.
ECHO To Run the Batch File, You need to Pass 4 Parameters
ECHO.
ECHO 1) User Name ( %1 )
ECHO 2) Password ( %2 )
ECHO 3) Server Name ( %3 )
ECHO 4) Database Name ( %4 )
GOTO Return
:RETURN
Copy above code and paste in txt file then rename it by log.bat
If any exception come it will log in lol.log file at same folder location. Please keep this we should have all *.sql files at same location.
SA -User Name
99 - Password
Wednesday, March 26, 2014 3:00 PM
Above code is working fine. but The problem I am facing is if credential is wrong the then its closing all even not creating error.txt.
Second problem is if my SQL server has windows authentication then what we have to pass on user id and pwd?
Thanks,
Regards Vikas Pathak
Tuesday, June 24, 2014 10:37 AM
Hi Mate.
can i save the below details in my batch file parmantly, so that i don't need to provide the details agains again in CMD while i execute this. Please help
set /p SName=Server Name :
set /p UName=User Name :
set /p Pwd=Password :
set /p DbName=Database Name
If i am providing the details before execution of this bat file it will throw error.