SQL Server 2000 實戰：用 VBScript + DTS 自動化資料轉換與郵件報表推送

背景與目標

應用服務提供者常需「主動推送」資料給客戶，而非等待客戶端請求。本文示範如何結合 SQL Server 2000 的 資料轉換服務 (DTS)、VBScript (ActiveX Script)、ADO 與 CDO (Collaboration Data Objects)，自動從 Pubs 範例資料庫擷取銷售資料、轉換為 HTML 表格，並透過 SMTP 寄送每日銷售報表給作者。

架構概覽

1. 擴充 authors 資料表，新增 au_email 欄位儲存收件信箱
2. 在企業管理器建立本地 DTS 套件 BookSales
3. 加入 ActiveX Script Task，撰寫 VBScript 執行核心邏輯
4. 設定排程讓 SQL Server Agent 定時執行套件

核心 VBScript 流程

' 常數設定（需依環境修改）
Const SMTP_SERVER = "exchange.afs-link.com"
Const SENDER_EMAIL = "Book Sales Reporting Service <system@domain.com>"
Const DB_CONNECT_STRING = "Provider=SQLOLEDB.1;Data Source=(local);Initial Catalog=Pubs;user id='sa';password='***'"

' 主程式
Function Main()
    Process_Daily_Sales
    Main = DTSTaskExecResult_Success
End Function

' 核心處理
Sub Process_Daily_Sales()
    Dim rstAuthors, rstSales, Todays_Date
    ' 測試用固定日期
    Todays_Date = "'9/14/1994'"

    ' 1. 找出今日有銷售的作者
    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
    Set rstAuthors = ExecuteSQL(strAu_Sales)

    If Not (rstAuthors.EOF And rstAuthors.BOF) Then
        While Not rstAuthors.EOF
            ' 2. 取得該作者的銷售明細
            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"
            Set rstSales = ExecuteSQL(strAu_Sales)

            ' 3. 組裝 HTML 郵件內文
            strTable = rstAuthors("au_fname") & " " & rstAuthors("au_lname") & "
" & _
                       rstAuthors("address") & "
" & _
                       rstAuthors("city") & ", " & rstAuthors("state") & " " & rstAuthors("zip")
            strTable = strTable & FormatRecordset(rstSales)

            ' 4. 寄送郵件
            Call Send_Mail("Book Sales Report For: " & Todays_Date, _
                           rstAuthors("au_email"), strTable)

            rstAuthors.MoveNext
        Wend
    End If
End Sub

' 執行 SQL 回傳 ADO Recordset
Function ExecuteSQL(sqlCmd)
    Dim conn, rs
    Set conn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    conn.Open DB_CONNECT_STRING
    rs.Open sqlCmd, conn
    Set ExecuteSQL = rs
End Function

' 將 Recordset 轉為 HTML Table 字串
Function FormatRecordset(rs)
    ' ... 略：雙層迴圈產生 <table><tr><th>...</table>
End Function

' 使用 CDO 寄送 HTML 郵件
Sub Send_Mail(subject, recipient, htmlBody)
    Dim iMsg, iConf, Flds
    Set iMsg = CreateObject("CDO.Message")
    Set iConf = CreateObject("CDO.Configuration")
    Set Flds = iConf.Fields
    With Flds
        .Item("https://schemas.microsoft.com/cdo/configuration/sendusing") = 1 ' cdoSendUsingPickup
        .Item("https://schemas.microsoft.com/cdo/configuration/smtpserver") = SMTP_SERVER
        .Item("https://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 10
        .Update
    End With
    With iMsg
        Set .Configuration = iConf
        .To = recipient
        .From = SENDER_EMAIL
        .Subject = subject
        .HTMLBody = htmlBody
        .Send
    End With
End Sub

部署與排程

• 在 DTS 設計器工具列點擊 分析 檢查語法，再點 執行 (綠色箭頭) 測試
• 於企業管理器右鍵套件選 排程套件，設定每日 23:00 執行
• 確認 SQL Server Agent 已啟動，且套件以作業形式出現

擴充建議

• 整合其他 DTS Task（如資料轉換、檔案操作）
• 使用全域變數與工作流控制複雜邏輯
• 套用至即時財報、訂單狀態、產業動態等多樣化推播場景