Post reply

How to Execute *.sql scripts using SQLCMD in batch file format

  • August 24, 2008 at 11:50 pm

    #92470

    Hello All,

    I have a few *.sql scripts. Typically a few Create DB Scripts followed by Modify DB Scripts and Then CreateSprocs Scripts. All these scripts need to be run on a daily basis.

    Thus I want to Automate the process using a Batch Scripts os that Windows Scheduler can be used to automate at specific Time.

    I was trying with SQLCMD, but was not sure of syntax. Can I use SQLCMD or should I use something else?

    Thanks,

    Jerry

  • August 25, 2008 at 12:55 am

    #862394

    Hey Jerry,

    I would advise to create a proc that will fetch the scripts from the .sql files and schedule it.

    Regards,

    Arjun

  • August 25, 2008 at 1:44 am

    #862407

    Thanks to All,

    Here is the solution

    >> "D:\ClearviewBuild\OutputofModifyDBandCreateSprocs.txt" Echo Running ModifyBuildQueueDB

    "C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.exe" -i "D:\Build\Database\Modify Databases\ModifyCoreDatabase-5-0.sql" >> "D:\Build\OutputofModifyDBandCreateSprocs.txt"

    >> "D:\ClearviewBuild\OutputofModifyDBandCreateSprocs.txt" Echo *************************

    >> "D:\ClearviewBuild\OutputofModifyDBandCreateSprocs.txt" Echo *************************

    >> "D:\ClearviewBuild\OutputofModifyDBandCreateSprocs.txt" Echo Running AcreateAuditSprocs

    "C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.exe" -i "D:\Build\Database\Create Sprocs\CreateAuditSprocs.sql" >> "D:\Build\OutputofModifyDBandCreateSprocs.txt"

    >> "D:\ClearviewBuild\OutputofModifyDBandCreateSprocs.txt" Echo *************************

    >> "D:\ClearviewBuild\OutputofModifyDBandCreateSprocs.txt" Echo *************************

    In Brief sqlcmd -i is the command for execting sql scripts and -q is the command line parameter to execte individual SQL Scripts

    Added some commenting lines to make the log look neat

    Thanks,

    Jerry

  • March 11, 2009 at 12:07 pm

    #957778

    I am trying to setup a scheduled task using a .cmd file. I want to backup the databases. I have a script, but when the command runs the backups are not generated. I think it is the way I am calling the instance. This is for our Windows Internal database. Here is what I have in the .cmd file.

    sqlcmd -S\\.\pipe\mssql$microsoft##ssee\sql\query-E -i"E:\SQL_Backups\scripts\backupDB.sql"

  • March 11, 2009 at 12:32 pm

    #957795

    Found my problem. I had a typo. I needed a space before -E.

Viewing 5 posts - 1 through 4 (of 4 total)

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