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:
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.