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
0 comments
Hide comments