SQLServerCentral / Discuss Content Posted by David Poole / Article Discussions / Article Discussions by Author / Deploying Scripts with SQLCMD / Latest Posts

RE: Deploying Scripts with SQLCMD

yordan.georgiev — Wed, 10 Mar 2010 00:14:55 GMT

:: 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 OFFECHO CREATE FIRST BACKUP OF ALL DATABASES ON THE DEFAULT INSTANCE ONES: ECHO CREATING THE LOG FILESecho THIS IS THE ERROR LOG OF THE UPDATE OF THE DBNAME ON %DATE% >error.logecho THIS IS THE INSTALL LOG OF THE UPDATE OF THE DBNAME ON %DATE% >install.logECHO STARTTING BACKUP CD .\0.BackUpECHO 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 >NULECHO DONE WITH BACKUP GOING UP cd ..ECHO THE BACKUPS ARE IN THE FOLDERECHO D:\DATA\BACKUPSECHO CLICK A KEY TO CONTINUEECHO ========================================================================================================================PAUSEECHO START TO EXECUTE THE MIXED FILESCD .\1.MixedECHO CREATING THE LOG FILESecho. >>"..\error.log"echo. >>install.logECHO 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 >NULECHO DONE WITH MIXED GOING UP ECHO HIT A KEY AFTER PAUSE PAUSEECHO STARTING INSTALLING TABLES CD .\2.TablesECHO FOR EACH SQL FILE DO RUN IT ping -n 1 127.0.0.1 >NULfor /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 >NULECHO DONE WITH TAbles GOING UP cd .. ping -n 1 127.0.0.1 >NUL ECHO HIT A KEY AFTER PAUSE PAUSEECHO STARTING INSTALLING stored proceduresCD ".\3.StoredProcedures"ECHO FOR EACH SQL FILE DO RUN IT ping -n 1 127.0.0.1 >NULfor /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 PAUSEECHO STARTING INSTALLING TriggersCD ".\4.Triggers"ECHO FOR EACH SQL FILE DO RUN IT ping -n 1 127.0.0.1 >NULfor /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 >NULECHO DONE WITH triggers GOING UP cd .. ping -n 1 127.0.0.1 >NUL ECHO HIT A KEY AFTER PAUSE PAUSEECHO Please , Review the log files and sent them back to Advanced Application Support cmd /c start /max INSTALL.LOGCMD /C start /MAX ERROR.LOGecho DONE !!!ECHO HIT A KEY TO EXIT pause

RE: Deploying Scripts with SQLCMD

danpep — Mon, 08 Mar 2010 11:54:20 GMT

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: [url=http://www.sqldart.com]www.sqldart.com[/url].I'm still busy working on the web site/documentation, but I would appreciate any feedback in the meantime.

RE: Deploying Scripts with SQLCMD

Paresh Motiwala — Mon, 17 Aug 2009 08:57:06 GMT

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 MotiwalaBoston

RE: Deploying Scripts with SQLCMD

peter_wong — Mon, 11 May 2009 17:55:14 GMT

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.

RE: Deploying Scripts with SQLCMD

Renato Buda-153382 — Mon, 30 Mar 2009 06:10:33 GMT

It is worth pointing out that :setvar variables are [i]not[/i] 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 [i]a constant string[/i]2. VarName is an environment variable3. The sqlcmd command line -v VarName=valueThis 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.

RE: Deploying Scripts with SQLCMD

h_d_t — Sun, 29 Mar 2009 22:32:28 GMT

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.

RE: Deploying Scripts with SQLCMD

wldhrs — Wed, 25 Mar 2009 19:04:05 GMT

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.

RE: Deploying Scripts with SQLCMD

sean hawkes — Wed, 25 Mar 2009 13:11:09 GMT

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.SeanI 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.

RE: Deploying Scripts with SQLCMD

philip.griffiths — Wed, 25 Mar 2009 06:14:45 GMT

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 5Incorrect 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 :rIs there a way to add context to the error file? I don't want to have to use many different error files...Phil

RE: Deploying Scripts with SQLCMD

scottm30 — Tue, 24 Mar 2009 18:05:59 GMT

Great article. Thank you for providing this information David.Your article saved me a great deal of work this morning thanks to the SQLCMD information you provided.RegardsScott

RE: Deploying Scripts with SQLCMD

matthew.mark.ctr — Tue, 24 Mar 2009 16:10:19 GMT

Sounds promising but I seem to be missing something. I have scripts I need to run on all user databases. I've tried using sp_MSforeachDB with "Use DB_ID('?')" and dynamic sql. But since some of my scripts have nested sql statements, it's been a nightmare. I've found a few good articles on dynamic sql and I'm somewhat successful. So then I found this article on SQLCMD mode using variables. It's promising. However, I can run a procedure on one database just fine. The err.out file works great.However, Does anyone know how to use this to run one or more scripts against many databases on the same server?This is what I am using for one DB:***********************:setvar SQLInstance "DV-FS49":setvar ScriptDirectory "\\FP49\Database\Scripts\Upgrade to 2005\Preparation scripts\" :error $(ScriptDirectory)Script.err :out $(ScriptDirectory)Script.out :connect $(SQLInstance) :setvar DB "BarCodes" USE $(DB) :r $(ScriptDirectory)CreateFunctionGetObjectDefinition.sql ***********************Thanks

RE: Deploying Scripts with SQLCMD

Tom Bakerman — Tue, 24 Mar 2009 13:43:01 GMT

Good stuff. One suggestion, especially since you mention deploying to many servers. Remove the [b]:setvar[/b] statements, and declare them externally (probably as environment variables if you're going to run this from SSMS). I've done this with "build" scripts created from Visual Studio. I have a .bat file that executes the scripts via SQLCMD, and cycles through my servers executing each script.Using SQLCMD mode from SSMS is nice in that it gives us the opportunity to test the scripts before putting them into production.

RE: Deploying Scripts with SQLCMD

Mohit K. Gupta — Tue, 24 Mar 2009 12:14:30 GMT

Excellent article; I was thinking of doing something similar. As I been tasked with automating releases; so once developer submit it it gets executed in proper server/database automatically and sends an email alert saying it's done.I was thinking of using SQLCMD and its abilities to achive this. Thanks much sir!Mohit.

RE: Deploying Scripts with SQLCMD

Pieter-423357 — Tue, 24 Mar 2009 07:32:44 GMT

Great Article, very powerfull, simple and low 'Black box' rating, unlike other apps.For our automation, I have wrapped the Commandline version into a VB.NET program. The only problem we see is that I can only output 4000 bytes per script. Sometimes I need to see a top 10 of some table that was just created and 4000 bytes is nothing. Any suggestions to get around this limit, or it this a DOS thing?

RE: Deploying Scripts with SQLCMD

David.Poole — Tue, 24 Mar 2009 07:28:26 GMT

[quote][b]kuldip.bhatt (3/24/2009)[/b][hr]how i give the usrename and password in the sql command?:setvar Maindbserver "NHSserver":setvar Maindb "NHS_031209":Connect $(Maindbserver) go$ maindb [/quote]:connect $(Maindbserver) -U YourUserName -P YourPasswordPersonally I would avoid SQL standard security for scripts like the plague. It would mean you have a clear text file with credentials exposed.

RE: Deploying Scripts with SQLCMD

alen teplitsky — Tue, 24 Mar 2009 06:59:55 GMT

good stuff, we're not under the same constraints here but using this we can build a deployment pack and execute it. the way it is now it's a manual process of running different scripts as specified by dev

RE: Deploying Scripts with SQLCMD

Phil Factor — Tue, 24 Mar 2009 06:27:45 GMT

OOh yes. SQLCMD! I agree wholeheartedly that it is an essential for quickly automating complex processes across several servers. I love it. It is so good that one is tempted to keep it to oneself, and amaze people with one's wizardry. Robyn and I wrote a workbench a while back which might help as a follow-up to David's excellent introduction. [url=http://www.simple-talk.com/sql/sql-tools/the-sqlcmd-workbench/]The SQLCMD workbench[/url]I notice that the SQL Server 2008 version of SQLCMD is very similar to that of SQL Server 2005. Have they improved anything, or is SQLCMD one of those tools they have left to fester?

RE: Deploying Scripts with SQLCMD

kuldipMCA — Tue, 24 Mar 2009 06:07:56 GMT

how i give the usrename and password in the sql command?:setvar Maindbserver "NHSserver":setvar Maindb "NHS_031209":Connect $(Maindbserver) go$ maindb this is my code.in this script how i can give user name and password?My question is if diff servers has diffrent password then what is the solution for this.you must give Password and usrename.

RE: Deploying Scripts with SQLCMD

Renato Buda-153382 — Tue, 24 Mar 2009 06:00:42 GMT

Great article, David.I had not seen examples of the error and standard outputs before.Kuldip: it looks as though your windows login does not have windows integrated access to the server. You need to supply a SQL user name and password as part of the SQLCMD connect command or on the command line.[quote][b]kuldip.bhatt (3/24/2009)[/b][hr]Fatal scripting error. Cannot open connection specified in the SQLCMD script.Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.i got like that error when i do connect with the use of the sqlcmd.[/quote]

RE: Deploying Scripts with SQLCMD

yordan.georgiev — Tue, 24 Mar 2009 05:31:24 GMT

for the most bravest here is a perl script to run multiple sql scirpts ... requirement: the scirpts should be numbered: 1.RUN.scriptname.sql2.RUN.scriptName2.sql3... n. RUN.scirptname3.sqland placed in the same directory as the perl scriptYou can create a command line executable for it ( does not required Perl on the running machine ) with par or perl2exe ... Anybody interested in ... I could put a link for binary download it ... Grab the source at : http://ysgitdiary.blogspot.com/2009/03/perl-script-to-run-sql-scripts-for-sql.htmlComments , shouts boos etc. would be highly appreciated .. .

RE: Deploying Scripts with SQLCMD

Jim Russell-390299 — Tue, 24 Mar 2009 05:13:49 GMT

Excellent!I have reached the stage in life where if it isn't scripted, I screw it up. How nice to learn about yet another scripting strategy.

RE: Deploying Scripts with SQLCMD

kuldipMCA — Tue, 24 Mar 2009 05:11:12 GMT

Fatal scripting error. Cannot open connection specified in the SQLCMD script.Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.i got like that error when i do connect with the use of the sqlcmd.

RE: Deploying Scripts with SQLCMD

Adam Aspin — Tue, 24 Mar 2009 02:40:59 GMT

Good stuff!Have you ever had the (mis)fortune to use MSbuild too?Adam

Deploying Scripts with SQLCMD

David.Poole — Tue, 24 Mar 2009 00:35:57 GMT

Comments posted to this topic are about the item [B]Deploying Scripts with SQLCMD[/B]