SQL Server的T-SQL編程語言在數據存儲和恢復方面功能強大，但在與SQL Server數據庫之外的系統交互方面則功能較弱。然而，我們可以通過SQL Server內置的COM自動操作環境來克服這個限制，SQL Server內置的COM自動操作環境可以使用戶在存儲過程中自動操作COM對象。

在SQL Server 7.0和SQL Server 6.5中提供了7個擴展的存儲過程，可以通過自己開發的或Office等現成的COM對象擴展SQL Server的功能。SQL Server還提供了一種錯誤處理機制，可以把出錯信息寫到SQL代理日志中。利用COM自動化操作服務，還可以把SQL Server與微軟的Exchange Server、Index Server和其他可以通過COM自動化操作服務控制其他軟件進行集SQL Server 6.5引進了對象自動操作環境，它最初被稱作OLE。隨著時間的變遷對象操作的名稱也有所變化，然而與SQL Server 6.5相比，SQL Server 7.0中的自動操作環境沒有改變，因此微軟的文檔中仍然把這一功能稱作OLE操作而不是COM操作，在查閱SQL Server在線手冊（BOL）時尤其需要注意這一點。下面我們來討論如何使用SQL Server的COM自動操作存儲過程以及COM自動操作如何幫助我們解決現實的編程問題。

COM操作的細節

表1列出了SQL Server中的7個用于COM操作的擴展存儲過程。當自動操作一個COM對象時，需要首先通過調用sp_OACreate建立一個COM對象的實例，然后通過一系列的sp_OAGetProperty、sp_OASetProperty和sp_OAMethod調用完成需要完成的任務，在完成對COM對象的操作后，還需要調用sp_OADestroy釋放該對象。在詳細地研究每個儲存過程時，請注意二個很重要的問題。

第一，必須提供調用的所有參數，因為自動操作功能不支持有名參數，如果不能使用一個詳細的參數，需要向它傳遞一個NULL作為占位符；第二，每個調用返回一個整數類型的HRESULT，如果調用成功則該值為0。在后面，我們將討論如何處理返回值為非。

存儲過程 描述

sp_OACreate 建立自動操作對象的一個實例

sp_OADestroy 釋放一個對象的實例

sp_OAGetErrorInfo 從其他過程返回的HRESULT中獲得錯誤描述信息

sp_OAGetProperty 把一個對象的屬性存儲在結果集或局部變量中

sp_OASetProperty 改變一個對象屬性的值

sp_OAMethod 執行對象的方法，向方法傳遞參數，并得到返回值

sp_OAStop 關閉SQL Server的自動操作環境

表 1: SQL Server的COM自動操作存儲過程

COM操作必須以調用sp_OACreate存儲過程開始，語法格式如下所示：

sp_OACreate progid | clsid, objecttoken OUT.PUT,

[context:]

第一個參數是程序ID（ProgID━━一個應用程序名.類名形式的字符串，例如：

Excel.Application，）或者一個類ID（CLSID━━一個nnnnnnnn-nnnn-nnnn-nnnn-nnnnnnnnnnnn形式的全球唯一的ID），它標明你希望創建實例的COM對象。

在可能的情況下，我建議使用ProgID參數，因為它易于輸入和記憶。你會發現，只有很少的希望自動操作的對象沒有ProgID，如果偶爾碰上這樣的對象，就只有使用CLSID了。第二個變量objecttoken也是一個整型變量，一個對象的標記是指向SQL Server創建的對象的句柄和指針，我們需要在隨后的對對象的自動操作中使用這個返回的對象標記來確定這個對象。最后的context變量是可選的，可以強迫創建的對象使用某種自動操作的機制。如果其值為1，則要求對象在一個ActiveX DLL文件中；值為4，則要求對象在ActiveX EXE服務器中；如果是缺省的值5，則可以使用任一自動操作。在這里我們建議使用缺省的選項，而無須為context參數提供一個恰當的值。下面調用op_OACreate 的命令將創建一個微軟的Excel程序的實例：

Declare @Object int

Declare @RetVal int

Exec @RetVal=sp_OACreate 'Excel.Application',

@Object OUTPUT

在創建一個對象后，需要獲取其一些屬性。要得到這些屬性，可以通過下面的語法調

sp_OAGetProperty：

sp_OAGetProperty objecttoken, propertyname[, propertyvalue OUTPUT] [,第一個參數objecttoken的值就是由sp_OACreate返回的值，參數Propertyname是我們希望獲取的屬性。

在獲取這個值是有幾種選擇，如果該屬性是一個單一的值，可以把它存儲在一個變量中，或者把它作為一個單行、單字段的結果集；如果屬性值是一個一維或二維的數組，則必須把它作為一個結果集；如果如果該屬性的值是一個多于二維的數組，sp_OAGetProperty就不能返回它的值，會出現一個錯誤。要返回一個結果集，只須簡單地不指定propertyvalue參數的值即可（如果需要它有一個值以便使用index參數，就把NULL賦給它好了。

否則的話，應該賦給propertyvalue一個適當的類型的值，并且一定要把該參數標記為OUTPUT。如果你訪問的屬性是一個集合，就需要使用index參數指定這個集合中一個特定的數字。如果一個對象的屬性是另一個對象，就應該把這個對象存入一個整數型變量中，sp_OAGetProperty返回的也是一個對象標記，不過與sp_OACreate返回的并不相同。我們可以使用這個對象標記對存儲過程返回的任何對象進行自動化操作。下面的命令調用sp_OAGetProperty把一個名字為DefaultFilePath的屬性值存入變量@DFP中：

Exec sp_OAGetProperty @Object, 'DefaultFilePath',

@DFP OUTPUT

可以通過如下格式使用sp_OASetProperty存儲過程改變一個對象的屬性值：

sp_OASetProperty objecttoken, propertyname,newvalue [, index]

第一個參數objecttoken是由sp_OACreate返回的，參數Propertyname是要改變的對象的屬性名字，Newvalue參數是想賦給屬性的新變量，可以是一個變量或一個文字值。如果設定的屬性值是作為一個集合的一個對象，可以使用可選的index參數來指定這個集合的一個特定的位置。下面的命令調用sp_OASetProperty把名字為FixedDecimalPlaces的屬性設置為6：Exec sp_OASetProperty @Object, 'FixedDecimalPlaces', 6

可以用下面的語法調用sp_OAMethod存儲過程執行一個對象的方法：

sp_OAMethod objecttoken, methodname [, returnvalue OUTPUT] [, 

Sp_OAMethod是最靈活的，因而也是最復雜的自動操作存儲過程，我們甚至可以用它象調用一個方法那樣調用一個屬性，而且還能得到一個返回值，當然，我們也能使用sp_OAGetProperty來完成這一任務。該存儲過程的第一個參數objecttoken是由sp_OACreate返回的對象標記，參數methodname是希望執行的方法的名字，如果該方法有返回值，則下一個參數returnvalue應當是一個包含該方法返回值的適當類型的變量；如果返回值是一個一維或二維的數組，則用NULL作為一個占位符，該過程將返回一個結果集。該存儲過程不能返回一個超過二維的數組作為結果集合，在這種情況下，SQL Server就會出錯。如果該方法沒有返回類型。

如果調用的方法需要參數，就需要在調用sp_OAMethod時提供這些參數。如果方法允許按順序提供參數，則按要求的順序列出每個參數，并用逗號分隔每個參數，還可以用變量或文字變量作為參數。如果需要使用有名參數，SQL Server也提供了相應的機制，只需使用：@變量名=變量值 的形式列出所需的變量即可。需要注意的是不要因為有@前綴而把變量名當作局部變量，當調用存儲過程sp_OAMethod時，SQL Server就會解析出@，因此，即使在調用的方法中有名字為HostName的參數時，仍然可以使用名字為@HostName的局部變量。

下面是二個調用sp_OAMethod的例子。第一個例子調用一個名字為CentimetersToPoints的方法，它只接受在@CMVal變量中提供的一個參數，返回的值存儲在變量@RetVal中。第二個例子調用一個名字為MailLogon的方法，它接受三個可選的變量，這個例子中根據名字接受二個變量，把Name設置為字符串"MyUserName"，把 Password設置為字符串：

Exec sp_OAMethod @Object, 'CentimetersToPoints',@RetVal OUTPUT, @CMVal

Exec sp_OAMethod @Object, 'MailLogon', NULL,@Name='MyUserName',

不再使用一個對象后，需要通過下面的語法調用存儲過程sp_OADestroy釋放對該對象的引sp_OADestroy objecttoken

調用sp_OADestroy存儲過程可以釋放由參數objecttoken指定的對象，同時還釋放這個對象所使用的內存和其他資源。下面是一個調用sp_OADestroy的命令：

Exec sp_OADestroy @Object

需要注意的是，T-SQL中的數據類型與其他的編程語言并非是一一對應的，在調用一個需要特定的數據類型的方法時就可能出錯。"數據類型轉換"工具條可以將SQL Server的數據。

錯誤處理

象在前面提到的那樣，如果對存儲過程的調用成功了，則會返回一個為0的HRESULT值，其他的HRESULT值則意味著發生了錯誤。要判斷一個非零的HRESULT值，可以把HRESULT值傳：

sp_OAGetErrorInfo [objecttoken] [, source OUTPUT] [, description OUTPUT]

第一個參數objecttoken是由sp_OACreate返回的對象標記。

下面的四個參數返回錯誤信息。Source是產生這一錯誤信息的應用程序或庫，Description是該錯誤的描述，如果有幫助文件的話，則該Helpfile是幫助文件的路徑。這三個參數都是有符號或無符號字符型數據，sp_OAGetErrorInfo會根據定義的變量的大小截取返回的值。最后一個參數helpid是特定錯誤在幫助文件中的索引號。下面的命令調用sp_OAGetErrorInfo以獲得某一個錯誤的更詳細的信息：

Declare @Source varchar(100), @Description varchar(255), @HelpFile

Exec sp_OAGetErrorInfo @Object, @Source OUTPUT, @Description OUTPUT,

SQL Server在線手冊還提供了一個有關sp_DisplayOAErrorInfo存儲過程的例子，該存儲過程可以調用sp_OAGetErrorInfo把返回的值組織成格式化的字符串，以便把該信息寫入日志文件中。

關于sp_DisplayOAErrorInfo的更詳細的信息，請參閱工具條， 另外，調用sp_OAStop儲存過程可以關閉SQL Server的COM自動操作環境，它無需任何參數。關閉自動操作環境在大多數情況下并非是必需的，第一次調用sp_OACreate時自動操作環境會自動開啟，SQL Server關閉時自動操作環境也會自動關閉。如果一個存儲過程正在對一個對象進行自動操作，而另一個過程調用sp_OAStop時就會出現錯誤，因此我們不建議在程序中調用sp_OAStop，只有在調試一個沒有運行的過程時，才可以通過一個查詢窗口調用它。

在實際工作中使用COM自動操作

至此，我們已經學習了如何使用每一個COM自動操作存儲過程，我們現在來討論一下一個綜合應用它們的例子。程序清單1是一個名字為sp_OpenWordIfCoProcAvailable的過程，在這個過程中，我們用sp_OACreate創建了一個Microsoft Word的實例，然后使用sp_OAGetProperty來獲取Word的MathCoProcessorAvailable屬性，如果sp_OAGetProperty返回1，則sp_OpenWordIfCoProcAvailable向調用過程返回Word對象的對象標記；否則，

sp_OpenWordIfCoProcAvailable關閉Word，并返回0。為了節省版面，我們只調用了出錯處理過程一次，在實際應用中，應該在每次調用自動操作存儲過程后都調用出錯處理過程。注意，為對Word進行自動操作，應該在安裝SQL Server的機器上安裝Word。

程序清單 1：自動操作Word的方法的例子

Create Procedure sp_OpenWordIfCoProcAvailable As

Declare @Object int, @hr int, @RetVal int 
Exec @hr = sp_OACreate 'Word.Application', @Object OUTPUT 
BEGIN 

Exec sp_DisplayOAErrorInfo @Object, @hr 
Return 0

END

Exec @hr = sp_OAGetProperty @Object, 'MathCoProcessorAvailable', @RetVal

If @hr=0 

BEGIN 

Exec @hr = sp_OAMethod @Object, 'Quit', 0 

Exec @hr = sp_OADestroy @Object 

Return 0

END

Exec @hr = sp_OAMethod @Object, 'Activate' 
Return @Object

如果需要對一個使用Visual Basic編寫的COM對象進行自動操作，調試它與SQL Server之間的互操作性是一件相當容易的事。我們需要在運行SQL Server的機器上安裝有Visual Basic，在Visual Basic的編輯器中加載COM項目，設置一些斷點，然后編譯并運行該COM對象。在有存儲過程對該對象進行自動操作時，在運行到一個斷點時，編輯器就會自動切換到調試模式，我們就可以象調試其他的Visual Basic程序那樣調試這個COM對象。如果要對調試過程實施更多的控制，可以使用T-SQL Debugger for VB插件，它能采用步進方式執行存。

此外，在SQL Server中應用COM自動操作我們還能作什么呢？下面是我曾使用SQL Server強大的COM自動操作功能的實際例子。前不久，我需要從一個SQL Server存儲過程中使用一個通過命名管道進行通訊，而SQL Server中沒有提供通過編程方式打開和使用命名管道的機制，我正好有一個可以使用命名管道通訊的VB例和庫，因此就把這個庫文件作成一個類，并創建了一個ActiveX DLL文件，然后從存儲過程中對DLL進行自動操作。

另一次，我需要復制一些文件和數據庫表。使用SQL Server的復制功能可以很方便地復制這些數據，但復制文件則要難得多，NT的目錄同步功能很弱，不能滿足要求。盡管我還可以把拷貝命令存到字符變量中，然后把變量傳遞給xp_cmdshell，但會遇到命令行長度的限制。更不方便的是，如果在拷貝過程中發生了錯誤，我不能很方便地判斷錯誤發生在什么地方，因此，我就編寫了一個ActiveX DLL，并通過自動操作它來處理文件的拷貝工作。

還有一次，我需要在SQL Server 6.5和Index Server 2.0之間先執行連結后再完成查詢任務，如果使用帶ADO的Windows 2000 Indexing Services和SQL Server 7.0，完成這樣的工作非常簡單，但如果不是使用這些產品，則要困難得多。

首先，需要編寫一個可以執行Index Server查詢對象ixsso.dll的ActiveX DLL，對它進行自動操作，從Index Server目錄中獲得信息，并通過一個方法將信息返回到存儲過程中。

然后把這些數據保存到一個臨時表中，再對它進行聯結操作。COM自動操作再一次幫我解決了問題。在存儲過程中執行COM自動操作幾乎可以使我們完成任何想完成的操作。SQL Server 2000中的COM自動操作沒有什么變化，因此采用這種方法編寫的代碼在將來仍然可以使用下去。
(e129)