编写数据库脚本

作者：Andrew Clinick

发表日期：2000 年 1 月 10 日

我在“If It Moves, Script It”（英文）这篇文章中曾谈到，如何使用“Windows Script Host(WSH)”( Windows 脚本主机) 管理 Windows 和 Windows 中的应用程序。文中的大多数示例都是基于管理 Windows 操作系统自身的，并不基于在该操作系统下运行的应用程序。为迎接新千年，我想我应该谈谈，如何在众多显露可脚本化接口的应用程序中使用脚本。这次只涉及“SQL Server”。在以后的几个月中，我将着重谈 Exchange、Office 和“系统管理服务器”。
您可以通过使用“分布式管理对象”、“数据转换服务”和新的“SQL Server XML”实现，将脚本用于“SQL Server”。
许多人都能够通过“Active Data Object(ADO)”和“Active Server Page (ASP)”技术访问数据库了。ADO 在帮助您查询和更新数据库方面做了大量的工作 — 但在备份（Y2K 问题带给我们的警惕）或是数据库之间的传输数据方面，其表现又如何呢？而这时就非涉及到 XML 不可了。

在此，我将告诉您如何通过使用 ADO 的伴随技术 － 特别是“分布式管理对象”、“数据转换服务”和新的“SQL Server XML”实现，将脚本用于“SQL Server”。
分布式管理对象
“分布式管理对象 (DMO)”是一组 COM 对象，它将“SQL Server”数据库和复制管理封装在一起。这意味着您可以编写一个 WSH 脚本，将特定表中的所有数据都复制到用制表符分隔的文件中，这有助于大量数据的移动。我之所以选择这个示例，是因为它的代码编写起来简单，但 DMO 允许您获取“SQL Server”中的每个对象，使您能够编写出一些非常优秀而有意义的管理脚本。

DMO 的关键是 SQLDMO.SQLServer 对象，它是基本的对象，它允许您连接到服务器并获取所有可用对象。在这种情况下，我将使用 Database 集合来选择数据库，然后从 table 集合访问要转储到文件的表。如果不提供数据库，将出现错误消息，并且脚本也就结束了。如果不提供表名，脚本将在数据库所有的表中循环，并导出非系统表。如果提供了数据库，它就导出该表。该示例虽然功能有限，但它为“SQL Server”提供了基于命令行的简单的导出实用程序，您可以以它为根据。

现在先看一段代码：

@#声明与 sql 谈话时使用的变量Dim oServer @# SQL Server 对象Dim oDatabase @# 要使用的目标数据库Dim oBCP @# BCP 对象Dim nRows @# 从 bcp 返回的行数dim table @# 表对象on error resume next@# 第一个参数必须是数据库if WScript.Arguments(0) = "" then WScript.Echo "您没有提供要连接的数据库" WScript.Quitend if@# 创建 SQL DMO 的实例Set oServer = CreateObject("SQLDMO.SQLServer")@# 创建 SQL DMO Bulkcopy 对象的实例Set oBCP = CreateObject("SQLDMO.BulkCopy")oServer.EnableBcp = True@# 登录到本地服务器@# 希望您已经更改了 sa 口令!!oServer.Connect ".", "sa" @# 连接到提供的数据库Set oDatabase = oServer.Databases(Wscript.Arguments(0))@# 将分隔符设置为逗号oBCP.ColumnDelimiter = vbComma@# 将文件类型设置为以逗号分隔oBCP.DataFileType = SQLDMODataFile_CommaDelimitedCharoBCP.ImportRowsPerBatch = 1000oBCP.MaximumErrorsBeforeAbort = 1BCP.RowDelimiter = vbCrLfoBCP.ServerBCPDataFileType = SQLDMOBCPDataFile_CharoBCP.UseExistingConnection = True@# 如果未提供表，则转储所有的表f wscript.Arguments(1) = "" then for each table in oDatabase.tables @# 确保该表不是系统表 if table.systemobject = false then oBCP.DataFilePath = table.name & ".csv" nRows = table.ExportData(oBCP) wscript.Echo nRows & " rows exported from " & table.name end if nextelse @# 设置输出文件 oBCP.DataFilePath = wscript.Arguments(1) & ".csv" nRows = oDatabase.Tables(wscript.arguments(1)).ExportData(oBCP) wscript.Echo nRows & " rows exported from " & wscript.arguments(1)end if

DMO 的全部内容要比本文介绍的多得多，但我希望本文能给您一些感性认识：用某些简单的 WSH 脚本和 DMO 可以做些什么。您可以下载我的示例（英文）。有关 DMO 的详细信息，请访问 http://msdn.microsoft.com/library/psdk/sql/9_dmoc01.htm（英文）。
数据转换服务
导出到 Comma Separated 文件 (CSV)，可以作为将数据导出到 SQL 和从 SQL 导入的起点，但这不象是尖端科技（太过于 20 世纪了，您不觉得吗?）。“SQL Server 7.0”以“数据转换服务 (DTS)”的形式，提供了相当完善的导入和导出机制。幸运的是，脚本在“SQL Server”的这个新领域中仍有一席之地，因此，您可以用“Visual Basic(R) 脚本编辑 (VBScript)”、“JScript(R)”或“Perl”的技术来扩展 DTS 的能力。

DTS 的设置非常简单，特别是在使用“SQL ServerEnterprise Manager”的时候。在默认情况下，“SQL Server”有一个定义的文件夹，可存储任何转换，而且“Enterprise Manager”提供了创建和编辑 DTS 程序包的大的图形用户界面 (GUI)。在下面的示例中，我选择了已由 sqlexport.wsf 文件创建的 employees.csv 文件和“SQL Server”中的一个新表。DTA 程序包将创建该表，加载到 Text 文件中，然后运行某个脚本，将数据转换到“SQL Server”的表中。


DTS 程序包中的转换，使脚本在整个转换过程中保持运行状态。“SQL Enterprise Manager”提供的简单的脚本编辑器，有一个语法分析脚本按钮。在运行脚本之前，该按钮将警告您脚本中的错误。在转换过程中，该脚本使用 VBScript 的 Cint 功能，将 employeeid 和 deptid 的输入转换为 int，并将所有的电子邮件地址转换为小写字母。

@#**********************************************************************@# Visual Basic 转换脚本@# 把每个源列复制到@# 目标列@#***********************************************************************Function Main() DTSDestination("EmployeeID") = cint(DTSSource("Col001")) DTSDestination("FirstName") = DTSSource("Col002") DTSDestination("LastName") = DTSSource("Col003") DTSDestination("email") = lcase(DTSSource("Col004")) DTSDestination("extension") = DTSSource("Col005") DTSDestination("office") = DTSSource("Col006") DTSDestination("DeptID") = cint(DTSSource("Col007")) Main = DTSTransformStat_OKEnd Function

用 DTS，能够采用某些完善的导入/导出机制，并使您能够在转换的任何一步中使用脚本。重要的是，要注意脚本可能不是操作数据的最佳方式 — 尤其是您的数据集很大的话。如果您有大量数据需要转换，而且性能也很重要，则您可能需要考虑使用 Visual Basic 或 C++ 来创建 COM 组件，然后从 DTS 内部调用该组件。也就是说，如果性能并不重要，并且要在数据导入/导出时对它进行转换，则脚本为您提供了实现这一点的灵活机制，并使您能够将所有代码存储到“SQL Server”数据库中，使部署变得相当简单。
关于 XML
目前，XML 看起来像是在应用程序之间共享数据的最佳工具，所以“SQL Server”的所有管理肯定都得到了 XML 的好处。编写提取数据库中所有数据的脚本，以及编写用编程的方法创建 XML 文档的脚本，都是可能的。但是，如果您只要查询“SQL Server”的话，最好使它在 XML 中返回数据，然后用脚本操作该 XML。“SQL Server”组最近发行了“SQL Server XML”技术的非正式版本，完全做到了这一点。

新的 XML 技术使用起来非常简单。实际上是对服务器发出 HTTP 请求，将查询传递给服务器，而服务器返回 XML。为了举例说明，我创建了简单的 WSH 脚本，它向本地机器查询 North Wind 数据库的雇员表中的所有数据。为访问 XML，该脚本使用了“Inte.net Explorer 5.01”附带的 XML 分析程序。此对象的美妙之处，在于它处理您所有的 HTTP 请求，并使您能够同步调用。因为您再也不用处理任何事件驱动的程序，所以，它对编写脚本很有帮助。

该脚本非常简单。它创建了 XML 分析程序的实例，使用分析程序打开 URL，然后将结果保存为 .xml 文件。只需五行的脚本，不错吧!

@# 设置请求的 url xmlpath = "http://localhost/Northwind?sql=select+*+from+employees+for+xml+auto"@# 创建“XML 分析程序”的实例 Set myXMLDoc = CreateObject("Microsoft.XMLDOM") @# 不需要异步 myXMLDoc.async = false @# 加载该 URL myXMLDoc.Load xmlpath @# 保存文档 myXMLDoc.save "employees.xml"

它的强大之处在于，现在可很容易地与服务器建立远程连接并转储数据；只要更改 URL，您早已做过了。此例告诉您如何导出，但是您可以轻松地写出导入例行程序，用 XML 分析程序在 XML 中反复操作并将值插入数据库中。
摘要
“SQL Server”提供全面的可编写脚本的机制，用于本地或远程管理数据库。DMO 和 DTS 已经上市（实际上，DMO 已发行了许多版本），所以您可以直接利用它们，使您的数据库管理任务自动化。Windows 2000 已与“Windows Script Host 2.0”一起上市，所以以上所有脚本的运行，都不受装有“SQL Server”的 Windows 2000 服务器的条件限制。“SQL Server”中新的 XML 技术使存取数据变得轻而易举，从而使编写“SQL Server”的脚本更加容易。有关“SQL Server”的详细信息，请访问 SQL Server Developer enter（英文）。

Andrew Clinick 是“Microsoft Script Technology”组中的程序经理，机会使然，只要涉及脚本，他就可能大显身手一番。他的大部分业余时间都花在观看美国电视台转播的精彩橄榄球赛，以及向他的美国同事解说板球。

 

原文转自：http://www.ltesting.net