sqlcmd 公用程式sqlcmd Utility

適用於: 是SQL Server是Azure SQL Database是Azure Synapse Analytics (SQL DW)是平行處理資料倉儲APPLIES TO: YesSQL Server YesAzure SQL Database YesAzure Synapse Analytics (SQL DW) YesParallel Data Warehouse

針對 SQL Server 2014 與更舊版本,請參閱 sqlcmd 公用程式For SQL Server 2014 and lower, see sqlcmd Utility.

如需在 Linux 上使用 sqlcmd 的詳細資訊,請參閱在 Linux上安裝 sqlcmd 與 bcpFor using sqlcmd on Linux, see Install sqlcmd and bcp on Linux.

sqlcmd 公用程式可讓您透過下列各種可用的模式,在命令列提示字元中輸入 Transact-SQL 陳述式、系統程序和指令碼檔案:The sqlcmd utility lets you enter Transact-SQL statements, system procedures, and script files through a variety of available modes:

  • 在命令提示字元中。At the command prompt.
  • 在 [查詢編輯器] 中的 SQLCMD 模式中。In Query Editor in SQLCMD mode.
  • 在 Windows 指令碼檔案中。In a Windows script file.
  • 在 SQL Server Agent 作業的作業系統(Cmd.exe) 作業步驟中。In an operating system (Cmd.exe) job step of a SQL Server Agent job.

此公用程式會使用 ODBC 來執行 Transact-SQL 批次。The utility uses ODBC to execute Transact-SQL batches.

下載最新版的 sqlcmd 公用程式Download the latest version of sqlcmd Utility

下載 下載適用於 SQL Server (x64) 的 Microsoft 命令列公用程式 15 (2.6 MB)download Download Microsoft Command Line Utilities 15 for SQL Server (x64) (2.6 MB)
下載 下載適用於 SQL Server (x86) 的 Microsoft 命令列公用程式 15 (2.3 MB)download Download Microsoft Command Line Utilities 15 for SQL Server (x86) (2.3 MB)

命令列工具已正式推出,不過,其將透過 SQL Server 2019 (15.x)SQL Server 2019 (15.x) 的安裝程式套件發行。The command line tools are General Availability (GA), however they are being released with the installer package for SQL Server 2019 (15.x)SQL Server 2019 (15.x).

版本資訊Version Information

版本號碼:15.0Release number: 15.0
組建編號:15.0.1300.359Build number: 15.0.1300.359
發行日期:2019 年 3 月 13 日Release date: March 13, 2019

新版本的 SQLCMD 支援 Azure AD 驗證,其包含 SQL Database、SQL 資料倉儲,以及 Always Encrypted 功能的多重要素驗證 (MFA) 支援。The new version of SQLCMD supports Azure AD authentication, including Multi-Factor Authentication (MFA) support for SQL Database, SQL Data Warehouse, and Always Encrypted features. 新的 BCP 支援 Azure AD 驗證,其包含 SQL Database 與 SQL 資料倉儲的多重要素驗證 (MFA) 支援。The new BCP supports Azure AD authentication, including Multi-Factor Authentication (MFA) support for SQL Database and SQL Data Warehouse.

系統需求 Windows 10、Windows 7、Windows 8、Windows 8.1、Windows Server 2008、Windows Server 2008 R2、Windows Server 2008 R2 SP1、Windows Server 2012、Windows Server 2012 R2System Requirements Windows 10 , Windows 7, Windows 8, Windows 8.1, Windows Server 2008, Windows Server 2008 R2, Windows Server 2008 R2 SP1, Windows Server 2012, Windows Server 2012 R2

此元件同時需要 Windows Installer 4.5Microsoft ODBC Driver 17 for SQL ServerThis component requires both Windows Installer 4.5 and Microsoft ODBC Driver 17 for SQL Server.

若要檢查 SQLCMD 版本執行 sqlcmd -? 命令,並確認正在使用 15.0.1300.359 版本或更新版本。To check the SQLCMD version execute sqlcmd -? command and confirm that 15.0.1300.359 version or higher is in use.

注意

您需要 13.1 版或更新版本才能支援 Always Encrypted (-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 Cloud Shell 試用已預先安裝的 sqlcmd 公用程式:啟動 Cloud 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 SqlClient,在 [查詢編輯器] 中執行正規和 SQLCMD 模式。(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. 因為可能會套用不同的預設選項,因此,以 SQLCMD 模式在 SQL Server Management StudioSQL Server Management Studio 中以及在 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 doesn't 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。Signs in to SQL Server with a Dedicated Administrator Connection (DAC). 這種連接可用以進行伺服器的疑難排解。This kind of connection is used to troubleshoot a server. 此連接只適用於支援 DAC 的伺服器電腦。This connection works only 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. -G 選項不支援 -A 選項。The -A option isn't supported with the -G option. 當使用 -A 連線到 SQL Database 時,您必須為 SQL Server 系統管理員。When connecting to SQL Database using -A, you must be a SQL server administrator. DAC 無法供 Azure Active Directory 系統管理員使用。DAC isn't available for an Azure Active Directory administrator.

-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 時會發出 USEdb_name 陳述式。Issues a USE db_name statement when you start sqlcmd. 這個選項會設定 sqlcmd 指令碼變數 SQLCMDDBNAME。This option sets the sqlcmd scripting variable SQLCMDDBNAME. 這項參數會指定初始資料庫。This parameter specifies the initial database. 預設值為您登入的預設資料庫屬性。The default is your login's default-database property. 如果資料庫不存在,系統會產生一則錯誤訊息,且會結束 sqlcmdIf 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 sign in 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 參數至少需要 sqlcmd13.1版。The -g switch requires at least sqlcmd version 13.1. 若要判斷您的版本,請執行 sqlcmd -?To determine your version, execute sqlcmd -?.

-G-G
這個參數在連線到 SQL Database 或 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 參數至少需要 sqlcmd13.1版。The -G switch requires at least sqlcmd version 13.1. 若要判斷您的版本,請執行 sqlcmd -?To determine your version, execute sqlcmd -?. 如需詳細資訊,請參閱 使用 Azure Active Directory 驗證連線到 SQL Database 或 SQL 資料倉儲For more information, see Connecting to SQL Database or SQL Data Warehouse By Using Azure Active Directory Authentication. -G 選項不支援 -A 選項。The -A option is not supported with the -G option.

重要

-G 選項只適用於 Azure SQL Database 與 Azure 資料倉儲。The -G option only applies to Azure SQL Database and Azure Data Warehouse. Linux 或 macOS 目前不支援 AAD 整合式與互動式驗證。AAD Integrated and Interactive Authentication is not currently supported on Linux or macOS.

  • 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 
    

    -G 參數會在後端產生下列連接字串︰The -G parameter generates 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. Linux 或 macOS 目前不支援 AAD 整合式驗證AAD Integrated Authentication is not currently supported on Linux or macOS.

    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.

  • Azure Active Directory 互動式Azure Active Directory Interactive

    Azure SQL Database 與 SQL 資料倉儲的 Azure AD 互動式驗證,可讓您使用支援多重要素驗證的互動式方法。The Azure AD Interactive authentication for Azure SQL Database and SQL Data Warehouse, allows you to use an interactive method supporting multi-factor authentication. 如需詳細資訊,請參閱 Active Directory 互動式驗證For more information, see Active Directory Interactive Authentication.

    Azure AD 互動需要 sqlcmd 15.0.1000.34 版 或更新版本,以及 ODBC 17.2 版或更新版本Azure AD interactive requires sqlcmd version 15.0.1000.34 or later as well as ODBC version 17.2 or later.

    若要啟用互動式驗證,請在不使用密碼的情況下,僅以使用者名稱 (-U) 提供 -G 選項。To enable interactive authentication, provide -G option with user name (-U) only, without a password.

    下列範例會使用 Azure AD 互動模式來匯出資料,其中指出使用者代表 AAD 帳戶的使用者名稱。The following example exports data using Azure AD interactive mode indicating username where user represents an AAD account. 此為與在上一區段中所使用的相同範例:Azure Active Directory 使用者名稱與密碼This is the same example used in the previous section: Azure Active Directory Username and Password.

    互動模式需要手動輸入密碼,若為已啟用多重要素驗證的帳戶,請完成您已設定的 MFA 驗證方法。Interactive mode requires a password to be manually entered, or for accounts with multi-factor authentication enabled, complete your configured MFA authentication method.

    sqlcmd -S testsrv.database.windows.net -d Target_DB_or_DW -G -U alice@aadtest.onmicrosoft.com
    

    先前的命令會在後端產生下列連接字串︰The previous command generates the following connection string in the backend:

    SERVER = Target_DB_or_DW.testsrv.database.windows.net;UID=alice@aadtest.onmicrosoft.com; AUTHENTICATION = ActiveDirectoryInteractive   
    

    如果 Azure AD 使用者是使用 Windows 帳戶的網域同盟使用者,則命令列中所需的使用者名稱會包含其網域帳戶 (例如,joe@contoso.com 如下所示):In case an Azure AD user is a domain federated user using a Windows account, the user name required in the command-line, contains its domain account (for example, joe@contoso.com see below):

    sqlcmd -S testsrv.database.windows.net -d Target_DB_or_DW -G -U joe@contoso.com  
    

    如果來賓使用者存在於特定 Azure AD 中,且屬於 SQL DB 中具有執行 sqlcmd 命令的資料庫權限群組,則會使用其來賓使用者別名 (例如, *keith0@adventureworks.com* )。If guest users exist in a specific Azure AD and are part of a group that exists in SQL DB that has database permissions to execute the sqlcmd command, their guest user alias is used (for example, *keith0@adventureworks.com*).

    重要

    使用 -G-U 選項搭配 SQLCMD 時,存在一個已知問題:若在 -G 選項之前放置 -U 選項可能會導致驗證失敗。There is a known issue when using the -G and -U option with SQLCMD, where putting the -U option before the -G option may cause authentication to fail. 請一律先以 -G 選項開始,再放置 -U 選項。Always start with the -G option followed by the -U 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
在連線到 SQL Server 可用性群組的可用性群組接聽程式或 SQL Server 容錯移轉叢集執行個體時,一律指定 -MAlways 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,則會關閉 -MIf -M is not specified, -M is off. 如需詳細資訊,請參閱接聽程式、用戶端連線能力、應用程式容錯移轉建立及設定可用性群組 (SQL Server)容錯移轉叢集和 AlwaysOn 可用性群組 (SQL Server)使用中次要:可讀取的次要複本 (AlwaysOn 可用性群組)For more information about Listeners, Client Connectivity, Application Failover, Creation and Configuration of Availability Groups (SQL Server), Failover Clustering and Always On Availability Groups (SQL Server), and Active Secondaries: Readable Secondary Replicas(Always On Availability Groups).

-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. 我們不建議使用 Null 密碼,但您可以針對參數值使用一組連續雙引號來指定 Null 密碼:We do not recommend the use of the null password, but you can specify the null password by using a pair of contiguous double-quotation marks for the parameter value:

  • -P ""-P ""

我們建議您使用強式密碼。We recommend that you use a strong password.

使用強式密碼! 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 環境變數。The SQLCMDPASSWORD environment variable takes precedence over the OSQLPASSWORD environment variable. OSQLPASSWORD 現在已不再共用,sqlcmdosql 公用程式可以在彼此之間使用,而不會受到干擾。Now that OSQLPASSWORD is no longer shared, the utilities sqlcmd and osql can be used next to each other without interference. 舊版指令碼仍可繼續使用。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).

如果您啟動 sqlcmd 時,並未指定 server_name [ \ instance_name ],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 or 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 article), 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) 和位元組由小到大 (Little-Endian) 的 Unicode 輸入檔。sqlcmd automatically recognizes both big-endian and little-endian Unicode input files. 如果已指定 -u 選項,則輸出一律為位元組由小到大的 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 approach 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.

這個選項可以使用一次以上: -iinput_file -II input_fileThis 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.

如果指定 -uoutput_file 會以 Unicode 格式儲存。If -u is specified, the output_file is stored in Unicode format. 如果檔案名稱無效,系統會產生一則錯誤訊息,且會結束 sqlcmdIf 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 is also relevant to 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 的格式為何, output_file均以 Unicode 格式儲存。Specifies 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 article.

-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 article.

-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 值,命令不會逾時。query**time_out 必須是介於 1 與 65534 之間的數字。If a time_out value is not specified, the command does not time out. The query**time_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 = " values " 值。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 parameter 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 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 parameter 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 an 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 ) ,其中 1<=n<=8000char( n ), where 1<=n<=8000

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

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

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

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

  • variantvariant

錯誤報告選項Error Reporting Options
-b-b
指定在發生錯誤時, sqlcmd 會結束作業並傳回 DOS ERRORLEVEL 值。Specifies that sqlcmd exits and returns a DOS ERRORLEVEL value when an error occurs. 當 SQL Server 錯誤訊息的嚴重性層級大於 10 時,傳回 DOS ERRORLEVEL 變數的值是 1;否則,傳回的值是 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
控制哪些錯誤訊息會傳送至 stdoutControls 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.

-V 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. 根據預設,在一行中單獨鍵入 "GO" 這個字,便會終止命令,並將命令傳給 SQL Server。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 presentation 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 display 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以外的任何值,就會產生錯誤,且會結束 sqlcmdIf 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. 如果指定了選擇性參數 1sqlcmd 會產生一則錯誤訊息,並結束作業。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 指令碼變數的詳細資訊,請參閱 以指令碼變數使用 sqlcmdFor 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,請在命令提示字元處鍵入 sqlcmd,並指定本文稍早所述的一或多個選項。To use sqlcmd interactively, type sqlcmd at the command prompt with any one or more of the options described earlier in this article. 如需詳細資訊,請參閱 使用 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 之前,在命令提示字元處設定的命令殼層 ( 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 """"

SQLCMDUSER、SQLCMDPASSWORD 和 SQLCMDSERVER 會在使用 :Connect 時設定。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

除了 sqlcmd 內的 Transact-SQL 陳述式之外,您也可以使用下列命令: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, 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 Notepad,請在命令提示字元之下,鍵入: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 <變數> [ " 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
< 檔案名稱 >| STDERR|STDOUT< filename >| STDERR|STDOUT
將所有錯誤輸出重新導向至 filename所指定的檔案、 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 < 檔案名稱 > | STDERR| STDOUT:Out < filename >| STDERR| STDOUT
建立並將所有查詢結果重新導向至 filename所指定的檔案、 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 is truncated to zero bytes. 在指令碼中, Out 命令可以重複出現。The Out command can appear multiple times in a script.

:Perftrace < 檔案名稱 > | STDERR| STDOUT:Perftrace < filename >| STDERR| STDOUT
建立並將所有效能追蹤資訊重新導向至 filename所指定的檔案、 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 is 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 is 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 位元組的整數 (long)。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 會結束作業,且會將訊息識別碼傳回用戶端。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 指令碼結束作業,並將訊息識別碼 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, and sqlcmd defines the following additional return values:

傳回值Return Values 描述Description
-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]
GO 會發出批次結束及執行任何快取的 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 < 檔案名稱 >:r < filename >
< filename > 所指定檔案中的其他 Transact-SQL 陳述式與 sqlcmd 命令,剖析至陳述式快取中。Parses additional Transact-SQL statements and sqlcmd commands from the file specified by <filename> into the statement cache.

如果檔案包含的 Transact-SQL 陳述式後面沒有緊接著 GO,您必須在 :r 之後的一行輸入 GOIf the file contains Transact-SQL statements that are not followed by GO, you must enter GO on the line that follows :r.

注意

< 檔案名稱 > 會在與執行 sqlcmd 的啟動目錄相對位置中讀取。< 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 命令時,以互動模式顯示的行數便會加 1。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 環境變數,就不會提示使用者。Users are not prompted 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 sign in. 例如,若要使用整合式安全性連線到 SQL Server myserver 的執行個體 instance1,您將使用下列命令:For example to connect to an instance, instance1, of SQL Server, myserver, by using integrated security you would use the following command:

: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 article

: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:

  • :Error:Out: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 is 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 is 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 command:

sqlcmd

在 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 query:

USE AdventureWorks2012;

SELECT TOP (2) BusinessEntityID, FirstName, LastName

FROM Person.Person;

GO

當您按下 ENTER,就會傳回下列結果集。When you press ENTER, the following result set is returned.

BusinessEntityID FirstName LastName

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

285 Syed Abbas

293 Catherine Abel

(2 row(s) affected)

雖然 BusinessEntityID 資料行的寬度只有四個字元,但它已擴充,能夠容納較長的資料行名稱。Although the BusinessEntityID column is only four 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 ONsqlcmd 就不會顯示參考用訊息。By using :XML ON, sqlcmd does not display informational messages.

若要將 XML 模式設定為關閉,請使用下列命令::XML OFFTo set the XML mode to 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 can't be mixed. 如果在執行輸出 XML 資料流的 Transact-SQL 陳述式之前發出 XML ON 命令,則輸出會混亂。If the XML ON command hasn't been issued before a Transact-SQL statement that outputs XML streams is executed, the output is garbled. 已發出 XML ON 命令之後,您就不能執行輸出正規資料列集的 Transact-SQL 陳述式。Once the XML ON command has been issued, you can't 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 to off, use the following command: :XML OFF.

如需詳細資訊,請參閱本文中的 XML 輸出格式For more info, see XML Output Format in this article.

使用 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 -G -U bob@contoso.com -P MyAADPassword -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 連接至 Database Engine Connect to the Database Engine With sqlcmd
使用查詢編輯器編輯 SQLCMD 指令碼 Edit SQLCMD Scripts with Query Editor
管理作業步驟 Manage Job Steps
建立 CmdExec 作業步驟Create a CmdExec Job Step

意見反應Feedback

needhelp_person_icon SQL 用戶端工具論壇needhelp_person_icon SQL Client Tools Forum

info_tip 取得說明Get help