Object Hierarchy Syntax
The propertyname parameter of sp_OAGetProperty and sp_OASetProperty and the methodname of sp_OAMethod support an object hierarchy syntax similar to Microsoft® Visual Basic®. When this special syntax is used, these parameters have the general form:
Syntax
'TraversedObject.PropertyOrMethod'
Arguments
TraversedObject
Is an OLE object in the hierarchy under the objecttoken specified in the stored procedure. Use Visual Basic syntax to specify a series of collections, object properties, and methods that return objects. Each object specifier in the series must be separated by a period (.).
An item in the series can be the name of a collection. Use this syntax to specify a collection:
Collection("item")
The double quotation marks (") around item are required. The Visual Basic exclamation point (!) syntax for collections is not supported.
PropertyOrMethod
Is the name of a property or method of the TraversedObject.
To specify all index or method parameters by using sp_OAGetProperty, sp_OASetProperty, or sp_OAMethod parameters (including support for sp_OAMethod output parameters), use this syntax:
PropertyOrMethod
To specify all index or method parameters inside the parentheses (causing all index or method parameters of sp_OAGetProperty, sp_OASetProperty, or sp_OAMethod to be ignored) use this syntax:
PropertyOrMethod( [ParameterName :=] "parameter" [,...] )
The double quotation marks (") around each parameter are required. All named parameters must be specified after all positional parameters are specified.
Remarks
If TraversedObject is not specified, PropertyOrMethod is required.
If PropertyOrMethod is not specified, the TraversedObject is returned as an object token output parameter from the OLE Automation stored procedure. If PropertyOrMethod is specified, the property or method of the TraversedObject is called, and the property value or method return value is returned as an output parameter from the OLE Automation stored procedure.
If any item in the TraversedObject list does not return an OLE object, an error occurs.
For more information about Visual Basic OLE object syntax, see the Visual Basic documentation.
For more information about HRESULT Return Codes, see HRESULT Return Codes in the sp_OACreate section.
Examples
These are examples of object hierarchy syntax using a SQL-DMO SQLServer object.
-- Call the Connect method of the SQLServer object.
EXEC @hr = sp_OAMethod @object,
'Connect("my_server", "my_login", "my_password")'
-- Get the pubs..authors Table object.
EXEC @hr = sp_OAGetProperty @object,
'Databases("pubs").Tables("authors")',
@table OUT
-- Get the Rows property of the pubs..authors table.
EXEC @hr = sp_OAGetProperty @object,
'Databases("pubs").Tables("authors").Rows',
@rows OUT
-- Call the CheckTable method of the pubs..authors table.
EXEC @hr = sp_OAMethod @object,
'Databases("pubs").Tables("authors").CheckTable',
@checkoutput OUT
See Also
Data Type Conversions Using OLE Automation Stored Procedures
How to create an OLE Automation object (Transact-SQL)
System Stored Procedures (OLE Automation Extended Stored Procedures)