Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to Execute *.sql scripts using SQLCMD in batch file format Expand / Collapse
Author
Message
Posted Sunday, August 24, 2008 11:50 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 12, 2009 4:21 AM
Points: 2, Visits: 4
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
Post #557945
Posted Monday, August 25, 2008 12:55 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, June 3, 2010 7:25 AM
Points: 75, Visits: 186
Hey Jerry,
I would advise to create a proc that will fetch the scripts from the .sql files and schedule it.
Regards,
Arjun
Post #557970
Posted Monday, August 25, 2008 1:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 12, 2009 4:21 AM
Points: 2, Visits: 4
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
Post #557988
Posted Wednesday, March 11, 2009 12:07 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 15, 2014 3:22 PM
Points: 1,155, Visits: 547
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"
Post #673594
Posted Wednesday, March 11, 2009 12:32 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 15, 2014 3:22 PM
Points: 1,155, Visits: 547
Found my problem. I had a typo. I needed a space before -E.
Post #673614
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse