Day 7 – Command ‘n Conquer

This is the seventh installment in the 12 day series for SQL tidbits during this holiday season.

Previous articles in this mini-series on quick tidbits:

  1. SQL Sat LV announcement
  2. Burning Time
  3. Reviewing Peers
  4. Broken Broker
  5. Peer Identity
  6. Lost in Space

As a DBA, we sometimes like to shortcut things.  Not shortcutting a process or something of importance.  The shortcuts are usually in the realm of trying to shortcut time, or shortcut the number of steps to perform a task or shortcutting by automating a process.

We seldom like to perform the same task over and over and over again.  Click here, click there, open a new query window, yadda yadda yadda.  When you have 100 or so servers to run the same script against – it could be quite tedious and boring.  When that script is a complete one-off, there probably isn’t much sense in automating it either.

To do something like I just described, there are a few different methods to get it done.  The method I like to use is via SQLCMD mode in SSMS.  Granted, if I were to use it against 100 servers, it would be a self documenting type of script.  I like to use it when setting up little things like replication.

How many times have you scripted a publication and the subscriptions?  How many times have you read the comments?  You will see that the script has instructions to run certain segments at the publisher and then other segments at the subscriber.  How many times have you handed that script to somebody else to run and they just run it on the one server?

Using SQLCMD mode and then adding a CONNECT command in the appropriate places could solve that problem.  The only thing to remember is to switch to SQLCMD mode in SSMS.  Oh and switching to SQLCMD mode is really easy.  The process to switch to SQLCMD mode is even documented.  You can read all about that here.

And there you have it, yet another simple little tidbit to take home and play with in your own little lab.

5 Comments - Leave a comment
  1. […] Command ‘n Conquer […]

  2. […] Command ‘n Conquer […]

Leave a comment

Your email address will not be published. Required fields are marked *

December 2013
« Nov   Jan »


  • @RP_RATNA: VM hitting 100 % cpu always SQL load is normal but still CPU 100 % how to trouble shoot checked all top performing queries #sqlhelp
  • @SQLSoldier: @grocep Yes you can. You probably won't notice either way unless you're under heavy external memory pressure. #sqlhelp
  • @grocep: Can i set ‘min server memory’ to 1/2 total memory on a SQL VM with ballooning turned off? Is it good or bad practice? #sqlhelp
  • @intentShared: #sqlhelp in 2012 you can add an AG listener and immediately start listening, in 2008r2 anyway to pick up a new IP without restarting svc?
  • @AdamMachanic: @thinbluedba have you looked at application roles? #sqlhelp
  • @Kos1mo: @AngryPets Yes, thank you for putting it in context. #sqlhelp @SQLSoldier
  • @AngryPets: @Kos1mo @SQLSoldier #sqlhelp It was replaced, effectively, by Linq to SQL then the EF. (Put another way: they're why there are no updates.)
  • @thinbluedba: #sqlhelp I'm looking for something like a proxy that would hold/prevent certain queries from running within SSMS, does Hexatier do that?
  • @Kos1mo: @SQLSoldier thanks that helps. Am looking at code that looks like it came from that sample. Binging for rants/wisdom about it. :) #sqlhelp
  • @SQLSoldier: @Kos1mo Last update I can see was June 2005. I don't think it is used any more nor replaced by something. #sqlhelp

Welcome , today is Friday, May 6, 2016