My contribution this week is about overcoming the variable limitations of SQLCmd mode. Most people know what SQLCmd is, the command line SQL client utility for running T-SQL, or perhaps if you are a PowerShell enthusiast you may be more familiar with its little brother invoke-sqlcmd. But perhaps you are not aware that you can take advantage of SQLCmd functionality in scripts you run directly in SQL Server Management Studio (SSMS). Unfortunately, along with the benefits of using SQLCmd mode, you also get the limitations. The limitation I’m going to talk about here is one that I had to figure out how to overcome recently, and I’m going to share what I learned with you today.
SQLCmd Mode and Variable Limitations
If you’re not familiar with SQLCmd or SQLCmd mode in SSMS, take a look at the different options you get with SQLCmd in Books Online. To enable SQLCmd mode in SSMS, put the focus on the query window, click Query in the menu, and click SQLCMD Mode. SQLCmd commands will be automatically highlighted for you if you already have some in the script.One of the commands that makes SQLCmd mode really appealing is the Connect command. This command allows you to connect to a specific server inline in the script which is great for those times when you need to do something that requires jumping back and forth between multiple servers. I was recently tasked with writing a script in T-SQL (please don’t ask why I didn’t use PowerShell, the project requirement was T-SQL) to automate setting up and taking down Peer-to-Peer replication which has this need.
The downside of the Connect command is that the command is processed immediately when the script batch runs and not where it occurs inline. So if you have multiple Connect commands in the script batch, they will all be processed immediately, and then it will process the remainder of the commands in the batch. Did you notice that I keep saying “batch”?
The key to using multiple Connect commands is that you have to use them in different batches separated by the GO batch separator. The batch separator is actually configurable, but the default is GO and changing it is a topic for another day. This raises another limitation though. I needed to assign values to variables to use throughout the script.
T-SQL variables are batch scoped, and I cannot use them across batches. So if I want to be able to assign some constant variables (such as database name) to use throughout the script, I would have to re-declare them in each batch. What a pain and poor user experience that would be. This was the limitation I needed to overcome. I needed to be able to declare variables once and use them across batches without re-declaring them.
Problem Solved
The solution is that I learned that SQLCmd variables are scoped to the the entire connection and persist across batches. YAY!
Using SQLCmd variables is a little different than using T-SQL variables. You reference SQLCmd variables by enclosing them in parenthesis preceded by a dollar sign ($). So instead of a variable like @DBName, I would initialize the variable as DBName and reference it in the script as $(DBName).
In T-SQL, this would look like:
Declare @DBName sysname = N'MyDB';
In SQLCmd mode, like this:
:SETVAR DBName "MyDB"
SQLCmd variables are processed immediately at run-time before the T-SQL is compiled. As a result, I can use a SQLCmd variable just like I could the value itself. The really cool thing about this is that I can use the DBName SQLCmd variable as the database name inline as if it was the name itself.
This is not a valid T-SQL query:
Select * From @DBName.sys.data_files;
This is a valid T-SQL Query when executing it in SQLCmd mode:
Select * From $(DBName).sys.data_files;
Likewise, I could use it in a use command:
USE $(DBNAME);
Or inline in a string without have to concatenate the variable to the string:
RaisError('Error in database $(DBName).', 15, 1);
But by far the coolest aspect of the SQLCmd variables is that they are connection scoped and persist across batches. I can assign the value once and reuse it across multiple batches without having to re-declare them.
To demonstrate what I mean, the following T-SQL script would fail to compile due to the variable not being declared in subsequent batches:
Declare @DBName sysname = N'MyDB'; Select * From sys.databases Where name = @DBName; GO Select * From sys.databases Where name = @DBName;
In order for this to work, I would have to declare the variable and assign it a value in every batch. Like this:
Declare @DBName sysname = N'MyDB'; Select * From sys.databases Where name = @DBName; GO Declare @DBName sysname = N'MyDB'; Select * From sys.databases Where name = @DBName;
If running in SQLCmd mode, the following T-SQL script would work perfectly:
:SETVAR DBName "MyDB" :SETVAR Server1 "MyServer1" :SETVAR Server2 "MyServer2" :Connect $(Server1) Select * From sys.databases Where name = N'$(DBName)'; GO :Connect $(Server2) If Exists (Select 1 From sys.databases Where name = N'$(DBName)') Begin Select * From $(DBName).sys.database_files; End
Summary
Yes, there are some limitations with using SQLCmd mode, but it also enables us to do some pretty cool things that we just can’t do with straight T-SQL. Figuring out how to work within these limitations and to take full advantage of the benefits, we can do a lot of things we thought we couldn’t.
I hope you learned something just like I did. And I hope you feel compelled to use SQLCmd mode when it is appropriate in some of your scripts.
Ray Herring
SetVar is great and I understand how it works.
I wish I could come up with a way to
1. Have SetVar in the script to set a default value
2. Optionally override the in script SetVar value from the command line using -v
SQLSoldier
Yes. If being able to execute some conditional T-SQL logic to re-set the variable value at run-time. Unfortunately, it just doesn’t work that way.