Calling All COM Objects

SQL Server system stored procedures and extended stored procedures perform a variety of database-related functions. However, one important area that even extended stored procedures don't address is COM interoperability. Most of Microsoft's core products have a built-in COM-object model that enables integrated interoperability. SQL Server provides a set of seven sp_OA stored procedures for OLE Automation.

7. sp_OACreate

Call sp_OACreate before any other sp_OA stored procedure. Sp_OACreate creates an instance of a COM object using either the COM object's Class ID (CLSID) or the human-readable Program ID. In the following example, the first parameter is the Distributed Management Objects (DMO) Program ID and the second parameter is an output parameter that returns a COM-object handle for the other procedures to use.

EXEC @hr = sp_OACreate 'Word.  Application', @object OUT

6. sp_OADestroy

Sp_OADestroy deletes a COM object and releases the system resources it consumed. This procedure takes a single parameter: the object handle that sp_OACreate returns.

EXEC @hr = sp_OADestroy @object

5. sp_OAStop

This stored procedure stops the execution of SQL Server's OLE Automation environment, which starts automatically when you issue the first call to sp_OACreate. It runs until SQL Server shuts down and takes no parameters.

EXEC sp_OAStop

4. sp_OAGetProperty

Sp_OAGetProperty accepts three parameters to retrieve a property value from the OLE Automation object: the object handle sp_OACreate returns, a string that identifies the property name, and an output parameter that contains the value. The third parameter must match the property's data type.

EXEC @hr = sp_OAGetProperty @object, 'Application',  @Word.Application OUT

3. sp_OASetProperty

This procedure assigns a value to a property. Its first parameter is the COM-object handle, the second identifies the property name, and the third contains the new value.

EXEC @hr = sp_OASetProperty @object, 'Text', @text

2. sp_OAMethod

Sp_OAMethod executes one of the COM object's methods. The first parameter is the COM-object handle. The parameters that follow must match the method's expectations.

EXEC @hr = sp_OAMethod @object, 'Activate'

1. sp_OAGetErrorInfo

Sp_OAGetErrorInfo returns error information for all sp_OA stored procedures. After calling any sp_OA procedure, check the return value—if the value isn't 0, then an error has probably occurred.

EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT