9.5 sqlcmd Utility

9.5 sqlcmd Utility

9.5.1 Installing sqlcmd

The sqlcmd utility is an application that runs at a Windows command-line prompt to start and stop instances of SQL Server, and to interactively execute T-SQL statements and scripts.

The sqlcmd utility is installed at the same time as an instance of SQL Server. For SQL Server 2014,sqlcmd.exe files, the C:\Program Files\Microsoft SQL Server\client Sdk\odbc\110\tools\binn folder is located by default. For SQL Server 2012 and earlier products, the location of the SQLCMD.exe file differs, for example: C:\Program Files\Microsoft SQL Server\100\tools\binn folder.

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/6C/18/wKiom1U_a1KTyjp-AAHug_m6WBw606.jpg "title=" folder. png "alt=" wkiom1u_a1ktyjp-aahug_m6wbw606.jpg "/>

9.5.2 querying the Database engine list

sqlcmd through the "-l" option, you can query the database engine services that are already installed on the local computer, regardless of whether the services for those instances have started, but you need to start the SQL Server Browser service to run this query. The options for sqlcmd are case-sensitive.

The following example is the result of running this query on a computer.

C:\> sqlcmd-l Server: PC2014 Pc2014\sql2008r2 pc2014\sql2012

9.5.3 connecting to an instance of SQL Server

SQLCMD uses the following options to connect to an instance of SQL Server.

The "-S" option specifies the instance of SQL Server to connect to. The format is:-S [Protocol:]server[\instance_name][,port]. If you do not specify a server, SQLCMD connects to the default instance on the local computer by default. The protocol can be TCP (TCP/IP), LPC (shared memory), or NP (Named pipes).

If you do not specify the credentials that are used to connect to the instance of SQL Server, the "-e" option is used by default to authenticate with the current Windows credentials (that is, the Windows account of the user running sqlcmd). If you need to specify credentials, you can use the "-u" option to specify the user name, and use the "-P" option to specify the password. Once the "-e" option is used, any "-u" option or the "-P" option cannot be used, otherwise an error will be made.

In the following example, SQLCMD uses the current Windows credentials to connect to the SQL2008R2 named instance of the PC2014 computer.

C:\> sqlcmd-s pc2014\sql2008r2

9.5.4 opening the DAC

If SQL Server is configured to accept remote management connections, you must start the DAC with an explicit port number. The format is:

C:\> sqlcmd–stcp:<server>,<port>

The SQL Server error log lists the port number of the DAC, which by default is 1434. If you configured SQL Server to accept only local DAC connections, use the following command and the loopback adapter (IP address is 127.0.0.1) to connect.

C:\> Sqlcmd–s 127.0.0.1,1434

In the following example, Sqlcmd opens the DAC with a "-A" option and connects to the master database by default.

C:\> sqlcmd-s 127.0.0.1-a-D Master

9.5.5 using OSQL.exe

The OSQL.exe file for SQL Server 2014 is located in the C:\Program Files\Microsoft SQL Server\120\tools\binn folder. (For SQL Server 2012 and earlier versions, OSQL.exe and SQLCMD.exe are located in this folder.) ）

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/6C/14/wKioL1U_ck7zyO0qAAIMPG8TOb0292.jpg "title=" Osql.png "alt=" Wkiol1u_ck7zyo0qaaimpg8tob0292.jpg "/>

This is an application that communicates with an instance of SQL Server through ODBC. The use of the OSQL utility is essentially the same as sqlcmd. This tool is reserved for compatibility with legacy products.

This article from "SQLServer2014 series" blog, declined reprint!

9.5 sqlcmd Utility