Command - Line Tools - T-SQL

SQL Server comes with plenty of great graphical tools to accomplish almost everything you could ever need to do, but there also comes a time when a simple command-line tool is the best tool for the job. The two tools used most frequently are SQLCMD and BCP, but there are many more. This section will describe just the SQLCMD utility because it is the one that will be used with T-SQL. For more information about all the command-line tools supported by SQL Server, check out SQL Server Books Online under the topic "Command Prompt Utilities.”

SQLCMD

The SQLCMD utility replaced OSQL starting with SQL Server 2005 as the utility used to execute T-SQL statements, stored procedures, and SQL script files from the command prompt. SQLCMD utilizes OLE DB (Object Linking and Embedding, Data Base) as the interface to connect to SQL Server and execute T-SQL. OLE DB is a very efficient mechanism for connecting to databases and provides many more error handling and connection options than the classic ODBC (Open Data Base Connectivity) connection objects used with OSQL.

The SQLCMD utility enables you to use variables, connect to servers dynamically, query server information, and pass error information back to the calling environment.

SQLCMD supports several arguments that change the way it behaves and how it connects to an instance of SQL Server. An abbreviated list is included in the following table. For a complete list of the argument options, consult SQL Server Books Online under the topic "SQLCMD Utility.” Note that SQLCMD command-line arguments are case sensitive.

SQL CMD

The SQLCMD utility typically is used to execute saved T-SQL scripts in batch processes. This functionality is further enhanced by the ability of SQLCMD to accept scripting parameters. SQL Server Management Studio makes the creation of SQLCMD scripts even easier with its SQLCMD Mode. You can write and test the scripts with Management Studio by selecting SQLCMD Mode on the SQL Editor toolbar.
Multiple variables can be declared and their values set with the SETVAR command as well as passed in to a SQLCMD script with the -v argument. The following example shows how to use multiple SETVAR commands by creating two variables, ColumnName and TableName. The SETVAR command not only creates the variables, it also specifies the value of the variables.

When testing this example, make sure to select the SQLCMD mode on the Query menu so that the query is run through the SQLCMD command. (see Figure In The Below).

Command---Line-Tools

Now let's use the SQLCMD utility from the command prompt. First, modify the preceding example and remove the SETVAR commands. Then save the script to a file as Get Products.SQL. I am saving it to the root of my C: drive. The script should look like the following example:

You could execute this script with the SQLCMD utility by using the following command line:

Because SQLCMD isn't a particularly interactive environment, actions must be performed using a single-line command. When you launch SQLCMD, you must provide login information — either a username and password for SQL Server authentication, or a switch to indicate that you want to use integrated Windows security. The following example uses integrated security by using the /E switch. (This stands for Enterprise security.) Note that the documentation for the /E switch shows that this means trusted connection. I am using it with the /S switch that specifies the server to connect to. If the server is not specified, the SQLCMD utility will attempt to connect to the local default instance of SQL Server. When you press Enter, a new prompt is displayed that shows the first line of a T-SQL batch along with a caret symbol:

SQLCMD /E /S WoodVista 1>

This indicates that you are now working in the SQLCMD environment rather than at the command prompt. It also lets you know that this is the first line in a batch process. SQLCMD runs all commands in batch mode and doesn't actually execute any commands until you explicitly tell it to, using the GO command. You can write as many lines of code as you want, but they will not be executed until the GO command is specified. You can think of the GO command very much the same as the Execute button on the SQL Editor toolbar. SQL batches are explained in detail in Chapter(Introducing the T-SQL Language) . For now, I'll continue to enter SQL commands and then type GOwhen I'm ready to execute the entire batch. Notice that the batch line number resets after every GO statement (Figure (Show In Above) ).Although the SELECT command asked only to return two columns, the results used up most of my screen real estate. Each character type column will use the maximum number of allocated characters. This means that if you have a column defined as varchar(2 55), even if the actual data doesn't take up this much space, this column will require 255 characters of screen space, not allowing much room for anything else. Another drawback to using this interface for returning data is that little of the result set is held in memory after the query runs. You can scroll the command window up to view some text, but this is very limited.
The EXIT command is used to leave SQLCMD and return to a command prompt. Type EXIT again to close the command prompt window.

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

T-SQL Topics