編寫資料庫指令碼

指令碼|資料|資料庫
作者：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) = "" thenWScript.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) = "" thenfor each table in oDatabase.tables' 確保該表不是系統資料表if table.systemobject = false thenoBCP.DataFilePath = table.name & ".csv"nRows = table.ExportData(oBCP)wscript.Echo nRows & " rows exported from " & table.nameend ifnextelse' 設定輸出檔案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，該指令碼使用了“Internet 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”組中的程式經理，機會使然，只要涉及指令碼，他就可能大顯身手一番。他的大部分業餘時間都花在觀看美國電視台轉播的精彩橄欖球賽，以及向他的美國同事解說板球。