Skip to content

Latest commit

 

History

History
93 lines (64 loc) · 3.74 KB

object-hierarchy-syntax-transact-sql.md

File metadata and controls

93 lines (64 loc) · 3.74 KB
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic helpviewer_keywords dev_langs
Object hierarchy syntax (Transact-SQL)
Object hierarchy syntax (Transact-SQL)
markingmyname
maghan
randolphwest
08/22/2024
sql
system-objects
reference
objects [SQL Server], hierarchy syntax
TSQL

Object hierarchy syntax (Transact-SQL)

[!INCLUDE SQL Server]

The propertyname parameter of sp_OAGetProperty and sp_OASetProperty and the methodname parameter of sp_OAMethod support an object hierarchy syntax similar to that of [!INCLUDE msCoName] [!INCLUDE visual-basic]. When this special syntax is used, these parameters have the following general form.

Syntax

TraversedObject.PropertyOrMethod

Arguments

TraversedObject

An OLE object in the hierarchy under the objecttoken specified in the stored procedure. Use [!INCLUDE 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 (") are required. The [!INCLUDE visual-basic] exclamation point (!) syntax for collections isn't supported.

PropertyOrMethod

The name of a property or method of the TraversedObject.

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 the following syntax:

PropertyOrMethod ( [ ParameterName := ] "parameter" [ , ... ] )

The double quotation marks (") are required. All named parameters must be specified after all positional parameters are specified.

Remarks

If TraversedObject isn't specified, PropertyOrMethod is required.

If PropertyOrMethod isn't 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. 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 doesn't return an OLE object, an error is raised.

For more information about [!INCLUDE visual-basic] OLE object syntax, see the [!INCLUDE visual-basic] documentation.

For more information about HRESULT return codes, see sp_OACreate.

Examples

The following are examples of object hierarchy syntax that use a SQL-DMO SQLServer object.

-- Get the AdventureWorks2022 Person.Address Table object.
EXEC @hr = sp_OAGetProperty @object,
   'Databases("AdventureWorks2022").Tables("Person.Address")',
   @table OUT

-- Get the Rows property of the AdventureWorks2022 Person.Address table.
EXEC @hr = sp_OAGetProperty @object,
   'Databases("AdventureWorks2022").Tables("Person.Address").Rows',
   @rows OUT

-- Call the CheckTable method to validate the
-- AdventureWorks2022 Person.Address table.
EXEC @hr = sp_OAMethod @object,
   'Databases("AdventureWorks2022").Tables("Person.Address").CheckTable',
   @checkoutput OUT

Related content