使用 SQL Server 2000 中的 VBScript 接口能够转换数据并向用户提供报告

核心提示：本文假设读者熟悉 sql server(WINDOWS平台上强大的数据库平台) 2000 和 VBScriptLevel of Difficulty 123 下载本文的代码：VBScriptAndSQLServer2000.exe (38 KB)摘要应用程序服务提供商通常必须自动向客户端发送信息，而不是按需发送，使用

本文假设读者熟悉 sql server(WINDOWS平台上强大的数据库平台) 2000 和 VBScriptLevel of Difficulty 123 下载本文的代码：VBScriptAndSQLServer2000.exe (38 KB)摘要应用程序服务提供商通常必须自动向客户端发送信息，而不是按需发送。例如，制造商可能希望知道某个连锁零售店每天卖出了多少他们的产品。尽管 sql server(WINDOWS平台上强大的数据库平台) 非常适合维护这种类型的数据库，但是您必须编写脚本，以便以客户端友好的格式从中获取数据。在本文中，您将看到如何使用数据转换服务（DTS，sql server(WINDOWS平台上强大的数据库平台) 中一个功能强大的工具），从 sql server(WINDOWS平台上强大的数据库平台) 2000 自动执行数据检索和格式设置，并使得向用户推入数据这一过程变得更加容易。

“推”又被称作 Web 强制转换，它自动执行对数据的搜索和检索。它是由 Web 或数据库服务器（而非用户）启动的。根据所定义的某个标准，无论“推”应用程序何时何地进行定向（经常是通过电子邮件直接定向到用户的桌面），它都将自动搜索数据库并提供所需的信息。这不仅能够方便地接收用户以其他方式可能无法轻松获取的重要信息，而且还大大改变了日常业务的运作方式。

“推”应用程序通常有两种形式。第一种类型包括电子邮件、邮寄名单和直邮服务，这种类型通常是由大型新闻和数据库组提供的。“推”应用程序还使得开发人员和用户能够创建配置文件并记录他们的首选项，以便他们能够从多个来源接收相关信息。我们在本文中描述的示例应用程序基于第一种类型的“推”应用程序。但是，我们的应用程序比较通用，同时还足以支持自定义分析功能。

如果各个机构需要集中和合并数据，则可以使用 sql server(WINDOWS平台上强大的数据库平台)? 2000 中的数据转换服务 (DTS) 工具，从不同的来源检索数据并将检索到的数据转换为既定渠道的目标。使用这些工具，可以执行简单的一次性数据转换，也可以开发复杂的、受工作流驱动的软件包。DTS 工具还提供一个图形用户界面和一个可相对容易进行编程的对象模型。 DTS 软件包由连接、任务、转换和工作流约束组成。每个软件包都可以包含一个或多个步骤或任务，它们可在该软件包运行时按顺序或并行执行。在执行时，该软件包连接到正确的数据源，复制数据和数据库对象、转换数据并将发生的事件通知用户或进程。您可以对软件包进行编辑、密码保护、安排执行时间并按版本检索。使用 DTS 设计器工具（将在本文的后面部分进行描述）可以方便地创建软件包。使用 VBScript 或 JScript? 可以创建一个任务，以便执行 DTS 中的其他任务中没有的功能。例如，可以：

?	创建和使用 ADO 连接、命令、记录集和其他对象，以便访问和操作数据
?	使用函数、子例程和 COM 对象设置数据格式并转换数据
?	创建、使用和修改存储在 DTS 全局变量和 ActiveX? script 常量中的值
?	集成其他 DTS 任务和工作流

示例应用程序我们的示例应用程序将展示如何使用 VBScript 任务来执行其他 DTS 任务中没有的功能，以及 VBScript 脚本如何能够作为已安排任务来运行。由于我们的主要目的是介绍在 sql server(WINDOWS平台上强大的数据库平台) 2000 中 VBScript 的功能，因此我们将不讨论 DTS 全局变量、工作流或者与其他任务的集成。图 1概述了在开发示例 DTS 应用程序时所遵循的步骤。DTS 软件包中的 VBScript 任务定期向作者发送有关其图书销售情况信息的电子邮件。这些数据的格式被设置为用户友好的报告。我们将 sql server(WINDOWS平台上强大的数据库平台) 2000 中的 Pubs 数据库用作数据源，但是此方法非常通用，可适用于任何数据模型。

(WINDOWS平台上强大的数据库平台) 2000 中的 VBScript 接口能够转换数据并向用户提供报告（图一）" />

图 1 工作流

 

第一步是用作者的电子邮件地址填充电子邮件字段。为此，我们选择了 Pubs 数据库并通过添加一个大小为 20 的 varChar 字段扩展了 authors 表，并将该字段命名为 au_email，如图 2 所示。我们假设在生产应用程序中一个单独的 GUI 或进程将负责填充该字段，但是为了演示的目的，您可以手动输入这些地址。

(WINDOWS平台上强大的数据库平台) 2000 中的 VBScript 接口能够转换数据并向用户提供报告（图二）" />

图 2 添加E-mail字段

 

接着，我们在 sql server(WINDOWS平台上强大的数据库平台) 2000 中创建了一个新的本地 DTS 软件包。要打开“DTS 设计器”以创建该软件包，需要在 sql server(WINDOWS平台上强大的数据库平台) 中启动“企业管理器”，在控制台树中右击“数据转换服务”，然后选择“新建包”，如图 3所示。使用“DTS 设计器”GUI，通过拖放方法并填写组成软件包的各个 DTS 对象的属性表，可以构建和配置软件包。

(WINDOWS平台上强大的数据库平台) 2000 中的 VBScript 接口能够转换数据并向用户提供报告（图三）" />

图 3 创建新的软件包

 

第三步是添加 VBScript 任务，方法是将 ActiveX 脚本任务从任务工具栏拖放到设计图面，如图 4所示。可以将任务的名称从“ActiveX 脚本任务：未定义”更改为适用于您的应用程序的名称。

(WINDOWS平台上强大的数据库平台) 2000 中的 VBScript 接口能够转换数据并向用户提供报告（图四）" />

图 4 ActiveX 任务属性

 

下一步是在 VBScript 任务中开发一些函数，以便检索数据、转换数据、将数据通过电子邮件发送给用户。为了便于浏览示例应用程序，只需复制示例代码（请参阅本文顶部的链接）中的函数。在“ActiveX 脚本任务属性”窗口中，确保语言设置为“VBScript 语言”。可通过单击“语言”按钮来设置语言（请参阅图4）。然后将源代码复制到设计器中，删除在设计器窗口中看到的默认 Main 函数代码。 您需要更改 sql server(WINDOWS平台上强大的数据库平台) 中的常量，使得该示例应用程序适用于您的情况，因为这些常量会发生变化（后面将对此进行解释）。确保在完成时保存任务，如图 5所示。我们将软件包命名为 BookSales。

(WINDOWS平台上强大的数据库平台) 2000 中的 VBScript 接口能够转换数据并向用户提供报告（图五）" />

图 5 保存软件包

 

您可以通过多种方法来运行示例应用程序。首先，单击“分析”按钮，确保没有任何语法错误。此按钮位于“ActiveX 脚本任务属性”窗口中。然后，在“DTS 设计器”的顶部，单击工具栏中的“Go”按钮。或者，可以通过右击所选任务，并从上下文菜单中选择“执行”步骤来执行该任务。还可以在“企业管理器”中执行 BookSales 软件包，如图 6所示。在成功完成任务时，电子邮件（请参阅图 7）将发送给作者。

(WINDOWS平台上强大的数据库平台) 2000 中的 VBScript 接口能够转换数据并向用户提供报告（图六）" />

图 6 运行 BookSales

 

通过选择“调度包”菜单项可以自动安排 BookSales 软件包的运行时间，如图 6所示。输入必需的频率和持续时间参数。

(WINDOWS平台上强大的数据库平台) 2000 中的 VBScript 接口能够转换数据并向用户提供报告（图七）" />

图 7 电子邮件调度

 

为了报告定单号、数量、付款条件和标题，BookSales 软件包将从 2002 年 1 月 1 日开始，在每天夜里 11:00 点运行。请注意，必须加载 sql server(WINDOWS平台上强大的数据库平台) 代理并将其配置为安排 DTS 软件包的运行时间。在“企业管理器”中“管理|sql server(WINDOWS平台上强大的数据库平台) 代理|作业”下，应当看到 BookSales 软件包以作业形式出现。VBScript 代码所有的示例代码都位于 sourcecode.txt 文件中，该文件可以随本文一起下载。在详述了需要设置的常量并快速浏览 Main 函数之后，我们将介绍代码如何管理销售数据、访问数据库、设置记录集的格式以及发送报告。下面的三个常量必须根据系统和网络设置进行调整：

>Const SMTP_SERVER = "exchange.afs-link.com"<}0{>Const SMTP_SERVER = "exchange.afs-link.com"<0>Const SENDER_E-MAIL = """Book Sales Reporting Service""" & _<>Const SENDER_E-MAIL = """Book Sales Reporting Service""" & _<>"<><0>Const DB_CONNECT_STRING = "Provider=SQLOLEDB.1;Data " & _<>Const DB_CONNECT_STRING = "Provider=SQLOLEDB.1;Data " & _<>"Source=(local);Initial Catalog=Pubs;user id = 'sa';password='"<>"Source=(local);Initial Catalog=Pubs;user id = 'sa';password='"<

第一个常量 Const SMTP_SERVER = "exchange.afs-link.com" 是 SMTP 服务器，即邮件服务器的 DNS 名称。电子邮件发件人函数需要使用此信息来发出电子邮件。网络管理员将了解到该设置。常量 SENDER_E-MAIL = ""Book Sales Reporting Service" " 是发件人的电子邮件地址。它通常是类似于 system@domainname.com 的地址。此常量的第一部分 (Book Sales Reporting) 允许您键入任何文本；这通常是发送每日销售报告的部门的名称。实际的电子邮件地址输入时必须包含在 <> 中。同样，网络管理员应当能够告诉您在此处输入什么内容。最后一个常量是数据库连接字符串，它提供连接到数据库所必需的全部信息。ADO 连接字符串看上去如下所示：

Const DB_CONNECT_STRING = "Provider=SQLOLEDB.1;Data " & _"Source=(local); Initial Catalog=Pubs;user id = " & _"'sa';password='"

我们假设读者基本掌握 ADO，因此我们只简要概述了连接字符串各部分的含义。 该字符串的数据库驱动程序部分为“Provider=SQLOLEDB.1;”，它定义连接到数据库所必需的 ODBC 驱动程序的类型。我们使用的是 sql server(WINDOWS平台上强大的数据库平台)；如果您的应用程序使用的不是 sql server(WINDOWS平台上强大的数据库平台)，则可以在 asp">Microsoft 开放式数据库连接中查找必需的 ODBC 驱动程序信息。Data Source 指定要搜索的服务器所在的位置。由于 sql server(WINDOWS平台上强大的数据库平台) 位于本地计算机上，因此我们指定了 "local"。您可能需要指定数据库所在计算机的 IP 地址或名称。Initial Catalog 包含数据库的名称；在我们的代码中，这是 sql server(WINDOWS平台上强大的数据库平台) 随附的 Pubs 数据库。最后，User Id 指定用户名和用户密码。在默认情况下，ActiveX 任务的入口点函数的名称为 Main，但是您可以更改它。Main 函数只包含两行。首先，它调用 Process_Daily_Sales 子例程，然后它返回 ActiveX 脚本常量 (DTSTaskExecResult_Success)。在图 8所示的“包对象浏览器”中，可以查看所有的项目常量和全局变量。

(WINDOWS平台上强大的数据库平台) 2000 中的 VBScript 接口能够转换数据并向用户提供报告（图八）" />

图 8 项目常量

 

这些常量和全局变量可用于控制 DTS 软件包中各个步骤的执行。为简化起见，我们将不详述这些常量和全局变量。有一点很重要，那就是 Main 函数之所以会以 DTSTaskExecResult_Success 形式返回 ActiveX 脚本常量，这是因为我们的示例只是一个由单个步骤组成的 VBScript 任务。管理销售数据Process_Daily_Sales 函数是应用程序的核心内容。它以记录集格式检索今天已售出其图书的作者列表。然后它将该记录集的格式设置为 HTML 表，最后它采用这个 HTML 表并将其通过电子邮件发送给每个相应的作者。此函数有三个局部变量：两个 ADO 记录集和一个日期：

Dim rstAuthorsDim rstSalesDim Todays_Date

因为并非所有的 Pubs 数据库都使用今天的销售数据进行填充，因此下面的两行代码会生成从 1994 年 9 月 14 日开始的报告，之所以选择该日期，是因为它适用于几乎所有的 Pubs 数据库。我们只是取消注释要处理的代码行并注释其他行：

'Todays_Date = "'" & Date() & "'"Todays_Date = "'9/14/1994'"

接着，我们获取作者和销售信息。通过使用简单的 SQL 联接，我们选择其图书已在今天被订购的作者：

strAu_Sales = "Select Distinct Authors.* from " & _"Authors,Sales, TitleAuthor Where " & _"TitleAuthor.au_id = Authors.au_id   and " & _"TitleAuthor.Title_id = Sales.Title_id   and " & _"Sales.ord_date = " & Todays_Date

现在，用以下调用将 SQL 语句转换成 ADO 记录集：

Set rstAuthors = ExecuteSQL(strAu_Sales)

接着，在循环访问以便向作者发送通知之前，确保 rstAuthors 实际上不为空：

If Not (rstAuthors.Eof and rstAuthors.Bof ) ThenWhile Not rstAuthors.Eof

从各个表（如 Stores、Sales、TitleAuthors 和 Titles）提取其他信息，如下定单的商店、定单号、数量、付款条件和图书标题。正如您所看到的那样，这是一个简单联接：

strAu_Sales = "SELECT distinct stores.stor_name as [Store Name], " & _"sales.ord_num as [Order Number], sales.qty as [Quantity], " & _"sales.payterms as [Pay Terms], Titles.Title FROM Stores, Sales, " & _"TitleAuthor, Titles " & "Where TitleAuthor.au_id = '" & _"rstAuthors("au_id")  &  "' and Sales.ord_date = " & Todays_Date " & _" & "and Sales.Title_id = Titles.Title_id  and sales.stor_id = " & _"stores.stor_id "

通过调用 ExecuteSQL 语句将 SQL 语句转换为 ADO 记录集：

  Set rstSales = ExecuteSQL(strAu_Sales )

现在，我们用作者的姓名和地址构造消息的正文。之所以使用 HTML 标记 ，是因为我们会将消息以 HTML 形式嵌入到电子邮件中：

strTable = rstAuthors("au_fname") & " " &rstAuthors("au_lname") & "" & rstAuthors("Address")& "" & rstAuthors("city") & ", " &rstAuthors("state") & " " & rstAuthors("Zip")

现在，消息的正文已构造完毕，通过包括以前提取的销售信息，用嵌入的 HTML 表扩展此正文。FormatRecordset 会将 rstSales ADO 记录集转换为 HTML 表：

strTable = strTable & FormatRecordset(rstSales)

随后调用 send_e-mail 函数，该函数接受主题、作者的电子邮件地址和 HTML 消息正文：

Call send_e-mail("Book Sales Report For: " & _& Todays_Date, rstAuthors("au_email"), strTable)

然后继续下一个作者：

rstAuthors.movenextWend

Execute_SQL 函数直接连接到数据库。在该函数内部，我们访问数据库，执行由调用方提供的 SQL 字符串，返回结果（如果有的话）。首先创建一个 ADO 连接：

Set myConn = CreateObject("ADODB.Connection")

然后创建一个 ADO 记录集：

set myRecordset = CreateObject("ADODB.Recordset" )

在下一步中，使用 DB_CONNECT_STRING 常量打开该连接：

myConn.Open = DB_CONNECT_STRING

在该连接打开之后，使用该连接和 SQL 打开此记录集：

myRecordset.Open MySQL(和PHP搭配之最佳组合)CmdText, myConn

最后，返回打开该记录集的结果：

Set ExecuteSQL = myRecordset

如果您具有一些 ADO 使用经验，将会明白所有这些步骤都非常简单。FormatRecordset 函数以参数形式接受 ADO 记录集，并（以字符串变量形式）返回 HTML 表。它有两个循环 ?? 一个循环嵌套在另一个循环中。首先，它逐句通过字段列表，然后依次循环通过各行。这些迭代用于将记录集转换为一个 HTML 表。

该 HTML 表是作为一个字符串创建的，而且有一个简单表是按如下方式定义的：

strTable = "

随后转到第一个记录并创建带有 标记的表行：

rstTable.MoveFirststrTable = strTable & ""

然后循环访问各个字段并将名称添加到颜色漂亮的 标记中：

For Index = 0 To rstTable.Fields.Count - 1strTable = strTable & "<" & _ "font color='white'>" strTable = strTable & _ rstTable.Fields.Item(Index).Name strTable = strTable & "</TD>"Next

标记是闭合标记；它对表的标题进行标记：

strTable = strTable & ""

现在，我们继续记录集的数据部分，以填充 HTML 表的其余部分。我们依次循环通过所有的记录并填充 HTML 表的 和标记：

While (Not rstTable.EOF)strTable = strTable & ""For Index = 0 To rstTable.Fields.Count - 1strTable = strTable & "" strTable = strTable & _ rstTable(rstTable.Fields.Item(Index).Name).Value strTable = strTable & "" strTable = strTable & ""NextstrTable = strTable & ""rstTable.MoveNextWend

最后，该表完成，函数值将作为字符串变量类型返回：

strTable = strTable & "

随后转到第一个记录并创建带有 标记的表行：

rstTable.MoveFirststrTable = strTable & ""

然后循环访问各个字段并将名称添加到颜色漂亮的 标记中：

For Index = 0 To rstTable.Fields.Count - 1strTable = strTable & "<" & _ "font color='white'>" strTable = strTable & _ rstTable.Fields.Item(Index).Name strTable = strTable & ""Next

strTable = strTable & ""

现在，我们继续记录集的数据部分，以填充 HTML 表的其余部分。我们依次循环通过所有的记录并填充 HTML 表的 和 标记：

While (Not rstTable.EOF)strTable = strTable & ""For Index = 0 To rstTable.Fields.Count - 1strTable = strTable & "" strTable = strTable & _ rstTable(rstTable.Fields.Item(Index).Name).Value strTable = strTable & "" strTable = strTable & "

 "FormatRecordset = strTable

发送报告Send_Mail 函数用于将报告发送给作者。请注意，此函数中使用的是 SMTP_SERVER 和 SENDER_E-MAIL 常量。Send_Mail 使用 Microsoft? Collaboration 数据对象，这些对象有时是指 CDO 2.0 或 CDOSYS.DLL。CDO 为在 Windows? 2000 上开发消息处理应用程序提供一个对象模型。CDOSYS 基于 SMTP 和 NNTP 标准并且可用作随 Windows 2000 Server 安装的系统组件。它是用来在 Windows 2000 Server 上构建批量邮件传递和基于 Web 的消息处理应用程序的标准 API。Send_Mail 函数有三个参数：subject、rcpt 和 msgHTML。Subject 是所发送电子邮件的主题，rcpt 是收件人的电子邮件地址，msgHTML 是邮件的 HTML 正文。报告以 HTML 格式发送给作者。Send_Mail 函数使用两个常量。cdoSendUsingPickup 常量表明消息应当使用本地 SMTP Service pickup 目录来发送。cdoSendUsingPort 常量表明消息使用网络（通过网络的 SMTP）发送。如果本地计算机上装有 SMTP 服务，则该常量的值默认为 cdoSendUsingPickup。否则，如果本地计算机上装有 Outlook? Express，则该常量的值默认为 cdoSendUsingPort，并且使用默认帐户的设置。在本文中，我们使用 cdoSendUsingPickup。接着，我们使用 COM 来创建 Message 和 Configuration 对象，如下所示：

set iMsg = CreateObject("CDO.Message")set iConf = CreateObject("CDO.Configuration")

Configuration 有几个字段。在设置这些字段的值之前，它们都通过如下代码链接到该配置：

Set Flds = iConf.Fields

http://schemas.microsoft.com/cdo/configuration/ 命名空间定义用于设置各种 CDO 对象配置的大多数字段。我们设置和更新 Configuration 对象的以下三个字段（SendUsing、SMTP_SERVER 和 TimeOut）：

With Flds.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = _cdoSendUsingPickup.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = _SMTP_SERVER.Item("http://schemas.microsoft.com/cdo/configuration/ " & _"smtpconnectiontimeout") = 10.UpdateEnd With

最后，在发送消息之前，用刚才定义的配置和其余电子邮件信息对消息进行配置：

With iMsgSet .Configuration = iConf.To = rcpt.From = SENDER_EMAIL.Subject = subject.HTMLBody = msgHTML.SendEnd With

结论

在本文中，我们已经介绍了如何使用 sql server(WINDOWS平台上强大的数据库平台) 2000、VBScript、ADO、CDO 和 DTS 软件包来实现推入技术。我们的示例非常简单，但是功能很强大。要对它进行扩展，可以使用复杂的数据模型和外部报告组件来向用户发送更丰富的报告。还可以将其他 DTS 任务集成到该 VBScript 任务中。此技术可用于将各种各样的信息（包括及时的财务报表、所采购商品的定单状态、行业更新或所需的任何其他数据）推向用户。