sqlcmd Utilitysqlcmd Utility

本主题适用于: 是SQL Server是Azure SQL 数据库是Azure SQL 数据仓库是并行数据仓库THIS TOPIC APPLIES TO: yesSQL ServeryesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

SQL Server 2014 和更低,请参阅sqlcmd 实用工具For SQL Server 2014 and lower, see sqlcmd Utility.

Sqlcmd实用工具,可以在输入 TRANSACT-SQL 语句、 系统过程和脚本文件在命令提示符下,查询编辑器在 SQLCMD 模式下,Windows 脚本文件或 SQL Server 代理作业的操作系统 (Cmd.exe) 作业步骤。The sqlcmd utility lets you enter Transact-SQL statements, system procedures, and script files at the command prompt, in Query Editor in SQLCMD mode, in a Windows script file or in an operating system (Cmd.exe) job step of a SQL Server Agent job. 此实用工具使用 ODBC 执行 TRANSACT-SQL 批处理。This utility uses ODBC to execute Transact-SQL batches.

备注

Sqlcmd 实用工具的最新版本可作为 Web 版本从 下载中心获取。The most recent versions of the sqlcmd utility is available as a web release from the Download Center. 你需要版本 13.1 或更高版本以支持始终加密 (-g) 和 Azure Active Directory 身份验证 (-G)。You need version 13.1 or higher to support Always Encrypted (-g) and Azure Active Directory authentication (-G). (你的计算机上可能已安装多个版本的 sqlcmd.exe。(You may have several versions of sqlcmd.exe installed on your computer. 请确保使用正确的版本。Be sure you are using the correct version. 若要确定版本,请执行 sqlcmd -?。)To determine the version, execute sqlcmd -?.)

你可以尝试从 Azure 云 Shell sqlcmd 实用工具,因为默认情况下预安装: 启动云 ShellYou can try the sqlcmd utility from Azure Cloud Shell as it is pre-installed by default: Launch Cloud Shell

若要在 SSMS 中运行 sqlcmd 语句,请从顶部导航栏上的“查询菜单”下拉列表中选择“SQLCMD 模式”。To run sqlcmd statements in SSMS, select SQLCMD Mode from the top navigation Query Menu dropdown.

重要

SQL Server Management StudioSQL Server Management Studio(SSMS) 使用 Microsoft .NET Framework.NET Framework用于执行的常规和中的 SQLCMD 模式下的 SqlClient查询编辑器 (SSMS) uses the Microsoft .NET Framework.NET Framework SqlClient for execution in regular and SQLCMD mode in Query Editor. 从命令行运行 sqlcmd 时, sqlcmd 使用 ODBC 驱动程序。When sqlcmd is run from the command line, sqlcmd uses the ODBC driver. 由于可以应用不同的默认选项,因此在 SQL Server Management StudioSQL Server Management Studio SQLCMD 模式下以及在 sqlcmd 实用工具中执行相同的查询时,可能会看到不同的行为。Because different default options may apply, you might see different behavior when you execute the same query in SQL Server Management StudioSQL Server Management Studio in SQLCMD Mode and in the sqlcmd utility.

当前, sqlcmd 在命令行选项和值之间不需要空格。Currently, sqlcmd does not require a space between the command line option and the value. 但是,在将来的版本中,在命令行选项和值之间可能需要空格。However, in a future release, a space may be required between the command line option and the value.

其他主题:Other topics:

语法Syntax

sqlcmd   
   -a packet_size  
   -A (dedicated administrator connection)  
   -b (terminate batch job if there is an error)  
   -c batch_terminator  
   -C (trust the server certificate)  
   -d db_name  
   -e (echo input)  
   -E (use trusted connection)  
   -f codepage | i:codepage[,o:codepage] | o:codepage[,i:codepage] 
   -g (enable column encryption) 
   -G (use Azure Active Directory for authentication)
   -h rows_per_header  
   -H workstation_name  
   -i input_file  
   -I (enable quoted identifiers)  
   -j (Print raw error messages)
   -k[1 | 2] (remove or replace control characters)  
   -K application_intent  
   -l login_timeout  
   -L[c] (list servers, optional clean output)  
   -m error_level  
   -M multisubnet_failover  
   -N (encrypt connection)  
   -o output_file  
   -p[1] (print statistics, optional colon format)  
   -P password  
   -q "cmdline query"  
   -Q "cmdline query" (and exit)  
   -r[0 | 1] (msgs to stderr)  
   -R (use client regional settings)  
   -s col_separator  
   -S [protocol:]server[instance_name][,port]  
   -t query_timeout  
   -u (unicode output file)  
   -U login_id  
   -v var = "value"  
   -V error_severity_level  
   -w column_width  
   -W (remove trailing spaces)  
   -x (disable variable substitution)  
   -X[1] (disable commands, startup script, environment variables, optional exit)  
   -y variable_length_type_display_width  
   -Y fixed_length_type_display_width  
   -z new_password   
   -Z new_password (and exit)  
   -? (usage)  

命令行选项Command-line Options

登录相关选项Login-Related Options
-A-A
到与专用的管理员连接 (DAC) 的 SQL Server 中的日志。Logs in to SQL Server with a Dedicated Administrator Connection (DAC). 此类型连接用于排除服务器故障。This kind of connection is used to troubleshoot a server. 这只适用于支持 DAC 的服务器。This will only work with server computers that support DAC. 如果 DAC 不可用, sqlcmd 会生成错误消息,然后退出。If DAC is not available, sqlcmd generates an error message and then exits. 有关 DAC 的详细信息,请参阅用于数据库管理员的诊断连接For more information about DAC, see Diagnostic Connection for Database Administrators. -A 选项不支持使用-G 选项。The -A option is not supported with the -G option. 时连接到 SQL 数据库使用-A,你必须是 SQL server 管理员联系。When connecting to SQL Database using -A, you must be a SQL server administrator. DAC 不可用的 Azure Active Directory 管理员。The DAC is not availble for an Azure Active Directory adminstrator.

-C-C
该开关供客户端用于将其配置为隐式表示信任服务器证书且无需验证。This switch is used by the client to configure it to implicitly trust the server certificate without validation. 此选项等同于 ADO.NET 选项 TRUSTSERVERCERTIFICATE = trueThis option is equivalent to the ADO.NET option TRUSTSERVERCERTIFICATE = true.

-d db_name-d db_name
启动 sqlcmd 时发出一个 USE db_name 语句。Issues a USE db_name statement when you start sqlcmd. 此选项设置 sqlcmd 脚本变量 SQLCMDDBNAME。This option sets the sqlcmd scripting variable SQLCMDDBNAME. 它指定初始数据库。This specifies the initial database. 默认为您的登录名的默认数据库属性。The default is your login's default-database property. 如果数据库不存在,则生成错误消息且 sqlcmd 退出。If the database does not exist, an error message is generated and sqlcmd exits.

-l login_timeout-l login_timeout
指定在你尝试连接到服务器时 sqlcmd 登录 ODBC 驱动程序的超时时间(以秒为单位)。Specifies the number of seconds before a sqlcmd login to the ODBC driver times out when you try to connect to a server. 此选项设置 sqlcmd 脚本变量 SQLCMDLOGINTIMEOUT。This option sets the sqlcmd scripting variable SQLCMDLOGINTIMEOUT. 登录到 sqlcmd 的默认超时时间为 8 秒。The default time-out for login to sqlcmd is eight seconds. 当使用 -G 选项连接到 SQL 数据库或 SQL 数据仓库并使用 Azure Active Directory 进行身份验证时,建议超时值至少为 30 秒。When using the -G option to connect to SQL Database or SQL Data Warehouse and authenticate using Azure Active Directory, a timeout value of at least 30 seconds is recommended. 登录超时必须是介于 0 和 65534 之间的数字。The login time-out must be a number between 0 and 65534. 如果提供的值不是数值或不在此范围内, sqlcmd 将生成错误消息。If the value supplied is not numeric or does not fall into that range, sqlcmd generates an error message. 该值为 0 时,则允许无限制等待。A value of 0 specifies time-out to be infinite.

-E-E
使用可信的连接而不是使用用户名和密码登录到 SQL Server。Uses a trusted connection instead of using a user name and password to log on to SQL Server . 默认情况下,如果未指定 -Esqlcmd 将使用信任连接选项。By default, without -E specified, sqlcmd uses the trusted connection option.

-E 选项会忽略可能的用户名和密码环境变量设置,例如 SQLCMDPASSWORD。The -E option ignores possible user name and password environment variable settings such as SQLCMDPASSWORD. 如果将 -E 选项与 -U 选项或 -P 选项一起使用,将生成错误消息。If the -E option is used together with the -U option or the -P option, an error message is generated.

-g-g
将列加密设置设为 EnabledSets the Column Encryption Setting to Enabled. 有关详细信息,请参阅 Always EncryptedFor more information, see Always Encrypted. 仅支持存储在 Windows 证书存储中的主密钥。Only master keys stored in Windows Certificate Store are supported. -g 开关至少需要 sqlcmd 版本 13.1The -g switch requires at least sqlcmd version 13.1. 若要确定你的版本,请执行 sqlcmd -?To determine your version, execute sqlcmd -?.

-G-G
当连接到 SQL 数据库或 SQL 数据仓库时,客户端将使用此开关指定该用户使用 Azure Active Directory 身份验证来进行身份验证。This switch is used by the client when connecting to SQL Database or SQL Data Warehouse to specify that the user be authenticated using Azure Active Directory authentication. 此选项设置 sqlcmd 脚本变量 SQLCMDUSEAAD = true。This option sets the sqlcmd scripting variable SQLCMDUSEAAD = true. -G 开关至少需要 sqlcmd 版本 13.1The -G switch requires at least sqlcmd version 13.1. 若要确定你的版本,请执行 sqlcmd -?To determine your version, execute sqlcmd -?. 有关详细信息,请参阅 使用 Azure Active Directory 身份验证连接到 SQL 数据库或 SQL 数据仓库For more information, see Connecting to SQL Database or SQL Data Warehouse By Using Azure Active Directory Authentication. -A 选项不支持使用-G 选项。The -A option is not supported with the -G option.

重要

-G 选项仅适用于 Azure SQL 数据库 和 Azure 数据仓库。The -G option only applies to Azure SQL Database and Azure Data Warehouse.

  • Azure Active Directory 用户名和密码:Azure Active Directory Username and Password:

    当你想要使用 Azure Active Directory 用户名和密码时,可以提供 -G 选项,也可以通过提供 -U 选项和 -P 选项来使用用户名和密码。When you want to use an Azure Active Directory user name and password, you can provide the -G option and also use the user name and password by providing the -U and -P options.

    Sqlcmd -S testsrv.database.windows.net -d Target_DB_or_DW -U bob@contoso.com -P MyAADPassword -G 
    

    这将在后端生成以下连接字符串:This will generate the following connection string in the backend:

     SERVER = Target_DB_or_DW.testsrv.database.windows.net;UID= bob@contoso.com;PWD=MyAADPassword;AUTHENTICATION = ActiveDirectoryPassword 
    
  • Azure Active Directory 集成Azure Active Directory Integrated

    要进行 Azure Active Directory 集成身份验证,可提供 -G 选项而无需用户名或密码:For Azure Active Directory Integrated authentication, provide the -G option without a user name or password:

    Sqlcmd -S Target_DB_or_DW.testsrv.database.windows.net  -G
    

    这将在后端生成以下连接字符串:This will generate the following connection string in the backend:

    SERVER = Target_DB_or_DW.testsrv.database.windows.net Authentication = ActiveDirectoryIntegrated; Trusted_Connection=NO
    

    备注

    -E 选项 (Trusted_Connection) 不能与 -G 选项一起使用。The -E option (Trusted_Connection) cannot be used along with the -G option).

-H workstation_name-H workstation_name
工作站的名称。A workstation name. 此选项设置 sqlcmd 脚本变量 SQLCMDWORKSTATION。This option sets the sqlcmd scripting variable SQLCMDWORKSTATION. 工作站名称列出在 sys.sysprocesses 目录视图的 hostname 列中,并且可使用存储过程 sp_who返回。The workstation name is listed in the hostname column of the sys.sysprocesses catalog view and can be returned using the stored procedure sp_who. 如果不指定此选项,则默认为当前计算机名称。If this option is not specified, the default is the current computer name. 此名称可用来标识不同的 sqlcmd 会话。This name can be used to identify different sqlcmd sessions.

-j 将原始错误消息输出到银幕上。-j Prints raw error messages to the screen.

-K application_intent-K application_intent
连接到服务器时声明应用程序工作负荷类型。Declares the application workload type when connecting to a server. 目前唯一支持的值是 ReadOnlyThe only currently supported value is ReadOnly. 如果未指定 -K ,sqlcmd 实用工具将不支持连接到 AlwaysOn 可用性组中的次要副本。If -K is not specified, the sqlcmd utility will not support connectivity to a secondary replica in an Always On availability group. 有关详细信息,请参阅活动次要副本:可读次要副本(AlwaysOn 可用性组)For more information, see Active Secondaries: Readable Secondary Replica (Always On Availability Groups)

-M multisubnet_failover-M multisubnet_failover
始终指定-M连接到 SQL Server 可用性组或 SQL Server 故障转移群集实例的可用性组侦听器时。Always specify -M when connecting to the availability group listener of a SQL Server availability group or a SQL Server Failover Cluster Instance. -M 将为(当前)活动服务器提供更快的检测和连接。-M provides for faster detection of and connection to the (currently) active server. 如果不指定 –M ,则 -M 处于关闭状态。If –M is not specified, -M is off. 有关详细信息 [!包括ssHADR创建和配置可用性组 (SQL server),[故障转移群集和 Alwayson 可用性组 (SQL Server)] (https://msdn.microsoft.comlibrary/ff929171.aspx,和 [活动辅助副本: 可读辅助副本 (Always On 可用性组)](https://msdn.microsoft.com/library/ff878253.aspx.For more information about [!INCLUDEssHADR, Creation and Configuration of Availability Groups (SQL Server), [Failover Clustering and Always On Availability Groups (SQL Server)](https://msdn.microsoft.comlibrary/ff929171.aspx, and [Active Secondaries: Readable Secondary Replicas(Always On Availability Groups)](https://msdn.microsoft.com/library/ff878253.aspx.

-N-N
此开关供客户端用于请求加密连接。This switch is used by the client to request an encrypted connection.

-P password-P password
用户指定的密码。Is a user-specified password. 密码是区分大小写的。Passwords are case sensitive. 如果使用了 -U 选项而未使用 -P 选项,并且未设置 SQLCMDPASSWORD 环境变量,则 sqlcmd 会提示用户输入密码。If the -U option is used and the -P option is not used, and the SQLCMDPASSWORD environment variable has not been set, sqlcmd prompts the user for a password. 若要指定空密码(不推荐),请使用 -P ""To specify a null password (not recommended) use -P "". 请记住始终:And remember to always:

使用强密码!!Use a strong password!!

通过向控制台输出密码提示,可以显示密码提示,如下所示: Password:The password prompt is displayed by printing the password prompt to the console, as follows: Password:

隐藏用户输入。User input is hidden. 也就是说,将不会显示任何输入的内容,光标保留原位不动。This means that nothing is displayed and the cursor stays in position.

使用 SQLCMDPASSWORD 环境变量可以为当前会话设置默认密码。The SQLCMDPASSWORD environment variable lets you set a default password for the current session. 因此,不必将密码硬编码到批处理文件中。Therefore, passwords do not have to be hard-coded into batch files.

以下示例首先在命令提示符处设置 SQLCMDPASSWORD 变量,然后访问 sqlcmd 实用工具。The following example first sets the SQLCMDPASSWORD variable at the command prompt and then accesses the sqlcmd utility. 在命令提示符下,键入:At the command prompt, type:

SET SQLCMDPASSWORD= p@a$$w0rd
在以下命令提示符处键入:At the following command prompt, type:

sqlcmd

如果用户名和密码组合不正确,将生成错误消息。If the user name and password combination is incorrect, an error message is generated.

注意!NOTE! 为实现向后兼容性而保留了 OSQLPASSWORD 环境变量。The OSQLPASSWORD environment variable was kept for backward compatibility. SQLCMDPASSWORD 环境变量优先于 OSQLPASSWORD 环境变量;也就是说 sqlcmdosql 可以彼此相邻使用而不会相互干扰,并且旧式脚本可以继续使用。The SQLCMDPASSWORD environment variable takes precedence over the OSQLPASSWORD environment variable; this means that sqlcmd and osql can be used next to each other without interference and that old scripts will continue to work.

如果将 -P 选项与 -E 选项一起使用,将生成错误消息。If the -P option is used with the -E option, an error message is generated.

如果 -P 选项后有多个参数,将生成错误消息并退出程序。If the -P option is followed by more than one argument, an error message is generated and the program exits.

-S [protocol:]server[\instance_name][,port]-S [protocol:]server[\instance_name][,port]
指定要连接到 SQL Server 的实例。Specifies the instance of SQL Server to which to connect. 它设置 sqlcmd 脚本变量 SQLCMDSERVER。It sets the sqlcmd scripting variable SQLCMDSERVER.

指定server_name连接到 SQL Server 在该服务器计算机上的默认实例。Specify server_name to connect to the default instance of SQL Server on that server computer. 指定server_name [\* * * instance_name* ] 若要连接到该服务器计算机上的 SQL Server 的命名实例。Specify server_name [ **\instance_name ] to connect to a named instance of SQL Server on that server computer. 如果指定服务器的计算机,则sqlcmd连接到本地计算机上 SQL Server 的默认实例。If no server computer is specified, sqlcmd connects to the default instance of SQL Server on the local computer. 从网络上的远程计算机执行 sqlcmd 时,此选项是必需的。This option is required when you execute sqlcmd from a remote computer on the network.

protocol 可以是 tcp (TCP/IP)、 lpc (共享内存)或 np (命名管道)。protocol can be tcp (TCP/IP), lpc (shared memory), or np (named pipes).

如果不指定server_name [\* * * instance_name* ] 当启动sqlcmd,SQL Server 检查并使用 SQLCMDSERVER 环境变量。If you do not specify a server_name [ **\instance_name ] when you start sqlcmd, SQL Server checks for and uses the SQLCMDSERVER environment variable.

备注

为实现向后兼容性而保留了 OSQLSERVER 环境变量。The OSQLSERVER environment variable has been kept for backward compatibility. SQLCMDSERVER 环境变量优先于 OSQLSERVER 环境变量;也就是说 sqlcmdosql 可以彼此相邻使用而不会相互干扰,并且旧式脚本可以继续使用。The SQLCMDSERVER environment variable takes precedence over the OSQLSERVER environment variable; this means that sqlcmd and osql can be used next to each other without interference and that old scripts will continue to work.

-U login_id-U login_id
登录名或包含的数据库用户名。Is the login name or contained database user name. 对于包含的数据库用户,必须提供数据库名称选项 (-d)。For contained database users you must provide the database name option (-d).

备注

OSQLUSER 环境变量可用于实现向后兼容性。The OSQLUSER environment variable is available for backward compatibility. SQLCMDUSER 环境变量优先于 OSQLUSER 环境变量。The SQLCMDUSER environment variable takes precedence over the OSQLUSER environment variable. 也就是说, sqlcmdosql 可以彼此相邻使用而不会相互干扰。This means that sqlcmd and osql can be used next to each other without interference. 此外,现有的 osql 脚本可以继续使用。It also means that existing osql scripts will continue to work.

如果既没有-U选项也不-P指定选项,则sqlcmd尝试通过使用 Microsoft Windows 身份验证模式连接。If neither the -U option nor the -P option is specified, sqlcmd tries to connect by using Microsoft Windows Authentication mode. 身份验证基于运行 sqlcmd的用户的 Windows 帐户。Authentication is based on the Windows account of the user who is running sqlcmd.

如果 -U 选项与 -E 选项(将在本主题的后面进行说明)一起使用,则会生成错误消息。If the -U option is used with the -E option (described later in this topic), an error message is generated. 如果 –U 选项后有多个参数,将生成错误消息并退出程序。If the –U option is followed by more than one argument, an error message is generated and the program exits.

-z new_password-z new_password
更改密码:Change password:

sqlcmd -U someuser -P s0mep@ssword -z a_new_p@a$$w0rd

-Z new_password-Z new_password
更改密码并退出:Change password and exit:

sqlcmd -U someuser -P s0mep@ssword -Z a_new_p@a$$w0rd

输入/输出选项Input/Output Options
-f codepage | i:codepage[,o:codepage] | o:codepage[,i:codepage]-f codepage | i:codepage[,o:codepage] | o:codepage[,i:codepage]
指定输入和输出代码页。Specifies the input and output code pages. 代码页页码是指定已安装的 Windows 代码页的数值。The codepage number is a numeric value that specifies an installed Windows code page.

代码页转换规则:Code-page conversion rules:

  • 如果未指定代码页, sqlcmd 会将当前代码页同时用于输入文件和输出文件,除非输入文件为 Unicode 文件,在此情况下无需进行转换。If no code pages are specified, sqlcmd will use the current code page for both input and output files, unless the input file is a Unicode file, in which case no conversion is required.

  • sqlcmd 自动识别 Big-endian Unicode 和 Little-endian Unicode 输入文件。sqlcmd automatically recognizes both big-endian and little-endian Unicode input files. 如果已指定 -u 选项,输出将始终为 Little-endian Unicode。If the -u option has been specified, the output will always be little-endian Unicode.

  • 如果未指定输出文件,输出代码页将为控制台代码页。If no output file is specified, the output code page will be the console code page. 这将使输出正确显示在控制台上。This enables the output to be displayed correctly on the console.

  • 假定多个输入文件具有相同的代码页。Multiple input files are assumed to be of the same code page. 可以将 Unicode 和非 Unicode 输入文件混合在一起。Unicode and non-Unicode input files can be mixed.

    在命令提示符处输入 chcp 以验证 Cmd.exe 的代码页。Enter chcp at the command prompt to verify the code page of Cmd.exe.

    -i input_file[,input_file2...]-i input_file[,input_file2...]
    标识包含一批 SQL 语句或存储过程的文件。Identifies the file that contains a batch of SQL statements or stored procedures. 可以指定要按顺序读取和处理的多个文件。Multiple files may be specified that will be read and processed in order. 文件名之间不要使用任何空格。Do not use any spaces between file names. sqlcmd 将首先检查所有指定的文件是否都存在。sqlcmd will first check to see whether all the specified files exist. 如果有一个或多个文件不存在, sqlcmd 将退出。If one or more files do not exist, sqlcmd will exit. -i 和 -Q/-q 选项是互斥的。The -i and the -Q/-q options are mutually exclusive.

    路径示例:Path examples:

-i C:\<filename>  
-i \\<Server>\<Share$>\<filename>  
-i "C:\Some Folder\<file name>"  

包含空格的文件路径必须用引号引起来。File paths that contain spaces must be enclosed in quotation marks.

可能会多次使用此选项: -i * * * input_file* *-我 * * * 我 input_file。This option may be used more than once: **-iinput_file -II input_file.

-o output_file-o output_file
标识从 sqlcmd接收输出的文件。Identifies the file that receives output from sqlcmd.

如果指定了 -u ,则 output_file 以 Unicode 格式存储。If -u is specified, the output_file is stored in Unicode format. 如果文件名无效,将生成一个错误消息,并且 sqlcmd 将退出。If the file name is not valid, an error message is generated, and sqlcmd exits. sqlcmd 不支持向同一文件并发写入多个 sqlcmd 进程。sqlcmd does not support concurrent writing of multiple sqlcmd processes to the same file. 文件输出将损坏或不正确。The file output will be corrupted or incorrect. 有关文件格式的详细信息,请参阅 -f 开关。See the -f switch for more information about file formats. 如果此文件不存在,将创建此文件。This file will be created if it does not exist. 前一个 sqlcmd 会话中的同名文件将被覆盖。A file of the same name from a prior sqlcmd session will be overwritten. 此处指定的文件不是 stdout 文件。The file specified here is not the stdout file. 如果指定了 stdout 文件,将不使用此文件。If a stdout file is specified this file will not be used.

路径示例:Path examples:

-o C:< filename>  
-o \\<Server>\<Share$>\<filename>  
-o "C:\Some Folder\<file name>"  

包含空格的文件路径必须用引号引起来。File paths that contain spaces must be enclosed in quotation marks.

-r[0 | 1]-r[0 | 1]
将错误消息输出重定向到屏幕 (stderr)。Redirects the error message output to the screen (stderr). 如果未指定参数或指定参数为 0,则仅重定向严重级别为 11 或更高的错误消息。If you do not specify a parameter or if you specify 0, only error messages that have a severity level of 11 or higher are redirected. 如果指定参数为 1,则将重定向所有消息输出(包括 PRINT)。If you specify 1, all error message output including PRINT is redirected. 如果使用 -o,将不起任何作用。Has no effect if you use -o. 默认情况下,消息将发送到 stdoutBy default, messages are sent to stdout.

-R-R
导致sqlcmd本地化数字、 货币、 日期和时间列从 SQL Server 检索基于客户端的区域设置。Causes sqlcmd to localize numeric, currency, date, and time columns retrieved from SQL Server based on the client’s locale. 默认情况下,将使用服务器的区域设置显示这些列。By default, these columns are displayed using the server’s regional settings.

-u-u
指定无论 input_file 为何种格式,都以 Unicode 格式存储 output_fileSpecifies that output_file is stored in Unicode format, regardless of the format of input_file.

查询执行选项Query Execution Options
-e-e
将输入脚本写入标准输出设备 (stdout)。Writes input scripts to the standard output device (stdout).

-I-I
将 SET QUOTED_IDENTIFIER 连接选项设置为 ON。Sets the SET QUOTED_IDENTIFIER connection option to ON. 默认情况下,此选项设置为 OFF。By default, it is set to OFF. 有关详细信息,请参阅 SET QUOTED_IDENTIFIER (Transact-SQL)For more information, see SET QUOTED_IDENTIFIER (Transact-SQL).

-q" cmdline query "-q" cmdline query "
启动 sqlcmd 时执行查询,但是在查询结束运行时不退出 sqlcmdExecutes a query when sqlcmd starts, but does not exit sqlcmd when the query has finished running. 可以执行多个以分号分隔的查询。Multiple-semicolon-delimited queries can be executed. 将查询用引号引起来,如下例所示。Use quotation marks around the query, as shown in the following example.

在命令提示符下,键入:At the command prompt, type:

sqlcmd -d AdventureWorks2012 -q "SELECT FirstName, LastName FROM Person.Person WHERE LastName LIKE 'Whi%';"

sqlcmd -d AdventureWorks2012 -q "SELECT TOP 5 FirstName FROM Person.Person;SELECT TOP 5 LastName FROM Person.Person;"

重要

请不要在查询中使用 GO 终止符。Do not use the GO terminator in the query.

如果在指定此选项的同时还指定了 -bsqlcmd 在遇到错误时将退出。If -b is specified together with this option, sqlcmd exits on error. -b 将在本主题后面部分进行介绍。-b is described later in this topic.

-Q" cmdline query "-Q" cmdline query "
sqlcmd 启动时执行查询,随后立即退出 sqlcmdExecutes a query when sqlcmd starts and then immediately exits sqlcmd. 可以执行多个以分号分隔的查询。Multiple-semicolon-delimited queries can be executed.

将查询用引号引起来,如下例所示。Use quotation marks around the query, as shown in the following example.

在命令提示符下,键入:At the command prompt, type:

sqlcmd -d AdventureWorks2012 -Q "SELECT FirstName, LastName FROM Person.Person WHERE LastName LIKE 'Whi%';"

sqlcmd -d AdventureWorks2012 -Q "SELECT TOP 5 FirstName FROM Person.Person;SELECT TOP 5 LastName FROM Person.Person;"

重要

请不要在查询中使用 GO 终止符。Do not use the GO terminator in the query.

如果在指定此选项的同时还指定了 -bsqlcmd 在遇到错误时将退出。If -b is specified together with this option, sqlcmd exits on error. -b 将在本主题后面部分进行介绍。-b is described later in this topic.

-t query_timeout-t query_timeout
指定命令(或 SQL 语句)超时的时间。此选项设置 sqlcmd 脚本变量 SQLCMDSTATTIMEOUT。Specifies the number of seconds before a command (or SQL statement) times out. This option sets the sqlcmd scripting variable SQLCMDSTATTIMEOUT. 如果未指定 time_out 值,则命令将不会超时。查询 * * time_out必须是介于 1 和 65534 之间的数字。If a time_out value is not specified, the command does not time out. The querytime_out must be a number between 1 and 65534. 如果提供的值不是数值或不在此范围内, sqlcmd 将生成错误消息。If the value supplied is not numeric or does not fall into that range, sqlcmd generates an error message.

备注

实际的超时值可能会与指定的 time_out 值相差几秒。The actual time out value may vary from the specified time_out value by several seconds.

-vvar = value[ var = value...]-vvar = value[ var = value...]
创建可在 sqlcmd脚本中使用的 sqlcmd 脚本变量。Creates a sqlcmdscripting variable that can be used in a sqlcmd script. 如果该值包含空格,则将其用引号引起来。Enclose the value in quotation marks if the value contains spaces. 你可以指定多个var=""值。You can specify multiple var="values" values. 如果指定的任何值中有错误, sqlcmd 会生成错误消息,然后退出。If there are errors in any of the values specified, sqlcmd generates an error message and then exits.

sqlcmd -v MyVar1=something MyVar2="some thing"

sqlcmd -v MyVar1=something -v MyVar2="some thing"

-x-x
导致 sqlcmd 忽略脚本变量。Causes sqlcmd to ignore scripting variables. 当脚本中包含多个 INSERT 语句,并且这些语句可能包含格式与常规变量(例如 $(variable_name))相同的字符串时,这一选项很有用。This is useful when a script contains many INSERT statements that may contain strings that have the same format as regular variables, such as $(variable_name).

格式设置选项Formatting Options
-h headers-h headers
指定要在列标题之间输出的行数。Specifies the number of rows to print between the column headings. 默认为每一组查询结果输出一次标题。The default is to print headings one time for each set of query results. 此选项设置 sqlcmd 脚本变量 SQLCMDHEADERS。This option sets the sqlcmd scripting variable SQLCMDHEADERS. 使用 -1 指定不可输出标题。Use -1 to specify that headers must not be printed. 任何无效的值都将导致 sqlcmd 生成错误消息并随后退出。Any value that is not valid causes sqlcmd to generate an error message and then exit.

-k [1 | 2]-k [1 | 2]
删除输出中的所有控制字符,例如制表符和换行符。Removes all control characters, such as tabs and new line characters from the output. 这会在返回数据时保留列格式。This preserves column formatting when data is returned. 如果指定了 1,则控制字符被一个空格替代。If 1 is specified, the control characters are replaced by a single space. 如果指定了 2,则连续的控制字符被一个空格替代。If 2 is specified, consecutive control characters are replaced by a single space. -k-k1相同。-k is the same as -k1.

-s col_separator-s col_separator
指定列分隔符字符。Specifies the column-separator character. 默认为空格。The default is a blank space. 此选项设置 sqlcmd 脚本变量 SQLCMDCOLSEP。This option sets the sqlcmd scripting variable SQLCMDCOLSEP. 若要使用对操作系统有特殊含义的字符,如“与”符号 (&) 或分号 (;),请将该字符用双引号 (") 引起来。To use characters that have special meaning to the operating system such as the ampersand (&), or semicolon (;), enclose the character in quotation marks ("). 列分隔符可以是任意 8 位字符。The column separator can be any 8-bit character.

-w column_width-w column_width
指定用于输出的屏幕宽度。Specifies the screen width for output. 此选项设置 sqlcmd 脚本变量 SQLCMDCOLWIDTH。This option sets the sqlcmd scripting variable SQLCMDCOLWIDTH. 该列宽必须是介于 8 和 65536 之间的数字。The column width must be a number greater than 8 and less than 65536. 如果指定的列宽不在此范围内,则 sqlcmd 将生成错误消息。If the specified column width does not fall into that range, sqlcmd generates and error message. 默认宽度为 80 个字符。The default width is 80 characters. 在输出行超出指定的列宽时,将转到下一行。When an output line exceeds the specified column width, it wraps on to the next line.

-W-W
此选项删除列的尾随空格。This option removes trailing spaces from a column. 在准备要导出到另一应用程序的数据时,请将此选项和 -s 选项一起使用。Use this option together with the -s option when preparing data that is to be exported to another application. 不能与 -y-Y 选项一起使用。Cannot be used with the -y or -Y options.

-y variable_length_type_display_width-y variable_length_type_display_width
设置 sqlcmd 脚本变量 SQLCMDMAXVARTYPEWIDTHSets the sqlcmd scripting variable SQLCMDMAXVARTYPEWIDTH. 默认值为 256。The default is 256. 它限制为下列大型可变长度数据类型返回的字符的数目:It limits the number of characters that are returned for the large variable length data types:

  • varchar(max)varchar(max)

  • nvarchar(max)nvarchar(max)

  • varbinary(max)varbinary(max)

  • xmlxml

  • UDT(用户定义数据类型)UDT (user-defined data types)

  • texttext

  • ntextntext

  • imageimage

备注

根据实现,UDT 可以使用固定的长度。UDTs can be of fixed length depending on the implementation. 如果此固定长度 UDT 的长度比 display_width短,则返回的 UDT 值将不受影响。If this length of a fixed length UDT is shorter that display_width, the value of the UDT returned is not affected. 但是,如果此长度比 display_width长,则输出会被截断。However, if the length is longer than display_width, the output is truncated.

重要

使用 -y 0 选项时要特别注意,因为根据返回的数据量大小,此选项可能导致服务器和网络上出现严重性能问题。Use the -y 0 option with extreme caution because it may cause serious performance issues on both the server and the network, depending on the size of data returned.

-Y fixed_length_type_display_width-Y fixed_length_type_display_width
设置 sqlcmd 脚本变量 SQLCMDMAXFIXEDTYPEWIDTHSets the sqlcmd scripting variable SQLCMDMAXFIXEDTYPEWIDTH. 默认值为 0(无限制)。The default is 0 (unlimited). 它限制为以下数据类型返回的字符数:Limits the number of characters that are returned for the following data types:

  • char( n ), where 1<=n<=8000char( n ), where 1<=n<=8000

  • nchar(n n ), where 1<=n<=4000nchar(n n ), where 1<=n<=4000

  • varchar(n n ), where 1<=n<=8000varchar(n n ), where 1<=n<=8000

  • nvarchar(n n ), where 1<=n<=4000nvarchar(n n ), where 1<=n<=4000

  • varbinary(n n ), where 1<=n<=4000varbinary(n n ), where 1<=n<=4000

  • 变量variant

    错误报告选项Error Reporting Options
    -b-b
    指定发生错误时, sqlcmd 退出并返回一个 DOS ERRORLEVEL 值。Specifies that sqlcmd exits and returns a DOS ERRORLEVEL value when an error occurs. 返回给 DOS ERRORLEVE 变量的值是1时 SQL Server 错误消息具有严重级别大于 10; 否则,返回的值是0The value that is returned to the DOS ERRORLEVEL variable is 1 when the SQL Server error message has a severity level greater than 10; otherwise, the value returned is 0. 如果除 -b 选项外还设置了 -V选项,则当严重级别低于使用 -V 设置的值时, sqlcmd将不报告错误。If the -V option has been set in addition to -b, sqlcmd will not report an error if the severity level is lower than the values set using -V. 命令提示符批处理文件可以测试 ERRORLEVEL 的值并相应处理错误。Command prompt batch files can test the value of ERRORLEVEL and handle the error appropriately. sqlcmd 不对严重级别 10 报告错误(信息性消息)。sqlcmd does not report errors for severity level 10 (informational messages).

    如果 sqlcmd 脚本包含错误的注释、语法错误或缺少脚本变量,则返回的 ERRORLEVEL 为 1。If the sqlcmd script contains an incorrect comment, syntax error, or is missing a scripting variable, ERRORLEVEL returned is 1.

    -m error_level-m error_level
    控制发送到 stdout的错误消息类型。Controls which error messages are sent to stdout. 将发送严重级别大于或等于此级别的消息。Messages that have a severity level greater than or equal to this level are sent. 如果此值设置为 -1,将发送所有消息(包括信息性消息)。When this value is set to -1, all messages including informational messages, are sent. -m-1之间不允许有空格。Spaces are not allowed between the -m and -1. 例如, -m-1 有效,而 -m-1 无效。For example, -m-1 is valid, and -m-1 is not.

    此选项还设置 sqlcmd 脚本变量 SQLCMDERRORLEVEL。This option also sets the sqlcmd scripting variable SQLCMDERRORLEVEL. 此变量的默认值为 0。This variable has a default of 0.

    -b error_severity_level-V error_severity_level
    控制用于设置 ERRORLEVEL 变量的严重级别。Controls the severity level that is used to set the ERRORLEVEL variable. 严重级别大于或等于此值的错误消息将设置 ERRORLEVEL。Error messages that have severity levels greater than or equal to this value set ERRORLEVEL. 小于 0 的值将报告为 0。Values that are less than 0 are reported as 0. 可以使用批处理文件和 CMD 文件来测试 ERRORLEVEL 变量的值。Batch and CMD files can be used to test the value of the ERRORLEVEL variable.

    其他选项Miscellaneous Options
    -a packet_size-a packet_size
    需要不同大小的数据包。Requests a packet of a different size. 此选项设置 sqlcmd 脚本变量 SQLCMDPACKETSIZE。This option sets the sqlcmd scripting variable SQLCMDPACKETSIZE. packet_size 必须是介于 512 和 32767 之间的值。packet_size must be a value between 512 and 32767. 默认值为 4096。The default = 4096. 如果脚本的两个 GO 命令之间包含大量 SQL 语句,则使用较大的数据包可以提高脚本执行的性能。A larger packet size can enhance performance for execution of scripts that have lots of SQL statements between GO commands. 您可以请求更大的包大小。You can request a larger packet size. 但是,如果请求遭拒绝, sqlcmd 将对包大小使用服务器默认值。However, if the request is denied, sqlcmd uses the server default for packet size.

    -c batch_terminator-c batch_terminator
    指定批处理终止符。Specifies the batch terminator. 默认情况下,命令会终止然后发送到 SQL Server 通过键入 go"在行本身。By default, commands are terminated and sent to SQL Server by typing the word "GO" on a line by itself. 重置批处理终止符时,请不使用 TRANSACT-SQL 保留关键字或对操作系统有特殊含义的字符即使它们的前面有反斜杠。When you reset the batch terminator, do not use Transact-SQL reserved keywords or characters that have special meaning to the operating system, even if they are preceded by a backslash.

    -L[c]-L[c]
    列出本地配置的服务器计算机和在网络上播发的服务器计算机的名称。Lists the locally configured server computers, and the names of the server computers that are broadcasting on the network. 此参数不能与其他参数结合使用。This parameter cannot be used in combination with other parameters. 可以列出的服务器的最大数目是 3000。The maximum number of server computers that can be listed is 3000. 如果服务器列表由于缓冲区大小而被截断,则会显示错误消息。If the server list is truncated because of the size of the buffer a warning message is displayed.

备注

鉴于网络广播的特点, sqlcmd 不可能及时接收来自所有服务器的响应。Because of the nature of broadcasting on networks, sqlcmd may not receive a timely response from all servers. 因此,每次调用该选项所返回的服务器列表都可能不同。Therefore, the list of servers returned may vary for each invocation of this option.

如果指定可选参数 c ,则输出不会显示 Servers: 标题行,并且列出的每个服务器行都没有前导空格。If the optional parameter c is specified, the output appears without the Servers: header line and each server line is listed without leading spaces. 这被称为清除输出。This is referred to as clean output. 清除输出可以提高脚本语言的处理性能。Clean output improves the processing performance of scripting languages.

-p[1]-p[1]
输出每个结果集的性能统计信息。Prints performance statistics for every result set. 以下示例是性能统计信息的格式:The following is an example of the format for performance statistics:

Network packet size (bytes): n

x xact[s]:

Clock Time (ms.): total t1 avg t2 (t3 xacts per sec.)

其中:Where:

x= 由 SQL Server 处理的事务数。x = Number of transactions that are processed by SQL Server .

t1 = 所有事务的总时间。t1 = Total time for all transactions.

t2 = 单个事务的平均时间。t2 = Average time for a single transaction.

t3 = 每秒平均事务数。t3 = Average number of transactions per second.

所有时间均以毫秒表示。All times are in milliseconds.

如果指定了可选参数 1 ,则统计信息的输出格式为以冒号分隔的格式,此格式可以由脚本轻松导入到电子表格中或进行处理。If the optional parameter 1 is specified, the output format of the statistics is in colon-separated format that can be imported easily into a spreadsheet or processed by a script.

如果可选参数是除 1之外的任何值,则将生成错误并且 sqlcmd 将退出。If the optional parameter is any value other than 1, an error is generated and sqlcmd exits.

-X[1]-X[1]
从批处理文件执行 sqlcmd 时,将禁用可能危及系统安全的命令。Disables commands that might compromise system security when sqlcmd is executed from a batch file. 禁用的命令仍然可以被识别; sqlcmd 发出警告消息并继续。The disabled commands are still recognized; sqlcmd issues a warning message and continues. 如果指定了可选参数 1 ,则 sqlcmd 将生成错误消息,然后退出。If the optional parameter 1 is specified, sqlcmd generates an error message and then exits. 使用 -X 选项时,将禁用以下命令:The following commands are disabled when the -X option is used:

  • EDED

  • !!!! commandcommand

    如果指定 -X 选项,则会阻止将环境变量传递给 sqlcmdIf the -X option is specified, it prevents environment variables from being passed on to sqlcmd. 同时该选项还会阻止执行通过使用 SQLCMDINI 脚本变量指定的启动脚本。It also prevents the startup script specified by using the SQLCMDINI scripting variable from being executed. 有关 sqlcmd 脚本变量的详细信息,请参阅 将 sqlcmd 与脚本变量结合使用For more information about sqlcmd scripting variables, see Use sqlcmd with Scripting Variables.

    -?-?
    显示 sqlcmd 的版本和 sqlcmd 选项的语法摘要。Displays the version of sqlcmd and a syntax summary of sqlcmd options.

注释Remarks

不必按语法部分所示的顺序使用选项。Options do not have to be used in the order shown in the syntax section.

在返回多个结果时, sqlcmd 在批处理中的每个结果集之间输出一个空行。When multiple results are returned, sqlcmd prints a blank line between each result set in a batch. 此外,如果没有应用于已执行的语句,则不会出现 <x> rows affected 消息。In addition, the <x> rows affected message does not appear when it does not apply to the statement executed.

若要交互使用 sqlcmd ,请在命令提示符处带本主题前面介绍的一个或多个选项键入 sqlcmdTo use sqlcmd interactively, type sqlcmd at the command prompt with any one or more of the options described earlier in this topic. 有关详细信息,请参阅 使用 sqlcmd 实用工具For more information, see Use the sqlcmd Utility

备注

选项 -L-Q-Z-i 会导致 sqlcmd 在执行后退出。The options -L, -Q, -Z or -i cause sqlcmd to exit after execution.

命令环境 (Cmd.exe) 中的 sqlcmd 命令行的总长度(包括所有参数和扩展变量)取决于 Cmd.exe 所在的操作系统。The total length of the sqlcmd command line in the command environment (Cmd.exe), including all arguments and expanded variables, is that which is determined by the operating system for Cmd.exe.

变量优先级(从低到高)Variable Precedence (Low to High)

  1. 系统级环境变量。System-level environmental variables.

  2. 用户级环境变量User-level environmental variables

  3. 运行sqlcmd 之前在命令提示符处设置的命令 shell ( SETX=Y)。Command shell (SET X=Y) set at command prompt before running sqlcmd.

  4. sqlcmd-v X=Ysqlcmd-v X=Y

  5. :Setvar X Y:Setvar X Y

备注

若要查看环境变量,请在“控制面板” 中打开“系统” ,然后单击“高级” 选项卡。To view the environmental variables, in Control Panel, open System, and then click the Advanced tab.

sqlcmd 脚本变量sqlcmd Scripting Variables

变量Variable 相关开关Related switch R/WR/W 默认Default
SQLCMDUSERSQLCMDUSER -U-U RR """"
SQLCMDPASSWORDSQLCMDPASSWORD -P-P -- """"
SQLCMDSERVERSQLCMDSERVER -S-S RR "DefaultLocalInstance""DefaultLocalInstance"
SQLCMDWORKSTATIONSQLCMDWORKSTATION -H-H RR "ComputerName""ComputerName"
SQLCMDDBNAMESQLCMDDBNAME -d-d RR """"
SQLCMDLOGINTIMEOUTSQLCMDLOGINTIMEOUT -l-l R/WR/W "8"(秒)"8" (seconds)
SQLCMDSTATTIMEOUTSQLCMDSTATTIMEOUT -t-t R/WR/W "0" = 无限期等待"0" = wait indefinitely
SQLCMDHEADERSSQLCMDHEADERS -H-h R/WR/W "0""0"
SQLCMDCOLSEPSQLCMDCOLSEP -S-s R/WR/W “ ”" "
SQLCMDCOLWIDTHSQLCMDCOLWIDTH -w-w R/WR/W "0""0"
SQLCMDPACKETSIZESQLCMDPACKETSIZE -A-a RR "4096""4096"
SQLCMDERRORLEVELSQLCMDERRORLEVEL -M-m R/WR/W 00
SQLCMDMAXVARTYPEWIDTHSQLCMDMAXVARTYPEWIDTH -y-y R/WR/W "256""256"
SQLCMDMAXFIXEDTYPEWIDTHSQLCMDMAXFIXEDTYPEWIDTH -y-Y R/WR/W "0" = 无限制"0" = unlimited
SQLCMDEDITORSQLCMDEDITOR R/WR/W "edit.com""edit.com"
SQLCMDINISQLCMDINI RR """"
SQLCMDUSEAADSQLCMDUSEAAD -G-G R/WR/W """"

使用 :Connect 时设置 SQLCMDUSER、SQLCMDPASSWORD 和 SQLCMDSERVER。SQLCMDUSER, SQLCMDPASSWORD and SQLCMDSERVER are set when :Connect is used.

R 表示在程序初始化过程中只能设置一次值。R indicates the value can only be set one time during program initialization.

R/W 表示可以使用 setvar 命令修改值,并且后续命令将受新值的影响。R/W indicates that the value can be modified by using the setvar command and subsequent commands will be influenced by the new value.

sqlcmd 命令sqlcmd Commands

除了中的 TRANSACT-SQL 语句sqlcmd,也有以下命令:In addition to Transact-SQL statements within sqlcmd, the following commands are also available:

GO [count]GO [count] :List:List
[:] RESET[:] RESET :Error:Error
[:] ED[:] ED :Out:Out
[:] !![:] !! :Perftrace:Perftrace
[:] QUIT[:] QUIT :Connect:Connect
[:] EXIT[:] EXIT :On Error:On Error
:r:r :Help:Help
:ServerList:ServerList :XML [ON | OFF]:XML [ON | OFF]
:Setvar:Setvar :Listvar:Listvar

使用 sqlcmd 命令时,请注意以下事项:Be aware of the following when you use sqlcmd commands:

  • 除 GO 以外,所有 sqlcmd 命令必须以冒号 (:) 为前缀。All sqlcmd commands, except GO, must be prefixed by a colon (:).

    重要

    为了保持现有 osql 脚本的向后兼容性,有些命令会被视为不带冒号。To maintain backward compatibility with existing osql scripts, some of the commands will be recognized without the colon. 这由 [:] 指示。This is indicated by the [:].

  • sqlcmd 命令只有出现在一行的开头时,才能够被识别。sqlcmd commands are recognized only if they appear at the start of a line.

  • 所有 sqlcmd 命令都不区分大小写。All sqlcmd commands are case insensitive.

  • 每个命令都必须位于单独的行中。Each command must be on a separate line. 命令不能跟 TRANSACT-SQL 语句或另一个命令。A command cannot be followed by a Transact-SQL statement or another command.

  • 命令将被立即执行。Commands are executed immediately. 它们不会放在执行缓冲区,如 TRANSACT-SQL 语句。They are not put in the execution buffer as Transact-SQL statements are.

    编辑命令Editing Commands
    [:] ED[:] ED
    启动文本编辑器。Starts the text editor. 此编辑器可以用于编辑当前的 TRANSACT-SQL 批处理中,或最后一个执行批处理。This editor can be used to edit the current Transact-SQL batch, or the last executed batch. 若要编辑上次执行的批处理,必须在上一批处理执行完之后立即键入 ED 命令。To edit the last executed batch, the ED command must be typed immediately after the last batch has completed execution.

    文本编辑器由 SQLCMDEDITOR 环境变量定义。The text editor is defined by the SQLCMDEDITOR environment variable. 默认编辑器为“Edit”。The default editor is 'Edit'. 若要更改编辑器,请设置 SQLCMDEDITOR 环境变量。To change the editor, set the SQLCMDEDITOR environment variable. 例如,若要在命令提示符下设置为 Microsoft 记事本的编辑器中,键入:For example, to set the editor to Microsoft Notepad, at the command prompt, type:

    SET SQLCMDEDITOR=notepad

    [:] RESET[:] RESET
    清除语句缓存。Clears the statement cache.

    :List:List
    输出语句缓存的内容。Prints the content of the statement cache.

    变量Variables
    :Setvar <var> [ "value" ]:Setvar <var> [ "value" ]
    定义 sqlcmd 脚本变量。Defines sqlcmd scripting variables. 脚本变量具有如下格式: $(VARNAME)Scripting variables have the following format: $(VARNAME).

    变量名称不区分大小写。Variable names are case insensitive.

    可以通过下列方式设置脚本变量:Scripting variables can be set in the following ways:

  • 隐式使用命令行选项。Implicitly using a command-line option. 例如, -l 选项可设置 SQLCMDLOGINTIMEOUT sqlcmd 变量。For example, the -l option sets the SQLCMDLOGINTIMEOUT sqlcmd variable.

  • 显式使用 :Setvar 命令。Explicitly by using the :Setvar command.

  • 在运行 sqlcmd之前定义一个环境变量。By defining an environment variable before you run sqlcmd.

备注

-X 选项可阻止将环境变量传递给 sqlcmdThe -X option prevents environment variables from being passed on to sqlcmd.

如果使用 :Setvar 定义的变量和某个环境变量同名,则使用 :Setvar 定义的变量优先。If a variable defined by using :Setvar and an environment variable have the same name, the variable defined by using :Setvar takes precedence.

变量名中不能包含空格字符。Variable names must not contain blank space characters.

变量名不能与变量表达式(如 $(var))具有相同的形式。Variable names cannot have the same form as a variable expression, such as $(var).

如果脚本变量的字符串值中含有空格,请用引号将该值引起来。If the string value of the scripting variable contains blank spaces, enclose the value in quotation marks. 如果未指定脚本变量的值,则将删除该脚本变量。If a value for a scripting variable is not specified, the scripting variable is dropped.

:Listvar:Listvar
显示当前设置的脚本变量列表。Displays a list of the scripting variables that are currently set.

备注

只显示由 sqlcmd设置的脚本变量和使用 :Setvar 命令设置的脚本变量。Only scripting variables that are set by sqlcmd, and those that are set using the :Setvar command will be displayed.

输出命令Output Commands
:Error :Error
< filename >| STDERR|STDOUT< filename >| STDERR|STDOUT
将所有错误输出重定向到 file name指定的文件、 stderrstdoutRedirect all error output to the file specified by file name, to stderr or to stdout. Error 命令可以在一个脚本中多次出现。The Error command can appear multiple times in a script. 默认情况下,错误输出将发送到 stderrBy default, error output is sent to stderr.

file namefile name
创建并打开一个要接收输出的文件。Creates and opens a file that will receive the output. 若该文件已经存在,则将其截断为零字节。If the file already exists, it will be truncated to zero bytes. 若该文件不可用(由于权限或其他原因),将不会切换输出,也不会将输出发送到上次指定的目标或默认目标。If the file is not available because of permissions or other reasons, the output will not be switched and will be sent to the last specified or default destination.

STDERRSTDERR
将错误输出切换到 stderr 流。Switches error output to the stderr stream. 如果已经重定向,流的重定向目标将会收到错误输出。If this has been redirected, the target to which the stream has been redirected will receive the error output.

STDOUTSTDOUT
将错误输出切换到 stdout 流。Switches error output to the stdout stream. 如果已经重定向,流的重定向目标将会收到错误输出。If this has been redirected, the target to which the stream has been redirected will receive the error output.

:Out < filename >| STDERR| STDOUT:Out < filename >| STDERR| STDOUT
创建所有查询结果并将它们重定向到 file name指定的文件、 stderrstdoutCreates and redirects all query results to the file specified by file name, to stderr or to stdout. 默认情况下,输出将发送到 stdoutBy default, output is sent to stdout. 若该文件已经存在,则将其截断为零字节。If the file already exists, it will be truncated to zero bytes. Out 命令可以在一个脚本中多次出现。The Out command can appear multiple times in a script.

:Perftrace < filename >| STDERR| STDOUT:Perftrace < filename >| STDERR| STDOUT
创建所有性能跟踪信息并将它们重定向到 file name指定的文件、 stderrstdoutCreates and redirects all performance trace information to the file specified by file name, to stderr or to stdout. 默认情况下,性能跟踪输出将发送到 stdoutBy default performance trace output is sent to stdout. 若该文件已经存在,则将其截断为零字节。If the file already exists, it will be truncated to zero bytes. Perftrace 命令可以在一个脚本中多次出现。The Perftrace command can appear multiple times in a script.

执行控制命令Execution Control Commands
:On Error[ exit | ignore]:On Error[ exit | ignore]
设置在脚本或批处理执行过程中发生错误时要执行的操作。Sets the action to be performed when an error occurs during script or batch execution.

使用 exit 选项时, sqlcmd 退出,并显示相应的错误值。When the exit option is used, sqlcmd exits with the appropriate error value.

使用 ignore 选项时, sqlcmd 会忽略错误,并继续执行批处理或脚本。When the ignore option is used, sqlcmd ignores the error and continues executing the batch or script. 默认情况下,会输出错误消息。By default, an error message will be printed.

[:] QUIT[:] QUIT
导致 sqlcmd 退出。Causes sqlcmd to exit.

[:] EXIT[ (statement) ][:] EXIT[ (statement) ]
允许将 SELECT 语句的结果用作 sqlcmd的返回值。Lets you use the result of a SELECT statement as the return value from sqlcmd. 如果为数值,最后一个结果行的第一列将转换为 4 字节的整数(长整型)。If numeric, the first column of the last result row is converted to a 4-byte integer (long). MS-DOS 将低字节传递给父进程或操作系统错误级别。MS-DOS passes the low byte to the parent process or operating system error level. Windows 200x 传递整个 4 字节整数。Windows 200x passes the whole 4-byte integer. 语法为:The syntax is:

:EXIT(query)

例如:For example:

:EXIT(SELECT @@ROWCOUNT)

您还可以在批处理文件中包含 EXIT 参数。You can also include the EXIT parameter as part of a batch file. 例如,在命令提示符处键入:For example, at the command prompt, type:

sqlcmd -Q "EXIT(SELECT COUNT(*) FROM '%1')"

使用 sqlcmd 实用工具将圆括号 () 中的所有内容发送给服务器。The sqlcmd utility sends everything between the parentheses () to the server. 如果系统存储过程选择了一个集合并返回一个值,则仅返回选择的内容。If a system stored procedure selects a set and returns a value, only the selection is returned. 如果圆括号中没有任何内容,则 EXIT() 语句会执行批处理中此语句前的所有内容,然后退出,且不返回任何值。The EXIT() statement with nothing between the parentheses executes everything before it in the batch and then exits without a return value.

当指定了不正确的查询时, sqlcmd 将退出,且不返回任何值。When an incorrect query is specified, sqlcmd will exit without a return value.

下面是 EXIT 格式的列表:Here is a list of EXIT formats:

  • :EXIT:EXIT

    不执行批处理就立即退出,无返回值。Does not execute the batch, and then quits immediately and returns no value.

  • :EXIT( ):EXIT( )

    执行批处理后退出,不返回值。Executes the batch, and then quits and returns no value.

  • :EXIT(query):EXIT(query)

    执行包括查询的批处理,返回查询的结果后退出。Executes the batch that includes the query, and then quits after it returns the results of the query.

    如果在 sqlcmd 脚本中使用 RAISERROR,并且出现状态 127,则 sqlcmd 将退出,并将消息 ID 返回给客户端。If RAISERROR is used within a sqlcmd script and a state of 127 is raised, sqlcmd will quit and return the message ID back to the client. 例如:For example:

    RAISERROR(50001, 10, 127)

    该错误会导致 sqlcmd 脚本终止并将消息 ID 50001 返回给客户端。This error will cause the sqlcmd script to end and return the message ID 50001 to the client.

    返回值为-1 到-99 保留了 SQL Server;sqlcmd定义以下的其他返回值:The return values -1 to -99 are reserved by SQL Server ; sqlcmd defines the following additional return values:

返回值Return Values DescriptionDescription
-100-100 选择返回值前遇到错误。Error encountered prior to selecting return value.
-101-101 选择返回值时找不到行。No rows found when selecting return value.
-102-102 选择返回值时发生转换错误。Conversion error occurred when selecting return value.

GO [count]GO [count]
转到发出信号,这两个批的末尾,并执行任何缓存 TRANSACT-SQL 语句。作为单独的批处理文件; 多次执行批处理不能在单个批处理中多次声明变量。GO signals both the end of a batch and the execution of any cached Transact-SQL statements.The batch is executed multiple times as separate batches; you cannot declare a variable more than once in a single batch.

其他命令Miscellaneous Commands
:r < filename>:r < filename >
分析其他 TRANSACT-SQL 语句和sqlcmd从指定的文件的命令 < ***filename>到语句中缓存。Parses additional Transact-SQL statements and **sqlcmd* commands from the file specified by <filename>into the statement cache.

如果该文件包含不跟的 TRANSACT-SQL 语句,必须输入后面的行上: rIf the file contains Transact-SQL statements that are not followed by GO, you must enter GO on the line that follows :r.

备注

系统会相对于 sqlcmd 在其中运行的启动目录读取 < filename >< filename > is read relative to the startup directory in which sqlcmd was run.

当遇到批处理终止符之后,将读取并执行该文件。The file will be read and executed after a batch terminator is encountered. 可以发出多个 :r 命令。You can issue multiple :r commands. 该文件可以包含任何 sqlcmd 命令,The file may include any sqlcmd command. 包括批处理终止符 GOThis includes the batch terminator GO.

备注

每遇到一个 :r 命令,交互模式下显示的行计数都会加一。The line count that is displayed in interactive mode will be increased by one for every :r command encountered. :r 命令会出现在 list 命令的输出中。The :r command will appear in the output of the list command.

:Serverlist:Serverlist
列出在本地配置的服务器和在网络上广播的服务器的名称。Lists the locally configured servers and the names of the servers broadcasting on the network.

:Connect server_name[\instance_name] [-l timeout] [-U user_name [-P password]]:Connect server_name[\instance_name] [-l timeout] [-U user_name [-P password]]
连接到 SQL Server 的实例。Connects to an instance of SQL Server . 同时关闭当前的连接。Also closes the current connection.

超时选项:Time-out options:

00 永远等待wait forever
n>0n>0 等待 n 秒钟wait for n seconds

SQLCMDSERVER 脚本变量将反映当前的活动连接。The SQLCMDSERVER scripting variable will reflect the current active connection.

如果未指定 timeout ,则其默认值将为 SQLCMDLOGINTIMEOUT 变量的值。If timeout is not specified, the value of the SQLCMDLOGINTIMEOUT variable is the default.

仅当指定了 user_name (作为选项或环境变量)时,才会提示用户输入密码。If only user_name is specified (either as an option, or as an environment variable), the user will be prompted to enter a password. 如果已设置 SQLCMDUSER 或 SQLCMDPASSWORD 环境变量,则不会出现此提示。This is not true if the SQLCMDUSER or SQLCMDPASSWORD environment variables have been set. 如果既未提供选项,又未提供环境变量,则使用 Windows 身份验证模式登录。If neither options nor environment variables are provided, Windows Authentication mode is used to login. 例如,若要连接到的实例, instance1,SQL server myserver,通过使用集成的安全性,你将使用以下:For example to connect to an instance, instance1, of SQL Server , myserver, by using integrated security you would use the following:

:connect myserver\instance1

若要使用脚本变量连接到 myserver 的默认实例,您会使用以下内容:To connect to the default instance of myserver using scripting variables, you would use the following:

:setvar myusername test

:setvar myservername myserver

:connect $(myservername) $(myusername)

[:] !!< command>[:] !!< command>
执行操作系统命令。Executes operating system commands. 若要执行操作系统命令,请用两个感叹号 (!!) 开始一行,后面输入操作系统命令。To execute an operating system command, start a line with two exclamation marks (!!) followed by the operating system command. 例如:For example:

:!! Dir

备注

该命令在运行 sqlcmd 的计算机上执行。The command is executed on the computer on which sqlcmd is running.

:XML [ON | OFF]:XML [ON | OFF]
有关详细信息,请参阅本主题中的 XML 输出格式JSON 输出格式For more information, see XML Output Format and JSON Output Format in this topic

:Help:Help
列出 sqlcmd 命令以及每个命令的简短说明。Lists sqlcmd commands together with a short description of each command.

sqlcmd 文件名sqlcmd File Names

可以使用sqlcmd 选项或 sqlcmd 命令指定 sqlcmd 输入文件。sqlcmd input files can be specified with the -i option or the :r command. 可以使用 -o 选项或 :Error:Out:Perftrace 命令指定输出文件。Output files can be specified with the -o option or the :Error, :Out and :Perftrace commands. 以下是使用这些文件的一些原则:The following are some guidelines for working with these files:

  • : 错误: 出: Perftrace应使用单独 < ***filename>:Error, **:Out* and :Perftrace should use separate <filename>. 如果相同 < ***filename* >** 是使用,就可能混合来自命令的输入。If the same <filename> is used, inputs from the commands may be intermixed.

  • 如果从本地计算机的 sqlcmd 调用远程服务器上的输入文件,并且该文件包含驱动器文件路径(如 :out c:\OutputFile.txt),If an input file that is located on a remote server is called from sqlcmd on a local computer and the file contains a drive file path such as :out c:\OutputFile.txt. 将在本地计算机而不是远程服务器上创建输出文件。The output file will be created on the local computer and not on the remote server.

  • 有效文件路径包括: C:\<filename>\\<Server>\<Share$>\<filename>"C:\Some Folder\<file name>"Valid file paths include: C:\<filename>, \\<Server>\<Share$>\<filename> and "C:\Some Folder\<file name>". 如果路径中包含空格,请使用引号。If there is a space in the path, use quotation marks.

  • 每个新的 sqlcmd 会话都将覆盖现有的同名文件。Each new sqlcmd session will overwrite existing files that have the same names.

信息性消息Informational Messages

sqlcmd 将输出由服务器发送的所有信息性消息。sqlcmd prints any informational message that are sent by the server. 在以下示例中,执行 TRANSACT-SQL 语句后,一条信息性消息都将打印。In the following example, after the Transact-SQL statements are executed, an informational message is printed.

在命令提示符下键入以下内容:At the command prompt, type the following:

sqlcmd

At the sqlcmd prompt type:

USE AdventureWorks2012;

GO

按下 Enter 时,会输出以下信息性消息:“已将数据库上下文改为 'AdventureWorks2012'。”When you press ENTER, the following informational message is printed: "Changed database context to 'AdventureWorks2012'."

Transact-SQL 查询的输出格式Output Format from Transact-SQL Queries

sqlcmd 首先输出列标题,其中包含在选择列表中指定的列名。sqlcmd first prints a column header that contains the column names specified in the select list. 列名使用 SQLCMDCOLSEP 字符分隔。The column names are separated by using the SQLCMDCOLSEP character. 默认情况下,将使用空格。By default, this is a space. 如果列名短于列宽,则使用空格填充输出,直到下一列。If the column name is shorter than the column width, the output is padded with spaces up to the next column.

此行将跟随一行分隔行,分隔行是一系列的破折号字符。This line will be followed by a separator line that is a series of dash characters. 以下输出显示了一个示例。The following output shows an example.

启动 sqlcmdStart sqlcmd. sqlcmd 命令提示符下键入以下命令:At the sqlcmd command prompt, type the following:

USE AdventureWorks2012;

SELECT TOP (2) BusinessEntityID, FirstName, LastName

FROM Person.Person;

GO

按下 Enter 时,会返回以下结果集。When you press ENTER, the following result set is retuned.

BusinessEntityID FirstName LastName

---------------- ------------ ----------

285 Syed Abbas

293 Catherine Abel

(2 row(s) affected)

虽然 BusinessEntityID 列只有 4 个字符宽,但已将其扩展以适应更长的列名。Although the BusinessEntityID column is only 4 characters wide, it has been expanded to accommodate the longer column name. 默认情况下,输出会在 80 个字符处终止。By default, output is terminated at 80 characters. 可通过使用 -w 选项或设置 SQLCMDCOLWIDTH 脚本变量来进行更改。This can be changed by using the -w option, or by setting the SQLCMDCOLWIDTH scripting variable.

XML 输出格式XML Output Format

从 FOR XML 子句得到的 XML 输出是在连续流中的未格式化的输出。XML output that is the result of a FOR XML clause is output, unformatted, in a continuous stream.

若要得到 XML 输出,请使用以下命令: :XML ONWhen you expect XML output, use the following command: :XML ON.

备注

sqlcmd 将采用常见的格式返回错误消息。sqlcmd returns error messages in the usual format. 请注意,XML 文本流中的错误消息还将采用 XML 格式输出。Notice that the error messages are also output in the XML text stream in XML format. 如果使用 :XML ON,则 sqlcmd 不显示信息性消息。By using :XML ON, sqlcmd does not display informational messages.

若要关闭 XML 模式,请使用以下命令: :XML OFFTo set the XML mode off, use the following command: :XML OFF.

发出 XML OFF 命令之前不应显示 GO 命令,因为 XML OFF 命令会将 sqlcmd 切换回面向行的输出。The GO command should not appear before the XML OFF command is issued because the XML OFF command switches sqlcmd back to row-oriented output.

XML(流形式)数据和行集数据不能混合。XML (streamed) data and rowset data cannot be mixed. 如果未执行输出 XML 流的 TRANSACT-SQL 语句之前发出 XML ON 命令,则输出将为乱码。If the XML ON command has not been issued before a Transact-SQL statement that outputs XML streams is executed, the output will be garbled. 如果已发出 XML ON 命令,则无法执行输出常规行集的 TRANSACT-SQL 语句。If the XML ON command has been issued, you cannot execute Transact-SQL statements that output regular row sets.

备注

:XML 命令不支持 SET STATISTICS XML 语句。The :XML command does not support the SET STATISTICS XML statement.

JSON 输出格式JSON Output Format

若要得到 JSON 输出,请使用以下命令: :XML ONWhen you expect JSON output, use the following command: :XML ON. 否则,输出包括的列名和 JSON 文本。Otherwise the output includes both the column name and the JSON text. 此输出不是有效的 JSON。This output is not valid JSON.

若要关闭 XML 模式,请使用以下命令: :XML OFFTo set the XML mode off, use the following command: :XML OFF.

有关详细信息,请参阅本主题中的 XML 输出格式For more info, see XML Output Format in this topic.

使用 Azure Active Directory 身份验证Using Azure Active Directory Authentication

使用 Azure Active Directory 身份验证的示例:Examples using Azure Active Directory Authentication:

sqlcmd -S Target_DB_or_DW.testsrv.database.windows.net  -G  -l 30
sqlcmd -S Target_DB_or_DW.testsrv.database.windows.net -U bob@contoso.com -P MyAADPassword -G -l 30

sqlcmd 最佳方法sqlcmd Best Practices

使用以下方法来帮助实现最高的安全性和效率。Use the following practices to help maximize security and efficiency.

  • 使用集成安全性。Use integrated security.

  • 在自动化环境中使用 -XUse -X in automated environments.

  • 使用适当的 NTFS 文件系统权限保护输入文件和输出文件。Secure input and output files by using appropriate NTFS file system permissions.

  • 若要提高性能,请在一个 sqlcmd 会话中执行尽可能多的操作,而不是在一系列会话中来执行这些操作。To increase performance, do as much in one sqlcmd session as you can, instead of in a series of sessions.

  • 将批处理或查询执行的超时值设置为大于您所预期的值。Set time-out values for batch or query execution higher than you expect it will take to execute the batch or query.

另请参阅See Also

启动 sqlcmd 实用工具 Start the sqlcmd Utility
使用 sqlcmd 运行 Transact-SQL 脚本文件 Run Transact-SQL Script Files Using sqlcmd
使用 sqlcmd 实用工具 Use the sqlcmd Utility
将 sqlcmd 与脚本变量结合使用 Use sqlcmd with Scripting Variables
使用 sqlcmd 连接到数据库引擎 Connect to the Database Engine With sqlcmd
使用查询编辑器编辑 SQLCMD 脚本 Edit SQLCMD Scripts with Query Editor
管理作业步骤 Manage Job Steps
创建 CmdExec 作业步骤Create a CmdExec Job Step