sp_OAMethod

Calls a method of an OLE object.

Syntax

sp_OAMethod objecttoken, methodname, [returnvalue OUTPUT
[, [@parametername =] parameter [OUTPUT]...]]

where

objecttoken

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

methodname

Is the method name of the OLE object to call.

returnvalue OUTPUT

Is the return value of the method of the OLE object. If specified, it must be a local variable of the appropriate datatype.

[@parametername =] parameter [OUTPUT]

Is a method parameter. If specified, parameter must be a value of the appropriate datatype.

To obtain the return value of an output parameter, parameter must be a local variable of the appropriate datatype, and OUTPUT must be specified. If a constant parameter is specified, or if OUTPUT is not specified, any return value from an output parameter is ignored.

If specified, parametername must be the name of the Visual Basic named parameter. Note that @parametername is not a Transact-SQL local variable. The at (@) is removed, and parametername is passed to the OLE object as the parameter name. All named parameters must be specified after all positional parameters are specified.

Remarks

If the method returns a single value, either:

• Specify a local variable for returnvalue. The method return value is returned in the local variable.

  Or

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

If the method return value is an OLE object, you must specify a local variable of datatype int for the returnvalue 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 method return value is 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 method return value is an array, if returnvalue is specified, it is set to NULL.

An error occurs under any of the following circumstances:

• If returnvalue is specified, but the method does not return a value
• If the method returns an array with more than two dimensions
• If the method returns an array as an output parameter

Note that sp_OAMethod can also be used to get a property value.

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

Examples

A.    Call a Method

This example calls the Connect method of the previously created SQLServer object.

EXEC @hr = sp_OAMethod @object, 'Connect', NULL, 'my_server',
    'my_login', 'my_password'
IF @hr <> 0
BEGIN
    EXEC sp_displayoaerrorinfo @object, @hr
    RETURN
END
  

B.    Get a Property

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_OAMethod @object, 'HostName', @property OUT
IF @hr <> 0
BEGIN
    EXEC sp_displayoaerrorinfo @object, @hr
    RETURN
END
PRINT @property