sp_OAGetProperty

Gets a property value of an OLE object.

Syntax

sp_OAGetProperty objecttoken, propertyname, [propertyvalue OUTPUT
[, index...]]

where

objecttoken

Is the object token of an OLE object previously created by sp_OACreate.

propertyname

Is the property name of the OLE object to return.

propertyvalue OUTPUT

Is the returned property value. If specified, it must be a local variable of the appropriate datatype.

index

Is an index parameter. If specified, it must be a value of the appropriate datatype.

Some properties have parameters. They are called indexed properties, and the parameters are called index parameters. A property can have multiple index parameters.

Remarks

If the property returns a single value, either:

• Specify a local variable for propertyvalue. The property value is returned in the local variable.

  Or

• Do not specify propertyvalue. The property value is returned to the client as a single-column, single-row results set.

If the property returns an OLE object, you must specify a local variable of datatype int for the propertyvalue parameter. An object token will be stored in the local variable, and this object token can be used with other OLE Automation stored procedures.

If the property returns an array with one or two dimensions, the array will be returned to the client as a results set as follows:

• A one-dimensional array is returned to the client as a single-row results set with as many columns as there are elements in the array. In other words, the array is returned as (columns).
• A two-dimensional array is returned to the client as a results set with as many columns as there are elements in the first dimension of the array and with as many rows as there are elements in the second dimension of the array. In other words, the array is returned as (columns, rows).

When the property returns an array, if propertyvalue is specified, it is set to NULL.

If propertyvalue is specified, but the property does not return a value, an error occurs. If the property returns an array with more than two dimensions, an error occurs.

You can also use sp_OAMethod to get a property value.

This procedure returns a 0 when successful or a non-zero HRESULT when an error occurs.

Examples

A.    Use Local Variable

This example gets the HostName property (of the previously created SQLServer object) and stores it in a local variable.

DECLARE @property varchar(255)
EXEC @hr = sp_OAGetProperty @object, 'HostName', @property OUT
IF @hr <> 0
BEGIN
    EXEC sp_displayoaerrorinfo @object, @hr
    RETURN
END
PRINT @property
  

B.    Use Results Set

This example gets the HostName property (of the previously created SQLServer object) and returns it to the client as a results set.

EXEC @hr = sp_OAGetProperty @object, 'HostName'
IF @hr <> 0
BEGIN
    EXEC sp_displayoaerrorinfo @object, @hr
    RETURN
END