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 »


  • @SQLTrooper: @gordoncoulter @arvisam I personally use WHILE (1=1) inf loops w/ WAITFOR DELAYS, in ~3K batches, etc. Adjust accordingly. #sqlhelp
  • @arvisam: @gordoncoulter you could also do a select into a new table and swap the two tables... could be much faster that way! #sqlhelp
  • @SQLSoldier: @pappyd I would at least schedule it to be done during an upcoming maintenance window after the migration. #sqlhelp
  • @SQLSoldier: @pappyd run updateusage is to make sure they are correct to start off with. 2/2 #sqlhelp
  • @SQLSoldier: @pappyd In earlier versions, there were sometimes inaccuracies in the usage stats. These issues have been fixed and the rec to 1/2 #sqlhelp
  • @pappyd: I am planning to run DBCC CHECKDB & sp_updatestats, but would like to not run UPDATEUSAGE to save some time #sqlhelp
  • @NedOtter: and then renames to valid extension when copy is complete. Thx. #sqlhelp 3/3
  • @NedOtter: it cannot get exclusive access. Wondering if my test is invalid, perhaps because MS copies with a different extension, #sqlhelp 2/3
  • @NedOtter: Anyone know log shipping internals? On 2ndary, if I use posh to exclusively lock tlog backup, restore fails because #sqlhelp 1/3
  • @rottengeek: what is MEMORYBROKER_FOR_XTP? super-happy it was able to give up memory when my prod server ran out, but i have SQL 2012 #sqlhelp

Welcome , today is Tuesday, June 28, 2016