Phil Parkin (5/5/2013)
Hi Wayne.Nice article, thank you.
Can you explain what you mean by: "...you can specify what the server is change all the variables at one place simply" please - it reads a little strangely to me.
Wow, I can't believe that slipped through. Thanks for catching this Phil.
In looking back over the entire sentence
By invoking the “Specify Values for Template Variables” dialog (CTRL+SHIFT+M, or the third menu item in the first screenshot in this article of the Query menu), you can specify what the server is change all the variables at one place simply.
, I am using a SQLCMD variable (set with SETVAR) to store a server's name to connect to. This allows me to have the server specified in one location, and to just reference it in the scripts. In the saved script, the server name is saved in the format used by templates (in the :SETVAR line), so when I open the script I just do the CTRL+SHIFT+M to change the variables to their desired value, and it's done. Elsewhere in the script, I use the SQLCMD variable. True, I can save this same template in place of each place that I need to reference this server, but I find it easier to use a SQLCMD variable here.
Another thing of interest - is there a way of using SQLCMD mode to run a command on one server, capture the result in a variable and use that result in subsequent commands against other servers, within a single script?
Since you have to issue a GO after the connect to a subsequent server, a TSQL variable would be lost. And you cannot set SQLCMD variables in this manner. Once you change a connection, any other approach that I've been able to think of (global temporary table, OpenRowset to save to a fixed file) would be scoped to the server, so this also would not work. (OpenRowset would work if these were multiple instances on the same server.) So, I cannot think of any way of doing this. Perhaps other readers have a solution?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes