SQLCMD

Change management is all about creating a reproducible way to deploy and manage your changes. This is simply impossible to do properly by having a DBA execute T-SQL scripts through a Management Studio environment. The main way to create a repeatable change management is by using sqlcmd files that may encapsulate that T-SQL logic into output logs. This way, you will know that the change will deploy to each of your environments (test, QA, Production, and so on) and give you a predictable result.

sqlcmd is a replacement for isql and osql. There are two modes you can use to execute a sqlcmd command: at a command line or in Management Studio. If you are a SQL Server 2000 DBA, the transition to sqlcmd will be an easy one for you, since it's very similar to osql, but with some additional switches to simplify your day-to-day job. You probably won't be familiar, though, with executing sqlcmd commands from Management Studio. Using Management Studio to execute these types of commands gives you lots of control and replaces many of the old extended stored procedures like xp_cmdshell. We cover both solutions in this section.

Sqlcmd from the Command Prompt

Executing sqlcmd from the command prompt allows you to run any query from a command prompt and, more important, allows you to wrap these queries into a packaged install batch file for deployments, making it easy for anyone to install the database. There are many switches in sqlcmd you can use, but most that you'll find have only a very specialized usage. The following are some of the important switches (they are all case sensitive):

• -U - User Name

• -P - Password

• -E - Use Windows Authentication. If you use this switch, you will not need to pass the -U and -P switches in.

• -S - Instance name to connect to

• -d - Database name to start in

• -i - Input file that contains the query to run

• -o - Output file where you wish to log the output of the query to

• -Q - You can also pass in the query with the -Q switch instead of an input file.

We'll start with a few basic examples to show you the commands you'll use on a regular basis. First, create a new SQL file with Notepad or the editor of your choice. Type the following query into the editor and save the file as C:\TestQuery.sql:

 SELECT * FROM Purchasing.Vendor 

Go to a command prompt and type the following command. The -i switch represents the input file that contains the file. The -S switch represents the server name to connect to. Finally, the -d switch represents the database name.

 sqlcmd -i c:\testquery.sql -S localhost -d AdventureWorks 

The query will retrieve all the records from the Vendor table and display them in the console window. If you do not specify a username and password with the -U and -P switches or the -E switch for Windows Authentication, sqlcmd will default to Windows Authentication.

A variation of this command is to use the -Q switch to pass the query to sqlcmd and then quit sqlcmd after the query is complete. The -q switch can also be used if you do not wish to exit. The other variation in the following query is the use of the -o switch. The -o switch passes the results of the query into an output file and does not display anything in the console.

 sqlcmd -Q "select * from purchasing.vendor" -d adventureworks -S localhost -o C:\testoutput.txt 

Another way you can execute sqlcmd from a command prompt is by just going to a command prompt and typing sqlcmd if you'd like to connect to your local instance or by specifying the instance with the -S switch. You will then be presented with a 1> prompt, where you can type a query. If you want to execute an operating system command from the sqlcmd window, you can type!! in front of the command, like this:

 1>!!dir 

To execute a SQL command, type the command. After you hit enter, you will see a 2> and 3> prompt until you finally issue a GO statement to execute the batch. After the GO statement, the batch runs and the prompt resets to 1>.

We've gone through the basic commands, but to simplify matters, it would be great to have a set of commands that executes each time you run sqlcmd. Initialization files run a series of commands after you execute sqlcmd but before control is handed over to the user.

To create an initialization file, first create a T-SQL file called C:\initexample.sql with Notepad or your favorite editor. This file is going to run a series of commands after you first run sqlcmd. The :setvar statement can be used to set user variables that can be used later in the script with the $ sign. This example initialization script will create three variables. One holds the database name, the other holds the 60-second timeout, and the last holds the server name. You use the variables later in the script by using $(variablename).

 :setvar DBNAME AdventureWorks :setvar sqlcmdlogintimeout 60 :setvar server "localhost" :connect $(server) -l $(sqlcmdlogintimeout) SELECT @@VERSION VersionofSQL; SELECT @@SERVERNAME as ServerName; 

Next, go to a command prompt and type the following command to set the sqlcmdini environment variable. This will set the environment variable for the profile of the user. After executing this, each time the sqlcmd program is executed, the initexample.sql script will execute before handing control to the user.

 SET sqlcmdini=c:\initexample.sql 

With the environment variable now set, just run sqlcmd from a command prompt (shown in Figure 10-3). After you run this, you will see the version of SQL Server and the server name before you're given control to run any query against the database.

Figure 10-3

Sqlcmd from Management Studio

Eventually, when doing your day-to-day job, you're going to find yourself wanting to integrate sqlcmd scripts into your regular T-SQL scripts, or someone may pass you a script that has sqlcmd integrated into it. To use this script in Management Studio's query environment, simply click the SQLCMD Mode icon in the Query Window. If you try to execute sqlcmd syntax from within the Query Window without being in SQLCMD Mode, you will receive an error like this:

 Msg 102, Level 15, State 1, Line 3 Incorrect syntax near '!'. 

sqlcmd and T-SQL can intermingle in the same script, and you can go in between script types easily as long as SQLCMD Mode is enabled. Once enabled, any sqlcmd syntax will be highlighted in grey. You can also enable the mode each time a query is opened by going to ToolsOptions in Management Studio. In the SQL Server page of the Query Execution group (shown in Figure 10-4), enable the option "By default, open new queries in SQLCMD mode." After enabling this option, any subsequent query windows will be opened with the mode enabled.

Figure 10-4

By using sqlcmd and T-SQL, you can connect to multiple servers from within the same script and run T-SQL statements. For example, the following script will log on to multiple servers and back up the master database of each server. The :CONNECT command is followed by the instance name that you wish to connect to. This is a simple example, but it can be strengthened in a disaster recovery situation to do massive repairing of your databases or SQL Server instances.

 :CONNECT localhost BACKUP DATABASE master TO DISK = N'C:\test.bak' :CONNECT localhost\sql2k5test BACKUP DATABASE master TO DISK = N'C:\test2.bak' 

You can also use the :SETVAR command inside of sqlcmd to create a script variable. This variable allows you to either set the variable from within the :SETVAR command, as shown in the following code, or can set it by passing in the variable through the command prompt. The following example shows you how to set a variable called SQLServer to the value of Localhost. You then use that variable by using the variable name prefixed with a dollar sign and wrapping it in parenthesis. Another variable called DBNAME is also created and used in the T-SQL backup command. As you can see, in sqlcmd, you can mix T-SQL and sqlcmd easily, and the variables can intermix.

 :SETVAR SQLServer Localhost :CONNECT $(SQLServer) :SETVAR DBNAME Master BACKUP DATABASE $(DBNAME) TO DISK = N'C:\test.bak' 

Now take the preceding script and modify it slightly to look like the following script. Then save it to a file called C:\InputFile.sql (place the file wherever is appropriate on your machine). This script will dynamically connect to a server and back up the master database.

 :CONNECT $(SQLServer) BACKUP DATABASE master TO DISK = N'C:\test.bak' 

With the script now saved out, you can go to a command prompt and type the following command using the -v switch (all of these switches are case sensitive) to pass in the variable. In this case, you pass in the name localhost to the SQLServer variable.

 sqlcmd -i c:\inputfile.sql -v SQLServer=" localhost"