none
How to run SQL scripts in bat file?

    Question

  •  

    I have multiple sql scripts files and I want to run them in a bat file for ease of use in Microsoft SQL Server 2005. How do I do that in steps by steps? Thanks.
    Wednesday, November 19, 2008 6:21 AM

Answers

  • In addition, the sql scripts can be parametrized within SQL (using sqlcmd mode and using variables) and call subsequent

    Sql Scripts (using the :r command), which was not possible in tools before.

     

    Jens K. Suessmeyer

     

     

    Wednesday, November 19, 2008 9:23 AM
    Moderator
  • In addition to the above posts, this is the basic syntax for running script files:

    Code Snippet

    sqlcmd  -i c:\data\runscripts\InventoryMonthEnd.sql

    You can edit scripts files with SQLCMD features in Query Editor by switching to the SQLCMD mode.

    Code Snippet

    sqlcmd -?

    at command prompt, will give you the help page.

    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    • Edited by Kalman Toth Sunday, September 30, 2012 8:45 AM
    Thursday, November 20, 2008 1:37 PM

All replies

  •  

    Please check the SQLCMD command-line utility which comes with SQL Server 2005. It should do the job. If the list of sql script files is dynamic then you can use looping in BAT or CMD file and call the utility multiple times.

     

    Thanks,

    Varsham

    Wednesday, November 19, 2008 6:37 AM
  • In addition, the sql scripts can be parametrized within SQL (using sqlcmd mode and using variables) and call subsequent

    Sql Scripts (using the :r command), which was not possible in tools before.

     

    Jens K. Suessmeyer

     

     

    Wednesday, November 19, 2008 9:23 AM
    Moderator
  • In addition to the above posts, this is the basic syntax for running script files:

    Code Snippet

    sqlcmd  -i c:\data\runscripts\InventoryMonthEnd.sql

    You can edit scripts files with SQLCMD features in Query Editor by switching to the SQLCMD mode.

    Code Snippet

    sqlcmd -?

    at command prompt, will give you the help page.

    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    • Edited by Kalman Toth Sunday, September 30, 2012 8:45 AM
    Thursday, November 20, 2008 1:37 PM
  • Question:

    Agreed to the fact that SQLCMD Utility is a great tool for running scripts from a command line but how about traversing through a set of multiple scripts from a location.

    I recently came through a suggestion that uses xp_cmdshell to do this but in a enterprise environment, this is almost always not allowed to be used or even in more cases, is disabled.

    Looking at the basic syntax of SQLCMD and the option of parameterizing or using variables for indicating individually each of the target scripts to be run , i think if i have around 50 scripts to be run, then is this a receommended way of approach.

    I may be mistaken or might have missed some details so please let me know if this makes any sense.

    I can create an SSIS package for this .. and could run from a job but is there a more simple and better way of doing this?

    Please comment

    Thanks for the help

    -

    John

    Friday, December 16, 2011 5:27 PM
  • I can create an SSIS package for this .. and could run from a job but is there a more simple and better way of doing this?


    SSIS is your best choice. Related article:

    How to: Automate SSIS Package Execution by Using the SQL Server Agent (SQL Server Video)


    Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER GRAND SLAM
    Friday, December 16, 2011 6:14 PM
  • Well you could use powershell as well to execute scripts from a particular location

    http://sqlblogcasts.com/blogs/martinbell/archive/2009/07/30/Executing-all-.SQL-files-in-a-directory-with-Powershell.aspx

    Saturday, December 17, 2011 12:49 AM
  • Please review batch file. I need to execute this file using sql authentication on sql server. And my log.log ,batch file, sql scripts on same folder location. Please give me advice for same. 

    -----------------------------------------------------------------------------------------------------------------------------

    @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= servername,portnumber
    @set LogFile=log.log
    echo Process Started  %DATE% , %TIME%
    echo Process Started  %DATE% , %TIME% > %LogFile%


    echo #### Processing for OneGlobalSystem-ORGBR2013-DBUpdates-v001-v002.sql Started @ %DATE% %TIME% >> %LogFile%
    SQLCMD -U xyz -P abc-S %DBServer% -d TEST-i Insert_Update_Delete_Select.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 >> %LogFile%

    :ERROR1
    ECHO Error Raised
    GOTO Return

    :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

         
    Monday, August 12, 2013 1:24 PM