sp_OACreate

Creates an OLE object on the SQL Server computer.

Syntax

sp_OACreate {progid | clsid}, objecttoken OUTPUT [, context]

where

progid

Is the programmatic identifier (ProgID) of the OLE object to create. This character string describes the class of the OLE object and has the following form:

'OLEComponent.Object'

OLEComponent is the component name of the OLE Automation server, and Object is the name of the OLE object. The specified OLE object must be creatable and must support the IDispatch interface.

For example, 'SQLOLE.SQLServer' is the ProgID of the SQL-DMO SQLServer object. SQL-DMO has a component name of SQLOLE, the SQLServer object is creatable, and (like all SQL-DMO objects) the SQLServer object supports IDispatch.

clsid

Is the class identifier (CLSID) of the OLE object to create. This character string describes the class of the OLE object and has the form:

'{nnnnnnnn-nnnn-nnnn-nnnn-nnnnnnnnnnnn}'

The specified OLE object must be creatable and must support the IDispatch interface.

For example, '{00026BA1-0000-0000-C000-000000000046}' is the CLSID of the SQL-DMO SQLServer object.

objecttoken OUTPUT

Is the returned object token. It must be a local variable of datatype int. This object token identifies the created OLE object and is used in calls to the other OLE Automation stored procedures.

context

Specifies the execution context that the newly created OLE object will run in. If specified, this value must be one of the following:

1In-process (.DLL) OLE server only
4Local (.EXE) OLE server only
5Both in-process and local OLE server allowed

If not specified, the default value is 5. This value is passed as the dwClsContext parameter of the call to CoCreateInstance.

If an in-process OLE server is allowed (by using a context value of 1 or 5 or by not specifying a context value), it has access to memory and other resources owned by SQL Server. An in-process OLE server that is badly behaved might damage SQL Server memory or resources and cause unpredictable results such as a SQL Server access violation.

When you specify a context value of 4, a local OLE server does not have access to any SQL Server resources, and it cannot damage SQL Server memory or resources.

Remarks

The created OLE object is automatically destroyed at the end of the Transact-SQL statement batch.

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

Examples

A.    Use Prog ID

This example creates a SQL-DMO SQLServer object by using its Prog ID.

DECLARE @object int
DECLARE @hr int
EXEC @hr = sp_OACreate 'SQLOLE.SQLServer', @object OUT
IF @hr <> 0
BEGIN
    EXEC sp_displayoaerrorinfo @object, @hr
    RETURN
END
  

B.    Use CLSID

This example creates a SQL-DMO SQLServer object by using its CLSID.

DECLARE @object int
DECLARE @hr int
EXEC @hr = sp_OACreate '{00026BA1-0000-0000-C000-000000000046}',
    @object OUT
IF @hr <> 0
BEGIN
    EXEC sp_displayoaerrorinfo @object, @hr
    RETURN
END