2.9 SqlCmd 命令行工具程序

SQL Server 2005 提供了非常多的命令行工具程序，例如大量输出入数据的 bcp.exe、分析性能的 dta.exe、与 SSIS 相关的 dtexec.exe、dtutil.exe、与 Reporting Services 相关的 rs.exe、rsconfig.exe、rskeymgmt.exe等等。我们在此仅介绍新版增加的、利用命令提示符执行 T-SQL 语法的工具程序 SqlCmd.exe。其他的工具程序你可以参考在线帮助“工具与公共程序参考”－“命令提示符公共程序”节点下的内容。

sqlcmd 通过 OLE DB 数据访问界面与 SQL Server 数据引擎沟通，可以让用户互动地执行 SQL 语法，或是指定 T-SQL 脚本文件交互执行，可以周期性在后台批处理地执行，一些日常营运维护的工作将会需要此种方式完成。当你想要利用 SQL Server 2005 保留的“专用管理连接线（Dedicated Administrator Connection DAC）接到 SQL Server 时，也一定要通过 SqlCmd.exe 命令行工具程序。

在先前的版本若 SQL Server 实例的资源用尽，管理员无法利用任何方式创建新的连接以进入 SQL Server 实例做补救的操作，例如，杀掉一些耗资源的连接工作。在这一版可以通过 SqlCmd 使用 SQL Server 2005 保留的“专用管理员连接”，完成一些补救或清除的工作。SQL Server 2005 会为“专用管理员连接”保有专属的资源，让该条连接一定可以成功地访问 SQL Server 实例，但只保有一条连接的资源，所以你不可能同时建立两条“专用管理员连接”。

SQL Server 2005 仍完整支持前版的 osql 工具程序来连接到 SQL Server 2005。不过，必须利用 SQL Server 2005 所提供的 osql 版本来执行新的功能。若你使用 SQL Server 2000 所提供的 osql 访问 SQL Server 2005，则只能执行与旧版兼容的功能，而部分新功能将无法使用。

在通过 SqlCmd 登录与执行 T-SQL 语法之前，有相当多的选择参数可用，你可以在命令提示行输入如下的语法：

SqlCmd/?

以获得如下的参数说明画面：

图2-30 查看 SqlCmd 工具程序所提供的多种参数

从图2-30中看到 SqlCmd 工具程序可接受的参数是大小写有别的，在此举几个常用的参数列表说明如表2-3所示，至于完整的帮助，你可以参照在线帮助的“工具和公用程序参考”－“命令提示符公用程序”－“sqlcmd 公用程序”节点：

表2-3 SqlCmd 经常搭配使用的参数

参数与设置格式	说明
?	显示如图2-30 的简单说明画面
A	利用SQL Server 2005为管理者保留的“专用管理员连接（DAC）”来登录。
d数据库名	使用SQL Server信任的Windows账号来登录，不需要设置用户账号/密码，默认采用此选项。
E	使用 SQL Server 信任的 Windows 账号登录需设置用户账号/密码，默认采用此选项。
i 输入文件名[, 文件名2...]	设置包含SQL 表达式的纯文本文件，通过 SqlCmd 执行。可以同时指定多个文件，文件名称之间不能有空格。SqlCmd 会依照顺序来读取和处理，并在执行前先检查指定的文件是否全部存在。如果有一个以上文件不存在，sqlcmd 会结束操作。
O输出文件名	接收 sqlcmd 输出的文件。如果有指定文件 u参数，输出文件会以 Unicode 格式存储。若文件名称包含空格，必须用引号括住。如果定义了无效的文件名称，则会产生错误信息并退出执行。
P 密码	搭配 U 所指定的帐号所使用的密码。如果没有指定 -U 和 -P 这两个选项，sqlcmd 会利用当前用户的 Windows 账号来连接 SQL Server 实例。
Q “SQL 语法”	功能与下面的 q 相同，只是执行完查询后会自动关闭 sqlcmd 应用程序。
q “SQL 语法”	启动 sqlcmd 连线后连接查询，但查询完成不退出 sqlcmd。你可以简单地直接执行如下的语法： sqlcmd –q “select * from Northwind.dbo.Customers” 这代表以 SQL Server 信任的Windows 账号登录本机的默认实例，直接执行 Northwind 数据库内的 Customers 数据表查询。
S 服务器名[\实例名]	指定要连接SQL Server 实例，如果未指定此选项sqlcmd 会连接本机的默认实例。
U SQL_Server_账号	指定 SQL 自身的登录账号
v 变量名称=“值” [ 变量名称=“值”...]	定义 sqlcmd 所载装的脚本文件所定义的变量。如果设置值包含字元，则需要用引号括住该值。可同时指定多个变量= “值” 的配对。如果指定的任何值发生错误，sqlcmd 会产生报错信息并退出执行。

进入到 SqlCmd 公用程序后，会以数字编号搭配大于符号（＞）当作命令提示符。顺序创建的数字代表你曾经下了几个命令，而 GO 命令会把累积下来可执行的 SQL 命令传递到服务器端，并返回结果。简单的使用范例如图2-31所示。

图2-31 通过 SqlCmd 工具程序连接 SQL Server 实例并执行命令

在图2-31中，笔者先通过 sqlcmdeditor 环境变量设置 SqlCmd 工具程序可以调用的文字编辑程序，接着执行 SqlCmd 工具程序，以集成验证的方式登录本机默认实例后，先下一个 SQL 命令：

use northwind

接着通过 ED 命令，可以打开先前以环境变量设置的 Notepad 编辑程序。在 Notepad 内编写如图2-31中，位于 ED 之后的 SQL 命令，保存退出后 SqlCmd 会将文件的内容重新读回到命令提示行上。最后利用 GO 命令将先前编写但存放在缓冲区内的语法一起传给 SQL Server 实例。

上述范例中除了在命令提示符执行 SQL 语法外，还可以调用该工具程序提供的一些命令，而通常这些命令前需要加上冒号（：），有部分命令为了与之前版本 osql 兼容，可以省略冒号。在表2-4中以中括号括起冒号的，就是使用时冒号可省略的命令。

这些命令必须在行首，且以个别单行执行，命令后面不能有 T-SQL 或其他命令。而这些命令都不分大小写。表2-4列出常用的命令：

表 2-4 常用的 SqlCmd 所提供之功能指令

命令格式	说明
[:] !!<命令提示行命令>	执行作操作系统提示行命令或可执行文件。如 !! dir C:\
:Connect 服务器[\实例名称] [-l 连接超时] [-U SQL Server 账号 [-P 密码]]	关闭目前的连接，重新登录到其他的 SQL Server 实例。
[:] ED	启动文字编辑器，以编辑目前在缓冲区中的批处理文件，或之前执行过的批处理文件。若要编辑前次执行过的批处理文件，须在该批处理文件执行完成之后立刻输入 ED 命令。文字编辑器由SQLCMDEDITOR 系统环变量来定义。默认是古老的 edit.com[1]。
GO [n]	将缓存的表达式以单一批处理文件一起传递到 SQL Server 实例行 n 次。
:Help	列出各 sqlcmd 命令的简单说明。
[:] RESET	清除之前已经缓存的表达式。
:Serverlist	列出在网络上可获得的 SQL Server 服务实例的名称。
[:] QUIT	结束并退出SqlCmd 工具程序

另外，SqlCmd 支持 T-SQL 语法文件包容变量，让你可以有弹性地重复使用 T-SQL 语法文件，这是以往 osql 工具程序办不到的。在 T-SQL 语法文件中可以下述的格式定义变量：

$（变量名称）

我们写一段简单的 T-SQL 语法如下，其中定义了两个变量 colname 和 tabname：

USE Northwind

SELECT $（colname）

FROM $（tabname）

另外用 Windows 命令底层（Command Shell）的指令语法编写批次文件来测试，范例程序代码2-4所示：

程序代码列表 2-4 通过底层的指令语法编写批次文件测试 SqlCmd 工具程序

@ECHO OFF

CLS

@ECH通过以下的 SqlCmd 选项执行

@ECHO i 输入 sql 文件名

@ECHO o 输出结果文件名

@ECHO 设置参数内容

@ECHO E 综合式验证登录

@ECHO 设置服务实例

@ECHO __________________________________________________

IF DEFINED Instance GOTO Instance

@ECHO －－－－－－－－－－－－连接到本机默认实例－－－－－－－－－－－－－－

@ECHO sqlcmd －isqlcmd.sql －oCustomers.log －vcolname=“CompanyName,ContactName” tabname=“Customers” －E

sqlcmd －isqlcmd.sql －oCustomers.log －vcolname=“CompanyName,ContactName” tabname=“Customers” －E

@ECHO ERRORLEVEL: %ERRORLEVEL%

PAUSE

@ECHO __________________________________________________

@ECHO －－－－－－－－－－－－－－－－－－－－用环境变量－－－－－－－－－－－－－－－－－－－－

@ECHO SET colname=*

@ECHO SET tabname=Orders

@ECHO sqlcmd －i sqlcmd.sql －oOrders.log －E

SET colname=*

SET tabname=Orders

sqlcmd －i sqlcmd.sql －oOrders.log －E

@ECHO ERRORLEVEL: %ERRORLEVEL%

GOTO End

:Instance

@ECHO －－－－－－－－－－－－－－连接到指定实例：%Instance%－－－－－－－－－－－－－－

@ECHO sqlcmd －S%Instance% －isqlcmd.sql －oCustomers.log －vcolname=“CompanyName,ContactName” tabname=“Customers” －E

sqlcmd －S%Instance% －isqlcmd.sql －oCustomers.log －vcolname=“CompanyName,ContactName” tabname=“Customers” －E

@ECHO ERRORLEVEL: %ERRORLEVEL%

PAUSE

@ECHO __________________________________________________

@ECHO －－－－－－－－－－－－－－－－－－－－用环境变量－－－－－－－－－－－－－－－－－－－－

@ECHO SET colname=*

@ECHO SET tabname=Orders

@ECHO sqlcmd －S%Instance% －i sqlcmd.sql －oOrders.log －E

SET colname=*

SET tabname=Orders

sqlcmd －S%Instance% －i sqlcmd.sql －oOrders.log －E

@ECHO ERRORLEVEL: %ERRORLEVEL%

:End

@ECHO 执行完毕

PAUSE

在范例中一开始便利用底层指令的 IF DEFINED 语法来测试我们自行定义的 Instance 环境变量是否存在，以决定程序逻辑的走向。

在范例中，通过 -i 菜单指定先前编写并保存的 T-SQL 指令文件 sqlcmd.sql，并以 -o 选项设置将执行结果输出至存放的文本文件。以 -E 选项设置登录验证的方式采用 Windows综合验证。由于笔者的机器上有两个 SQL Server 实例，因此再通过自行定义的 Instance 环境变量来决定连接的实例，若有定义 Instance 环境变量，则需要再利用 -S 选项设置连接的实例名称。

当然，整个范例中最大的特色是如何设置 T-SQL 指令文件中的变量部分，程序代码列表2-4 分别列举了两种方式，一是通过 -v 选项搭配变量名称以及值的配对来设置，另一种是取与 T-SQL 指令文件中变量相同名称的系统环境变量，赋予值后直接调用 sqlcmd 装载该指令文件即可。

SqlCmd 程序执行完毕后，可以查看系统环境变量 ERRORLEVEL，若返回值为 0 代表执行结果无误，若值不等于 0，则代表有错误发生，程序代码列表2-4的执行结果如图2-32所示：

图2-32 程序代码列表2-4 的执行结果

由于笔者在程序代码列表2-4中使用了底层命令的 Pause，因此你在执行过程中会需要按下任意键后才会继续执行。执行完毕后，你可以通过 Notepad 工具程序打开 Customers.log 和 Orders.log 两个普通文本文件，观察通过变量设置 T-SQL 语法内容的执行结果。

通过如上的批次执行方式，你可以在安装或维护日常 SQL Server 数据库时，周期地以后台的方式执行，而后查看执行记录来得知维护工作的结果。

最后，若你有兴趣看看被系统隐藏起来的对象，可以在任意的数据库内执行如下的语法：

select * from sys.objects where type='s'

将会发现一大堆系统数据表名称，其 schema 名称为 sys。但在一般的连接内，都无法看到这些数据表，若直接查询会得到如下的报错信息：

信息 208，级别 16，状态 1，行 1

无效的对象名称 'sys.sysfiles1'。

这时，你就可以通过 SqlCmd 工具程序搭配 -A 参数连接到 SQL Server 服务实例，便能够查看这些系统数据表。

另外，不晓得你是否注意到 SQL Server 服务实例启动时，所输出的 Log 会有一行如下的信息：

2005－12－23 14:05:52.75 spid5s Starting up database 'mssqlsystemresource'.

当你到存放数据库文件的目录下，默认的路径：

C:\Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\Data

也会看到 mssqlsystemresource.mdf 和 mssqlsystemresource.ldf 两个数据库文件。但在任何环境都看不到这个系统数据库，包括普通单纯的 SqlCmd -A 模式也不行。

SQL Server 2005 新增了一个存放系统对象的数据库，如系统视图表、存储过程等，其名称叫 mssqlsystemresource。由于系统对象统一放在这个数据库内，再对应给其他数据库使用，因此以后改版，上 Service Pack 时，只要换掉这个数据库即可。

若你有兴趣研究该数据库，可以先将 SQL Server 服务实例停止执行，而后复制mssqlsystemresource.mdf 和 mssqlsystemresource.ldf 两个数据库文件。在此，笔者分别命名为 Copy_Resource.mdf 和 Copy_Resource.ldf，而后重新启动 SQL Server 服务实例，并通过如程序代码列表2-5的 T-SQL 语法将该复制的数据库文件附加成另一个数据库，以后就可以研究其内部了 J

程序代码列表2-5 附加资源数据库以供研究各种系统对象的设计方式

DECLARE @path NVARCHAR（200）,@mdf NVARCHAR（200）,@ldf NVARCHAR（200）

SELECT @path=REPLACE（filename,'master.mdf',''） FROM sys.sysfiles WHERE fileid=1

SET @mdf=@path + N'Copy_Resource.mdf'

SET @ldf=@path + N'Copy_Resource.ldf'

EXEC sp_attach_db 'CopyResource',@mdf,@ldf

【查看所有评论(0)条】