Deploying Scripts with SQLCMD

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by David Poole » Deploying Scripts with SQLCMD

24 posts, Page 2 of 3

««1 2 3 »»

Deploying Scripts with SQLCMD

Rate Topic

Display Mode

Topic Options

Author

Message

Pieter-423357

Posted Tuesday, March 24, 2009 7:32 AM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, March 12, 2013 1:26 PM
Points: 86, Visits: 473

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?

Post #682312

Mohit K. Gupta

Posted Tuesday, March 24, 2009 12:14 PM

SSC Eights!

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:37 PM
Points: 941, Visits: 1,041

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.

---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN.
Microsoft FTE - SQL Server PFE

* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. Smooooth

How to ask for help .. Read Best Practices here.

Post #682678

Tom Bakerman

Posted Tuesday, March 24, 2009 1:43 PM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, May 10, 2013 6:33 AM
Points: 76, Visits: 568

Good stuff. One suggestion, especially since you mention deploying to many servers. Remove the :setvar 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.

Post #682778

matthew.mark.ctr

Posted Tuesday, March 24, 2009 4:10 PM

Grasshopper

Group: General Forum Members
Last Login: Monday, May 20, 2013 3:01 PM
Points: 13, Visits: 273

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

Post #682925

scottm30

Posted Tuesday, March 24, 2009 6:05 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, June 13, 2012 3:26 PM
Points: 566, Visits: 341

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.

Regards
Scott

Post #682985

philip.griffiths

Posted Wednesday, March 25, 2009 6:14 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, May 16, 2013 4:05 AM
Points: 5, Visits: 147

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

Post #683226

sean hawkes

Posted Wednesday, March 25, 2009 1:11 PM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, March 28, 2013 2:50 PM
Points: 27, Visits: 348

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.

Post #683597

wldhrs

Posted Wednesday, March 25, 2009 7:04 PM

Valued Member

Group: General Forum Members
Last Login: Wednesday, December 15, 2010 6:35 PM
Points: 63, Visits: 193

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

Post #683796

h_d_t

Posted Sunday, March 29, 2009 10:32 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, May 20, 2013 6:18 AM
Points: 5, Visits: 631

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.

Post #685860

Renato Buda-153382

Posted Monday, March 30, 2009 6:10 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 25, 2013 8:57 PM
Points: 139, Visits: 214

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.

Post #685996

« Prev Topic | Next Topic »

24 posts, Page 2 of 3

««1 2 3 »»

Permissions