SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Deploying Scripts with SQLCMD


Deploying Scripts with SQLCMD

Author
Message
Pieter-423357
Pieter-423357
Mr or Mrs. 500
Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)

Group: General Forum Members
Points: 555 Visits: 577
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?
Mohit K. Gupta
Mohit K. Gupta
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5384 Visits: 1089
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.
Tom Bakerman
Tom Bakerman
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1453 Visits: 931
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.
matthew.mark.ctr
matthew.mark.ctr
SSC-Enthusiastic
SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)

Group: General Forum Members
Points: 123 Visits: 282
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
scottm30
scottm30
SSC Eights!
SSC Eights! (825 reputation)SSC Eights! (825 reputation)SSC Eights! (825 reputation)SSC Eights! (825 reputation)SSC Eights! (825 reputation)SSC Eights! (825 reputation)SSC Eights! (825 reputation)SSC Eights! (825 reputation)

Group: General Forum Members
Points: 825 Visits: 383
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
philip.griffiths
philip.griffiths
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 183
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
sean hawkes
sean hawkes
SSC-Addicted
SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)

Group: General Forum Members
Points: 424 Visits: 357
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.
wldhrs
wldhrs
Old Hand
Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)

Group: General Forum Members
Points: 337 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
h_d_t
h_d_t
SSC-Enthusiastic
SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)

Group: General Forum Members
Points: 188 Visits: 821
thanks for the article. (i feel like a programmer all over again Smile )

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.
R M Buda
R M Buda
SSC Eights!
SSC Eights! (919 reputation)SSC Eights! (919 reputation)SSC Eights! (919 reputation)SSC Eights! (919 reputation)SSC Eights! (919 reputation)SSC Eights! (919 reputation)SSC Eights! (919 reputation)SSC Eights! (919 reputation)

Group: General Forum Members
Points: 919 Visits: 306
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search