Using the SQLCMD in SQL 2005 Server

Introduction

SQLCMD is the preferred command-line utility in SQL 2005 Server although the OSQL utility can be used side by side with SQLCMD. OSQL is included for backward compatibility to deal with legacy scripts. ISQL has been discontinued.

SQLCMD can be run both in the DOS screen as well as in the MS SQL Server 2005 Management Studio's query editor in the SQLCMD mode. If you want to use scripting, which brings in features like color coding, parsing and executing scripts, and others, this must be enabled. Enabling SQLCMD allows you to write SQL Commands as well as T-SQL Statements. SQLCMD can be enabled in the query editor as shown in this picture,

or by going to tools it can be enabled by default as shown in the next picture.

One of the key advantages of this new command-line utility is its ability to set environmental variables and effectively use them in scripts and send output in XML if needed. This article does not on touch on all these interesting features and myriad other possibilities, but helps you in getting up and running. There are a number of rules that need to be observed in writing commands, but these will not be discussed either, and the reader is directed to BOL (Book On Line).

One of the requirements for running the SQLCMD from the command line (in a DOS screen) is that the client and the server should be listening on/to the same TCP Port. If they are not, then the command line usage of SQLCMD results in an error as shown. While you do get an error message it is not very helpful. The only clue you get is the two words "TCP provider," if you can correctly guess. I wonder why they did not add another paragraph to this message.

C:Program FilesMicrosoft SQL Server90Shared>sqlcmd
HResult 0x274D, Level 16, State 1
TCP Provider: No connection could be made because the target machine
actively refused it.
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while
establishing a connection to the server. When connecting to SQL Server
2005, this failure may be caused by the fact that under the default
settings SQL Server does not allow remote connections. 
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

How do we correct the above condition? 

Open the SQL Configuration Manager by going through All Programs -> MS SQL Server 2005 -> Configuration Tools -> SQL Server Configuration Manager and click as shown.

This opens up the SQL Server Configuration Manager as shown. This screen shows the various SQL Server 2005 services, the SQL 2005 Network Configuration and the SQL 2005 Client Configuration in a tree view. You may expand each of these to view the constituent parts for the nodes.

In this picture the SQL Server 2005 Network configuration has been expanded to show the various protocols that are enabled. One of them is the TCP protocol. Right click the TCP protocol in the list view (right pane) and look at its properties. It opens as shown in the window that is superposed with the title TCP/IP Properties with the default tab, Protocol in view. Here you can enable and disable the protocol and make other desired changes.

Clicking on the tab labeled IP Addresses opens up the window shown in the next picture. You see here that the TCP Dynamic Ports is 1037. With the port configured to use dynamic ports, when the instance of SQL Server starts up it takes an available end point for the port given by the operating system. The SQL Server Browser connects the incoming client to that port.

SQL Client Connections

Close out of the SQL Server Network Connections, click on the SQL Client Configuration and expand the node to reveal the TCP node in the right pane. Right click on the TCP node to reveal the properties as shown in the next picture. You will see that the port it is listening to is different; it is 1433. Change this to 1037. Click on the button Apply and then click Ok. Close out of the configuration tool.

Did it fix the problem?

Go back to the DOS screen and run the SQLCMD command again. You will see that you are rocking now. Both OSQL and SQLCMD will work, the former implemented for backward compatibility with legacy scripts. They in no way interfere with one another. When SQLCMD is invoked from the command line it accesses the server via OleDB, whereas when it is invoked in the SQL Server Management Studio, it uses the .NET SQL Client.

C:Program FilesMicrosoft SQL Server90Shared>sqlcmd
1> exit
C:Program FilesMicrosoft SQL Server90Shared>osql
Error: No user selected. Try with -U or -E switches
C:Program FilesMicrosoft SQL Server90Shared>osql -E
1>

SQLCMD command line arguments

You can easily get a listing of the command-line arguments by typing in:

C:sqlcmd -?

as shown below:

C:Documents and Settings>sqlcmd -?
Microsoft (R) SQL Server Command Line Tool
Version 9.00.1399.06 NT INTEL X86
Copyright (c) Microsoft Corporation. All rights reserved.
usage: Sqlcmd           [-U login id]       [-P password]
[-S server]            [-H hostname]       [-E trusted connection]
[-d use database name] [-l login timeout]  [-t query timeout]
[-h headers]           [-s colseparator]   [-w screen width]
[-a packetsize]        [-e echo input]     [-I Enable Quoted Identifiers]
[-c cmdend]            [-L[c] list servers[clean output]]
[-q "cmdline query"]   [-Q "cmdline query" and exit]
[-m errorlevel]        [-V severitylevel]  [-W remove trailing spaces]
[-u unicode output]    [-r[0|1] msgs to stderr]
[-i inputfile]         [-o outputfile]     [-z new password]
[-f
| i:
[,o:
]] [-Z new password and exit]
[-k[1|2] remove[replace] control characters]
[-y variable length type display width]
[-Y fixed length type display width]
[-p[1] print statistics[colon format]]
[-R use client regional setting]
[-b On error batch abort]
[-v var = "value"...]  [-A dedicated admin connection]
[-X[1] disable commands, startup script, environment variables [and exit]]
[-x disable variable substitution]
[-? show syntax summary]

Here is an example of connecting to a database on the server and executing a query interactively. A number of statements are stored in a statement cache and then sent to the server when it reaches a "Go" command. The cache can be emptied by the RESET command.

Summary

The material presented in this tutorial is primarily to help in starting up the utility and looking at some of its very basic features. It does not describe all the features of this utility and the reader is directed to the book on line which is very exhaustive. The troubleshooting described should help if you get a cryptic message about connection problems while trying to run this utility.