OLE Automation Objects in Transact-SQL

Transact-SQL includes several system stored procedures that allow OLE Automation objects to be referenced in Transact-SQL batches, stored procedures, and triggers. These system stored procedures run as extended stored procedures, and the OLE Automation objects that are executed through the store procedures run in the SQL Server address space in the same way as an extended stored procedure.

The OLE Automation stored procedures allow Transact-SQL batches to reference:

• SQL-DMO objects
• Custom OLE Automation objects developed using the Microsoft® Visual Basic® development system. A custom in-process OLE server created using Visual Basic must have an error handler (specified with the On Error GoTo statement) for the Class_Initialize and Class_Terminate subroutines. The error handlers prevent unhandled errors from occurring in these subroutines. Unhandled errors in the Class_Initialize and Class_Terminate subroutines can cause unpredictable SQL Server problems, such as a SQL Server access violation. Error handlers for other subroutines are also recommended.

The first step to using an OLE Automation object in Transact-SQL is to call the sp_OACreate system stored procedure to create an instance of the object in the SQL Server address space.

After an instance of the object has been created, use these stored procedures to work with the properties, methods, and error information of the object:

• Call sp_OAGetProperty to get the value of a property.
• Call sp_OASetProperty to set the value of a property.
• Call sp_OAMethod to call a method.
• Call sp_OAGetErrorInfo to get the most recent error information.

When there is no more need for the object, call sp_OADestroy to deallocate the instance of the object created with sp_OACreate.

OLE Automation objects return data through property values and methods. sp_OAGetProperty and sp_OAMethod return these data values in the form of a result set.

The scope of an OLE Automation object is a batch. All references to the object must be contained in a single batch, stored procedure, or trigger.

When referencing objects, the SQL Server OLE Automation objects support traversing the object to other objects it contains. For example, when using the SQL-DMO SQLServer object, references can be made to databases and tables contained on that server. For more information, see Object Hierarchy Syntax.

See Also: