SQLCMD - SQL Server 2008

SQLCMD, pronounced “SQL command,” is a command-line tool used to connect to SQL Server and submit T-SQL queries and commands. With SQLCMD, you can perform the following:

  • Execute SQL scripts against any SQL Server.
  • Define and pass variables from the command line as well as within scripts.
  • Use predefined system variables.
  • Include multiple SQL scripts in-line.
  • Dynamically change connections within the same script.
  • Connect to SQL Server via the dedicated administrator connection.

SQLCMD was designed as a replacement to the existing OSQL and ISQL utilities. Of these two older utilities, only OSQL stills ships with SQL Server 2008; however, it has been deprecated since SQL Server 2005. Switching to SQLCMD from these older tools is definitely worth your time. The SQLCMD code was written from scratch, and a lot of effort was put into performance and features that promote usability, such as the ability to pass variables. SQLCMD also supports datatypes like nvarchar(max) and XML.

SQLCMD is designed so that the user can simply swap calls to OSQL.EXE with SQLCMD.EXE and have the script work without any modifications.

Connecting to SQL Server

Unlike OSQL and ISQL, which used ODBC to connect to SQL Server, SQLCMD uses an OLE DB connection and allows users to make multiple connections to different servers within the same script. For example, suppose you had a few simple backup database scripts that each backed up a database on a specific server. On SERVERONE, the administrator would run this backup script to back up the ReportServer database:

File: backup_ReportServer.sql
BACKUP DATABASE [ReportServer] TO DISK='C:backupsReportServer.bak'

On SERVERTWO, the administrator would run this backup script to back up the Products database:

File: backup_Products.sql
BACKUP DATABASE [Products] TO DISK='D:SQLServerBackupsProducts.bak'

In the real world, we know that administrators tend to have a lot of scripts that each performs its own functions on a specific server. With SQLCMD, you can now consolidate these into a single script using the :CONNECT command. Let’s see this same scenario of backing up multiple databases using a single script:

File: backup_databases.sql
--Make a connection to SERVERONE using Windows Authentication
:CONNECT SERVERONE –E
--Issue a backup database command for ReportServer
BACKUP DATABASE [ReportServer] TO DISK='C:backupsReportServer.bak'
GO
--Make a connection to SERVERTWO using Windows Authentication
:CONNECT SERVERTWO –E
--Issue a backup database command for Products database
BACKUP DATABASE [Products] TO DISK='D:SQLServerBackupsProducts.bak'
GO

Issuing the SQLCMD command sqlcmd -E -i backup_databases.sql yields the following result:

Passing Variables

SQLCMD also provides the ability to pass variables from the command line and within the script itself. For example, assume you have a generic “backup database” script, called backup_database_generic.sql, that could be reused:

File: backup_database_generic.sql
:CONNECT $(myConnection)
BACKUP DATABASE $(myDatabase) TO DISK='C:backups$(myDatabase).bak'

At this point, you could call this script from the command line using the new -v parameter. This parameter tells SQLCMD that the following text is a variable, an example of which is shown here:

C:>SQLCMD –E –i backup_database_generic.sql
–v myConnection="." myDatabase="ReportServer"

When the backup_database_generic.sql script is run, it will have two variables defined: myConnection, which is equal to ".", and myDatabase, which is equal to "ReportServer". Alternatively, if you wanted to use variables, you also could have set the parameters within another script:

File: backup_database_main.sql
:SETVAR myConnection .
:SETVAR myDatabase ReportServer
:R "backup_database_generic.sql"
GO

When this script is executed, SQLCMD will set the myConnection variable to "." (the period is an alias for the local server—you could have used "localhost" or the actual name of the server as well), set the myDatabase variable to "ReportServer", and then insert the contents of the backup_database_generic.sql script in-line.

Using the Dedicated Administrator Connection

SQL Server spawns a separate thread called the dedicated administrator connection (DAC). This connection was designed to be used by members of the sysadmin role in the event that they can’t connect to SQL Server under normal operating conditions. There is only one connection of this type allowed per instance of SQL Server available, and it can be accessed only through SQLCMD. To connect to SQL Server 2005 on the DAC, use the -A parameter in SQLCMD.

C:>SQLCMD –E –S. -A

Creating Scripts

Although some might claim Notepad is the best text editor around, writing scripts for SQLCMD can sometimes be tedious using even this robust text editor application. For this reason, SQL Server Management Studio supports writing SQLCMD scripts.

To create a new SQL Script, open Management Studio, connect to your server, and click the New Query button. On the context menu, select New SQL Server Query. You’ll now have a Query Editor window open. If you start typing SQLCMD commands in this window, you’ll notice that the editor will complain, and you’ll get errors if you try to execute the script.

SQL Server Management Studio not in SQLCMD mode

SQL Server Management Studio not in SQLCMD mode

These errors occur because you first need to enable SQLCMD mode in the editor. You can enable this mode by selecting SQLCMD mode from the Query menu in Management Studio. When the editor is in SQLCMD mode, you’ll notice that SQLCMD-specific commands such as :SETVAR and :CONNECT are highlighted in the editor. This is to differentiate them from traditional T-SQL.

SQL Server Management Studio in SQLCMD mode

SQL Server Management Studio in SQLCMD mode

Once the SQLCMD mode is enabled on the editor, the script can be executed by clicking the Execute button. This will pass the script to the SQLCMD application to be interpreted instead of submitting it to the SQL Server database engine directly.

SQLCMD is a complete rewrite of the deprecated OSQL command-prompt utility. It is designed with many performance-related improvements.


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

SQL Server 2008 Topics