sp_OACreate (Transact-SQL)
Applies to: SQL Server
Creates an instance of an OLE object.
Transact-SQL syntax conventions
sp_OACreate { progid | clsid }
, objecttoken OUTPUT
[ , context ]
[ ; ]
The programmatic identifier (ProgID) of the OLE object to create. This character string describes the class of the OLE object and has the 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 valid and must support the IDispatch
interface.
For example, SQLDMO.SQLServer
is the ProgID of the SQL-DMO SQLServer
object. SQL-DMO has a component name of SQLDMO, the SQLServer
object is valid, and (like all SQL-DMO objects) the SQLServer
object supports IDispatch
.
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 valid and must support the IDispatch
interface.
For example, {00026BA1-0000-0000-C000-000000000046}
is the CLSID of the SQL-DMO SQLServer
object.
The returned object token, and must be a local variable of data type int. This object token identifies the created OLE object and is used in calls to the other OLE Automation stored procedures.
Specifies the execution context in which the newly created OLE object runs. If specified, this value must be one of the following options:
1
= In-process (.dll
) OLE server only4
= Local (.exe
) OLE server only5
= Both 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 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 doesn't have access to any SQL Server resources, and it can't damage SQL Server memory or resources.
Note
The parameters for this stored procedure are specified by position, not by name.
0
(success) or a nonzero number (failure) that is the integer value of the HRESULT returned by the OLE Automation object.
For more information about HRESULT return codes, see OLE automation return codes and error information.
If OLE automation procedures are enabled, a call to sp_OACreate
starts the OLE Automation shared execution environment. For more information about enabling OLE automation, see Ole Automation Procedures (server configuration option).
The created OLE object is automatically destroyed at the end of the Transact-SQL statement batch.
Requires membership in the sysadmin fixed server role or execute permission directly on this stored procedure. The Ole Automation Procedures server configuration option must be enabled to use any system procedure related to OLE Automation.
The following example creates a SQL-DMO SQLServer
object by using its ProgID.
DECLARE @object INT;
DECLARE @hr INT;
DECLARE @src VARCHAR(255),
@desc VARCHAR(255);
EXEC @hr = sp_OACreate 'SQLDMO.SQLServer',
@object OUTPUT;
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object,
@src OUTPUT,
@desc OUTPUT
RAISERROR ('Error Creating COM Component 0x%x, %s, %s', 16, 1, @hr, @src, @desc);
RETURN
END;
GO
The following example creates a SQL-DMO SQLServer
object by using its CLSID.
DECLARE @object INT;
DECLARE @hr INT;
DECLARE @src VARCHAR(255),
@desc VARCHAR(255);
EXEC @hr = sp_OACreate '{00026BA1-0000-0000-C000-000000000046}',
@object OUTPUT;
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object,
@src OUTPUT,
@desc OUTPUT
RAISERROR ('Error Creating COM Component 0x%x, %s, %s', 16, 1, @hr, @src, @desc);
RETURN
END;
GO