none
How to create Batch file to execute sql scripts

    Question

  • 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
    • Edited by Ravi exact Wednesday, October 13, 2010 2:08 PM
    Thursday, October 07, 2010 11:35 AM

Answers

  • 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
    • Marked as answer by Ravi exact Thursday, December 16, 2010 1:54 PM
    Thursday, December 16, 2010 1:54 PM

All replies

  • http://dimantdatabasesolutions.blogspot.com/2009/01/executing-multiple-script-file-via.html
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, October 07, 2010 11:42 AM
  • 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.
    • Proposed as answer by ImprintPlus Thursday, May 09, 2013 4:42 PM
    Friday, October 08, 2010 1:41 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
    Wednesday, October 13, 2010 2:01 PM
  • 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:19 AM
  • Any progress Ravi?
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, October 19, 2010 11:55 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:43 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
    Wednesday, October 20, 2010 10:50 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
    Friday, October 22, 2010 5:44 AM
  • Hi Ravi,

    You can try this utility to generate your batch files.

    http://www.codeproject.com/KB/database/osqlScript.aspx

     

    Thanks,

    Bala

    http://sequelqb.blogspot.com/


    Thanks, G Balamurugan
    Saturday, October 23, 2010 4:10 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

    • Marked as answer by Ravi exact Thursday, December 16, 2010 1:50 PM
    • Unmarked as answer by Ravi exact Thursday, December 16, 2010 1:51 PM
    Friday, November 19, 2010 12:48 PM
  • 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
    • Marked as answer by Ravi exact Thursday, December 16, 2010 1:54 PM
    Thursday, December 16, 2010 1:54 PM
  • 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

    Friday, May 11, 2012 10:47 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

      
    Monday, July 22, 2013 9:16 AM
  • 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

    Wednesday, March 26, 2014 3:00 PM
  • 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.

    Tuesday, June 24, 2014 10:37 AM