使用 sqlcmd 实用工具

2017-06-13

该 sqlcmd 实用工具是一个命令行实用工具，用于临时执行 Transact-SQL 语句和脚本，以及自动执行 Transact-SQL 脚本任务。若要以交互方式使用 sqlcmd ，或生成要使用的 sqlcmd脚本文件，用户必须了解 Transact-SQL。该 sqlcmd 实用工具通常采用以下方式使用：

用户以交互方式以类似于在命令提示符下工作的方式输入 Transact-SQL 语句。结果显示在命令提示符处。若要打开命令提示符窗口，请单击“ 开始”，单击“ 所有程序”，指向 “附件”，然后单击 “命令提示符”。在命令提示符处，键入 sqlcmd，后面跟随所需的选项列表。有关受支持 sqlcmd的选项的完整列表，请参阅 sqlcmd 实用工具。
用户通过指定要执行的单个 Transact-SQL 语句或将实用工具指向包含要执行的 Transact-SQL 语句的文本文件来提交 sqlcmd 作业。输出通常定向到文本文件，但也可以在命令提示符处显示。
SQL Server Management Studio 查询编辑器中的 SQLCMD 模式。
SQL Server 管理对象 (SMO)
SQL Server 代理 CmdExec 作业。

通常使用的 sqlcmd 选项

以下选项最常使用：

标识 sqlcmd 要连接的 Microsoft SQL Server 实例的服务器选项（-S）。
指定sqlcmd用来连接到 SQL Server 实例的凭据的身份验证选项（-E、-U 和 -P）。

注释

-E 选项是默认值，无需指定。
输入选项（-Q、 -q 和 -i）标识输入 sqlcmd到的位置。
指定要在其中sqlcmd放置其输出的文件的输出选项（-o）。

连接到 sqlcmd 实用工具

下面是实用工具的 sqlcmd 常见用途：

通过 Windows 身份验证连接到默认实例以交互方式运行 Transact-SQL 语句：
```
sqlcmd -S <ComputerName>  
```
注释

在前面的示例中，未指定 -E ，因为它是默认实例，并使用 sqlcmd Windows 身份验证连接到默认实例。
使用 Windows 身份验证连接到命名实例以交互方式运行 Transact-SQL 语句：
```
sqlcmd -S <ComputerName>\<InstanceName>  
```
或
```
sqlcmd -S .\<InstanceName>  
```

使用 Windows 身份验证并指定输入和输出文件连接到命名实例：

sqlcmd -S <ComputerName>\<InstanceName> -i <MyScript.sql> -o <MyOutput.rpt>

使用 Windows 身份验证连接到本地计算机上的默认实例，执行查询，并在 sqlcmd 查询完成运行后继续运行：
```
sqlcmd -q "SELECT * FROM AdventureWorks2012.Person.Person"  
```
使用 Windows 身份验证连接到本地计算机上的默认实例，执行查询，将输出定向到文件，并在 sqlcmd 查询完成运行后退出：
```
sqlcmd -Q "SELECT * FROM AdventureWorks2012.Person.Person" -o MyOutput.txt  
```
使用 SQL Server 身份验证连接到命名实例以交互方式运行 Transact-SQL 语句，并 sqlcmd 提示输入密码：
```
sqlcmd -U MyLogin -S <ComputerName>\<InstanceName>  
```
注释

要查看实用工具 sqlcmd 支持的选项列表，请运行：sqlcmd -?。

使用 sqlcmd 以交互方式运行 Transact-SQL 语句

可以使用实用工具 sqlcmd 以交互方式在命令提示符窗口中执行 Transact-SQL 语句。若要使用 sqlcmd以交互方式执行 Transact-SQL 语句，请在不使用 -Q、 -q、 -Z 或 -i 选项的情况下运行该实用工具，以指定任何输入文件或查询。例如：

sqlcmd -S <ComputerName>\<InstanceName>

在没有输入文件或查询的情况下执行该命令时，sqlcmd连接到指定的SQL Server实例，然后显示一个新行，1>后跟闪烁下划线sqlcmd，这种闪烁下划线被称为提示符。 1 表示这是一条 Transact-SQL 语句的第一行，而 sqlcmd 提示是在您键入 Transact-SQL 语句时将开始的位置。

在 sqlcmd 提示符下，可以键入 Transact-SQL 语句和 sqlcmd 命令，例如 GO 和 EXIT。每个 Transact-SQL 语句都放入名为语句缓存的缓冲区中。键入 GO 命令并按 Enter 后，这些语句将发送到 SQL Server。若要退出 sqlcmd，请在新行的开头键入 EXIT 或 QUIT。

若要清除语句缓存，请键入 :RESET。键入 ^C 会导致 sqlcmd 退出。 ^C 还可以用于在已发出GO命令后停止执行语句缓存。

可以通过输入 ：ED 命令和 sqlcmd 提示来编辑交互式会话中输入的 Transact-SQL 语句。编辑器将打开，编辑 Transact-SQL 语句并关闭编辑器后，修订后的 Transact-SQL 语句将显示在命令窗口中。输入 GO 以运行修订后的 Transact-SQL 语句。

带引号的字符串

用引号引起来的字符无需任何额外的预处理即可使用。例外，输入两个连续的引号可以将引号插入字符串中。 SQL Server 将这种字符序列视作一个引号。（但是，转换发生在服务器中。脚本变量在字符串中出现时不会展开。

例如：

sqlcmd

PRINT "Length: 5"" 7'";

GO

下面是结果集：

Length: 5" 7'

跨越多行的字符串

sqlcmd 支持包含跨多行的字符串的脚本。例如，以下 SELECT 语句跨越多行，但在键入 GO后按 Enter 键时执行单个字符串。

SELECT First line

FROM Second line

WHERE Third line;

GO

交互式 sqlcmd 示例

这是以交互方式运行 sqlcmd 时看到的一个示例。

打开命令提示符窗口时，有一行类似于：

C:\> _

这意味着该文件夹是当前文件夹 C:\ ，如果指定文件名，Windows 将查找该文件夹中的文件。

键入 sqlcmd 以在本地计算机上连接到 SQL Server 的默认实例，命令提示符窗口的内容将为：

C:\>sqlcmd

1> _

这意味着你已连接到 SQL Server 实例， sqlcmd 现在已准备好接受 Transact-SQL 语句和 sqlcmd 命令。在1>之后闪烁的下划线是sqlcmd提示符，它标记了你键入的语句和命令将被显示的位置。现在，键入 USE AdventureWorks2012 并按 Enter，然后按 GO Enter。命令提示符窗口的内容将为：

sqlcmd

USE AdventureWorks2012;

GO

下面是结果集：

Changed database context to 'AdventureWorks2012'.

1> _

按下回车键后，输入USE AdventureWorks2012 发出sqlcmd信号，开始新的一行。按 Enter，键入 GO, 信号 sqlcmd 后，将 USE AdventureWorks2012 语句发送到 SQL Server 实例。 sqlcmd 然后返回一条消息，指示 USE 语句成功完成，并显示一个新 1> 提示作为输入新语句或命令的信号。

以下示例显示了键入 SELECT 语句、 GO 执行 SELECT语句和 EXIT 退出 sqlcmd语句时命令提示符窗口包含的内容：

sqlcmd

USE AdventureWorks2012;

GO

SELECT TOP (3) BusinessEntityID, FirstName, LastName

FROM Person.Person;

GO

下面是结果集：

BusinessEntityID FirstName LastName

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

1 Syed Abbas

2 Catherine Abel

3 Kim Abercrombie

(3 rows affected)

1> EXIT

C:\>

行 3> GO 之后的输出是由 SELECT 语句生成的。生成输出后， sqlcmd 重置 sqlcmd 提示并显示 1>。在行1>中输入EXIT后，命令提示符窗口会显示与第一次打开时相同的行。这表示 sqlcmd 已退出其会话。现在可以再键入一个 EXIT 命令关闭命令提示符窗口。

使用 sqlcmd 运行 Transact-SQL 脚本文件

您可以使用 sqlcmd 执行数据库脚本文件。脚本文件是包含混合 Transact-SQL 语句、 sqlcmd 命令和脚本变量的文本文件。有关如何使用脚本变量的详细信息，请参阅将 sqlcmd 与脚本变量结合使用。 sqlcmd 使用脚本文件中的语句、命令和脚本变量的方式类似于它如何处理以交互方式输入的语句和命令。主要区别在于 sqlcmd ，在不暂停的情况下读取输入文件，而不是等待用户输入语句、命令和脚本变量。

可以通过几种不同的方式创建数据库脚本文件：

可以在 SQL Server Management Studio 中以交互方式生成和调试一组 Transact-SQL 语句，然后将查询窗口的内容保存为脚本文件。
可以使用文本编辑器（如记事本）创建包含 Transact-SQL 语句的文本文件。

例子

答：使用 sqlcmd 运行脚本

启动记事本，然后键入以下 Transact-SQL 语句：

USE AdventureWorks2012;

GO

SELECT TOP (3) BusinessEntityID, FirstName, LastName

FROM Person.Person;

GO

创建一个名为 MyFolder 的文件夹，然后将脚本另存为文件夹 MyScript.sql 中的文件 C:\MyFolder。在命令提示符处输入以下命令以运行脚本，并将输出放入MyOutput.txt内的MyFolder中。

sqlcmd -i C:\MyFolder\MyScript.sql -o C:\MyFolder\MyOutput.txt

在记事本中查看内容 MyOutput.txt 时，你将看到以下内容：

Changed database context to 'AdventureWorks2012'.

BusinessEntityID FirstName LastName

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

1 Syed Abbas

2 Catherine Abel

3 Kim Abercrombie

(3 rows affected)

B. 将 sqlcmd 与专用管理连接配合使用

在以下示例中， sqlcmd 用于使用专用管理员连接（DAC）连接到有阻塞问题的服务器。

C:\>sqlcmd -S ServerName -A

1> SELECT blocked FROM sys.dm_exec_requests WHERE blocked <> 0;

2> GO

下面是结果集：

spid blocked

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

62 64

(1 rows affected)

用于 sqlcmd 结束阻塞过程。

1> KILL 64;

2> GO

C. 使用 sqlcmd 执行存储过程

以下示例演示如何使用 sqlcmd 执行存储过程。创建以下存储过程。

USE AdventureWorks2012;

IF OBJECT_ID ( ' dbo.ContactEmailAddress, 'P' ) IS NOT NULL

DROP PROCEDURE dbo.ContactEmailAddress;

GO

CREATE PROCEDURE dbo.ContactEmailAddress

(

@FirstName nvarchar(50)

,@LastName nvarchar(50)

)

AS

SET NOCOUNT ON

SELECT EmailAddress

FROM Person.Person

WHERE FirstName = @FirstName

AND LastName = @LastName;

SET NOCOUNT OFF

请在提示符 sqlcmd 下输入以下内容：

C:\sqlcmd

1> :Setvar FirstName Gustavo

1> :Setvar LastName Achong

1> EXEC dbo.ContactEmailAddress $(Gustavo),$(Achong)

2> GO

EmailAddress

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

gustavo0@adventure-works.com

D. 使用 sqlcmd 进行数据库维护

以下示例演示如何使用 sqlcmd 进行数据库维护任务。使用以下代码创建 C:\BackupTemplate.sql。

USE master;

BACKUP DATABASE [$(db)] TO DISK='$(bakfile)';

在sqlcmd提示符下输入以下内容：

C:\ >sqlcmd

1> :connect <server>

Sqlcmd: Successfully connected to server <server>.

1> :setvar db msdb

1> :setvar bakfile c:\msdb.bak

1> :r c:\BackupTemplate.sql

2> GO

Changed database context to 'master'.

Processed 688 pages for database 'msdb', file 'MSDBData' on file 2.

Processed 5 pages for database 'msdb', file 'MSDBLog' on file 2.

BACKUP DATABASE successfully processed 693 pages in 0.725 seconds (7.830 MB/sec)

E. 使用 sqlcmd 对多个实例执行代码

某文件中的以下代码表示一个连接到两个实例的脚本。请注意连接到第二个实例之前的 GO。

:CONNECT <server>\,<instance1>

EXEC dbo.SomeProcedure

GO

:CONNECT <server>\,<instance2>

EXEC dbo.SomeProcedure

GO

E. 返回 XML 输出

下面的示例说明了如何以连续流返回未格式化的 XML 输出。

C:\>sqlcmd -d AdventureWorks2012

1> :XML ON

1> SELECT TOP 3 FirstName + ' ' + LastName + ', '

2> FROM Person.Person

3> GO

Syed Abbas, Catherine Abel, Kim Abercrombie,

F. 在 Windows 脚本文件中使用 sqlcmd

sqlcmd命令（例如sqlcmd -i C:\InputFile.txt -o C:\OutputFile.txt,，可以与 VBScript 一起在 .bat 文件中执行）。在这种情况下，请勿使用交互式选项。 sqlcmd 必须安装在正在执行 .bat 文件的计算机上。

首先，创建以下四个文件：

C：\badscript.sql

SELECT batch_1_this_is_an_error  
GO  
SELECT 'batch #2'  
GO

C：\goodscript.sql

SELECT 'batch #1'  
GO  
SELECT 'batch #2'  
GO

C：\rreturnvalue.sql

:exit(select 100)  
@echo off  
C:\windowsscript.bat  
@echo off  

echo Running badscript.sql  
sqlcmd -i badscript.sql -b -o out.log  
if not errorlevel 1 goto next1  
echo == An error occurred   

:next1  

echo Running goodscript.sql  
sqlcmd -i goodscript.sql -b -o out.log  
if not errorlevel 1 goto next2  
echo == An error occurred   

:next2  
echo Running returnvalue.sql  
sqlcmd -i returnvalue.sql -o out.log  
echo SQLCMD returned %errorlevel% to the command shell  

:exit

C:\windowsscript.bat

@echo off  

echo Running badscript.sql  
sqlcmd -i badscript.sql -b -o out.log  
if not errorlevel 1 goto next1  
echo == An error occurred   

:next1  

echo Running goodscript.sql  
sqlcmd -i goodscript.sql -b -o out.log  
if not errorlevel 1 goto next2  
echo == An error occurred   

:next2  
echo Running returnvalue.sql  
sqlcmd -i returnvalue.sql -o out.log  
echo SQLCMD returned %errorlevel% to the command shell  

:exit

然后，在命令提示符处运行 C:\windowsscript.bat：

C:\>windowsscript.bat

Running badscript.sql

== An error occurred

Running goodscript.sql

Running returnvalue.sql

SQLCMD returned 100 to the command shell

G. 使用 sqlcmd 在 Azure SQL 数据库上设置加密

可以在连接到 SQL 数据库数据时执行 A sqlcmd，以指定加密和证书信任。有两个“sqlcmd”选项可用：

客户端使用 -N 开关请求加密连接。此选项等同于 ADO.net 选项 ENCRYPT = true。
客户端使用 -C 开关将其配置为隐式信任服务器证书，而不对其进行验证。此选项等同于 ADO.net 选项 TRUSTSERVERCERTIFICATE = true。

SQL 数据库服务不支持 SQL Server 实例上提供的所有 SET 选项。将相应的 SET 选项设置为 ON 或 OFF 时，下面的选项将引发错误：

SET ANSI_DEFAULTS
SET ANSI_NULLS
SET REMOTE_PROC_TRANSACTIONS
SET ANSI_NULL_DEFAULT

以下 SET 选项不会引发异常，但不能使用。它们已经被弃用：

SET CONCAT_NULL_YIELDS_NULL
设置 ANSI_PADDING
SET 查询控制器成本限制

语法

以下示例适用于 SQL Server Native Client Provider 设置包括的情况：ForceProtocolEncryption = False, Trust Server Certificate = No

使用 Windows 凭据进行连接并对通信加密：

SQLCMD -E -N

使用 Windows 凭据进行连接并信任服务器证书：

SQLCMD -E -C