March 25, 2009 at 6:14 am
Thanks for the article, nice introduction to a tool I've never used.
One problem I noticed when trying it out is that entries in the error file have no context so it's impossible to tell which statements generated the errors.
Let me explain; I created a simple script which connects to a server and db, sets the :error and :out variables and runs a succession of script files using the :r command. When I checked the error file afterwards it contained lines like this:
Msg 102, Level 15, State 1, Server foo, Line 5
Incorrect syntax near 'dbo'.
but there was nothing to say whether that error was a result of statements in the script or in one of the files called by :r
Is there a way to add context to the error file? I don't want to have to use many different error files...
Phil
March 25, 2009 at 1:11 pm
All in all a great article, one thing I'd like to see expanded is the backout scenero. What would be cool is to have a standard backout script that could be called in case of error(s). But that would require some a better understanding of the conditional logic in SQLCMD. I know there is a :ON ERROR Exit, but I may want something more then just an exit of the script. Anybody got ideas on how to accomplish that? Or am I just wanting to eat my cake and have it too.
Sean
I actually did use this last night this last night on a small deployment it work very well, It would be nice to be able to build deployment scripts that have more intellegince then the group I'm suppose to hand them off to.
March 25, 2009 at 7:04 pm
Very cool David, the industry needs more of what you're doing here, reproducible, auditable and error handleable. Sorry, I had to invent that last word cos it fitted in well but you get the drift don't you?
And Pieter's "low 'Black box' rating" comment just rounds it out completely.
Look forward to your next post.
Peter Edmunds ex-Geek
March 29, 2009 at 10:32 pm
thanks for the article. (i feel like a programmer all over again 🙂 )
how do i pass variables to the .sql files, from the main install file? so i create the :setvar variable 'globally', but i need to use that variable in my queries i call from the main file.
is that possible? surely it must be.
March 30, 2009 at 6:10 am
It is worth pointing out that :setvar variables are not like variables in a programming language. They are more like constants. I think it works this way: SQLCMD evaluates the value of $(VarName) just once when the script is parsed.
The $(VarName) variables can be set as follows:
1. :setvar VarName a constant string
2. VarName is an environment variable
3. The sqlcmd command line -v VarName=value
This limits the use of :setvar. For instance:
1. you cant select a value from a table and place it in $(VarName) using :setvar.
2. You cant use :setvar in a T-SQL loop.
3. You cant use @VarName variables as parameters for :connect , :r etc.
May 11, 2009 at 5:55 pm
We have been using isql and sqlcmd (recently) for the past 8 years to deploy stored procedures, triggers, functions, views, scripts to our customers and recently to our QA team.
In our release script (or batch files), we will pass in information like server, database, userid and password. In addition, we also use this script to verify the release before applying so that we will not accidentally overwrite the database.
We even go to the stage whereby we have different customers running different SQL Server version and hence will need to use either isql or sqlcmd.
August 17, 2009 at 8:57 am
I have another challenge.
Every month, I get about 15-20 scripts to be run against a database in Test Environment.
The scripts are numbered sqlscript1-15 or 30.
I somehow cannot get SQLCMD to work for it.
Has anybody, had success doing that.
If this works, I need to run the same scripts against Production.
Saving of output is also desired.
I can of course create a batch file with all scripts names hard coded, but then....there is no challenge.
Any help is welcome.
Paresh Motiwala
Boston
Paresh Motiwala Manager of Data Team, Big Data Enthusiast, ex DBA
March 8, 2010 at 11:54 am
When I first needed to script my releases about 10 years ago, I started developing an app to do it for me. I have rewritten this app a number of times and I think it is time to share it now.
This free .Net app allows you to deploy multiple commands/script files to multiple SQL Servers within a transaction.
Read about and download it here: www.sqldart.com.
I'm still busy working on the web site/documentation, but I would appreciate any feedback in the meantime.
March 10, 2010 at 12:14 am
:: Tested on Win7 and Sql Server 2008
:: THIS SCRIPT GOES TO ALL SUBFOLDERS AND RUNS THE
:: YOU NEED THE FOLLOWING FOLDER STRUCTURE:
:: ROOT - THIS IS WHERE THIS FILE IS + THE FOLLOWING SUBFOLDERS
:: --0.BackUp
:: --1.Mixed
:: --2.Tables
:: --3.StoredProcedures
:: --4.Triggers
:: --5.RollBack
@ECHO OFF
ECHO CREATE FIRST BACKUP OF ALL DATABASES ON THE DEFAULT INSTANCE ONES:
ECHO CREATING THE LOG FILES
echo THIS IS THE ERROR LOG OF THE UPDATE OF THE DBNAME ON %DATE% >error.log
echo THIS IS THE INSTALL LOG OF THE UPDATE OF THE DBNAME ON %DATE% >install.log
ECHO STARTTING BACKUP
CD .\0.BackUp
ECHO FOR EACH SQL FILE DO RUN IT THIS WILL TAKE A WHILE
ECHO SINCE WE ARE GOING TO MAKE A BACKUP FOR ALL THE DATABASES ON THE CURRENT HOST
for /f %%i in ('dir *.SQL /s /b /o') do ECHO RUNNING %%i1>>"..\install.log"&SQLCMD -U userName -P Password -H hostname -d DBNAME -t 30 -w 80 -u -p 1 -b -i %%i -r1 1>> "..\install.log" 2>> "..\error.log"
ECHO GO ONE FOLDER UP
ECHO SLEEP FOR 1 SECOND
ping -n 1 127.0.0.1 >NUL
ECHO DONE WITH BACKUP GOING UP
cd ..
ECHO THE BACKUPS ARE IN THE FOLDER
ECHO D:\DATA\BACKUPS
ECHO CLICK A KEY TO CONTINUE
ECHO ========================================================================================================================
PAUSE
ECHO START TO EXECUTE THE MIXED FILES
CD .\1.Mixed
ECHO CREATING THE LOG FILES
echo. >>"..\error.log"
echo. >>install.log
ECHO FOR EACH SQL FILE DO RUN IT
for /f %%i in ('dir *.SQL /s /b /o') do ECHO RUNNING %%i1>>"..\install.log"&SQLCMD -U userName -P Password -H hostname -d DBNAME -t 30 -w 80 -u -p 1 -b -i %%i -r1 1>> "..\install.log" 2>> "..\error.log"
ECHO GO ONE FOLDER UP
cd ..
ECHO SLEEP FOR 1 SECOND
ping -n 1 127.0.0.1 >NUL
ECHO DONE WITH MIXED GOING UP
ECHO HIT A KEY AFTER PAUSE
PAUSE
ECHO STARTING INSTALLING TABLES
CD .\2.Tables
ECHO FOR EACH SQL FILE DO RUN IT
ping -n 1 127.0.0.1 >NUL
for /f %%i in ('dir *.SQL /s /b /o') do ECHO RUNNING %%i1>>"..\install.log"&SQLCMD -U userName -P Password -H hostname -d DBNAME -t 30 -w 80 -u -p 1 -b -i "%%i" -r1 1>> "..\install.log" 2>> "..\error.log"
ping -n 1 127.0.0.1 >NUL
ECHO DONE WITH TAbles GOING UP
cd ..
ping -n 1 127.0.0.1 >NUL
ECHO HIT A KEY AFTER PAUSE
PAUSE
ECHO STARTING INSTALLING stored procedures
CD ".\3.StoredProcedures"
ECHO FOR EACH SQL FILE DO RUN IT
ping -n 1 127.0.0.1 >NUL
for /f %%i in ('dir *.SQL /s /b /o') do ECHO RUNNING %%i1>>"..\install.log"&SQLCMD -U userName -P Password -H hostname -d DBNAME -t 30 -w 80 -u -p 1 -b -i "%%i" -r1 1>> "..\install.log" 2>> "..\error.log"
ECHO DONE WITH STORED PROCEDDURES GOING UP
cd ..
ping -n 1 127.0.0.1 >NUL
ECHO HIT A KEY AFTER PAUSE
PAUSE
ECHO STARTING INSTALLING Triggers
CD ".\4.Triggers"
ECHO FOR EACH SQL FILE DO RUN IT
ping -n 1 127.0.0.1 >NUL
for /f %%i in ('dir *.SQL /s /b /o') do ECHO RUNNING %%i1>>"..\install.log"&SQLCMD -U userName -P Password -H hostname -d DBNAME -t 30 -w 80 -u -p 1 -b -i "%%i" -r1 1>> "..\install.log" 2>> "..\error.log"
ping -n 1 127.0.0.1 >NUL
ECHO DONE WITH triggers GOING UP
cd ..
ping -n 1 127.0.0.1 >NUL
ECHO HIT A KEY AFTER PAUSE
PAUSE
ECHO Please , Review the log files and sent them back to Advanced Application Support
cmd /c start /max INSTALL.LOG
CMD /C start /MAX ERROR.LOG
echo DONE !!!
ECHO HIT A KEY TO EXIT
pause
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply