Need help to write bat file that execute sql scripts in (sql server 2008 and another 3 files.?
I am sure these has been asked before but cannot find clear instruction how to create a batch file lets call it "Update Database" this batch file should
Execute sql scripts located in different folders Execute another 3 bat files.
Any quick examples how to do it?Never done it before thanks a lot
EDITED
Can I do this?
:On Error exit
:r C:\myPath\MasterUpdateDatabase.bat
GO
SQLCMD -S (Local) -i C:\myPath\InsertUsername.sql
I get an error:
"GO" is not recognized as internal external command
Thanks for any input
Answer
It looks like you're trying to use DOS commands to create a batch file that either (a) executes other batch files or (b) executes SQLCMD to run sql or a sql script.
Here are a couple examples all rolled into one. I'm using the DOS command START with the /WAIT switch, which will keep your original "master" batch file running in one window and execute the subsequent file or commands in a new window. That new window stays open until the script finished AND exits.
Some of the ECHOs probably aren't required, but the script will talk back to you for now, a little.
@echo off
So, this is pretty simple in the sense that you're just running the script. If you're script1.bat has break points, you can return an error back to the main script and have it end immediately. I wasn't clear if that was what you needed the master script to do.
echo Starting Database Update.
echo.
echo Excuting Script 1
echo.
start /wait C:\path\to\your\script1.bat
echo If there was a problem, break here.
Pause
echo Excuting Script 2
echo.
start /wait C:\path\to\your\script2.bat
echo If there was a problem, break here.
Pause
Here is where did used the same START /WAIT to run SQLCMD, which in this case just returns results from the query. One thing to note here is that the -Q (uppercase) runs the query and quits. If you use -q (lowercase) it will run the query and sit open in SQLCMD waiting for another query.
echo.
echo Running SQLCMD: "select top 100 * from sys.objects"
start /wait sqlcmd -S (local) -Q "select top 100 * from sys.objects"
And this is how you can run a sql script, which is what the -i denotes, but I also didn't run this in the START /WAIT as earlier. Not that you have to, but I wanted to show both examples. What this also shows is the -b will end the batch process if your script returns an error, which is useful if you're running multiple scripts that depend on success of the former(s).
echo.
echo Running SQLCMD from an (-i)nput file:
sqlcmd -S (local) -i C:\path\to\your\script.sql -b
echo.
echo Update Complete.
pause
End
So, I assumed you were looking for a .bat or .cmd file that utilized SQLCMD. The example I provided is pretty basic, but hopefully it sets you on the right path.
OH! And remember that CTRL+C breaks a batch script in process.