Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Deploying Scripts with SQLCMD Expand / Collapse
Author
Message
Posted Tuesday, March 24, 2009 7:32 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, May 5, 2014 8:55 AM
Points: 86, Visits: 478
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
Posted Tuesday, March 24, 2009 12:14 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 4:22 PM
Points: 942, Visits: 1,062
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.


How to ask for help .. Read Best Practices here.
Post #682678
Posted Tuesday, March 24, 2009 1:43 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 18, 2014 10:20 AM
Points: 110, Visits: 771
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
Posted Tuesday, March 24, 2009 4:10 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 12, 2013 3:01 PM
Points: 13, 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
Post #682925
Posted Tuesday, March 24, 2009 6:05 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, December 3, 2013 4:40 PM
Points: 654, Visits: 375
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
Posted Wednesday, March 25, 2009 6:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 24, 2014 2:46 AM
Points: 5, Visits: 166
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
Posted Wednesday, March 25, 2009 1:11 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 25, 2013 5:29 AM
Points: 28, Visits: 355
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
Posted Wednesday, March 25, 2009 7:04 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued 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
Posted Sunday, March 29, 2009 10:32 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 12:08 AM
Points: 7, Visits: 743
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
Posted Monday, March 30, 2009 6:10 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 8, 2014 6:02 AM
Points: 146, Visits: 238
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 »

Add to briefcase ««123»»

Permissions Expand / Collapse