Create an OLE object instance in SQL Server, sometimes we want to perform a stored procedure in the database while calling the COM object in the system. At this time we can use SQL system stored procedure sp_oacreate, this memory is called to have certain permissions, and only the SYSADMIN fixed server role can perform sp_oacreate.
Grammatical sp_oacreate progid, | CLSID, ObjectToken Output [, context]
Parameter PROGID
It is the program identifier (PROGID) of the OLE object to be created. This string describes the class of the OLE object, its form is as follows:
'Olecomponent.object'
OLECMPONENT is the component name of the OLE Automation server, Object is the OLE object name. The specified OLE object must be valid and must support the IDSPATCH interface.
For example, SqldMo.sqlServer is the ProgID of the SQL-DMO SQLServer object. SQL-DMO component name is SQLDMO, SQLServer object is valid, just like all SQL-DMO objects, SQL Server objects support iDispatch.
CLSID
It is the class identifier (CLSID) of the OLE object to be created. This string describes the class of the OLE object, its form is as follows:
'{nnnnnnnnnnnn-nnnn-nnnn-nnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN}'
The specified OLE object must be valid and must support the IDSPATCH interface.
For example, {00026ba1-0000-0000-c000-000000000046} is the CLSID of the SQL-DMO SQLServer object.
ObjectToken Output
Is the returned object token, and must be a local variable that the data type is Int. The object token is used to identify the created OLE object and will be used when calling other OLE automation stored procedures.
Context
Specifies the newly created OLE object to perform the context in it. If specified, this value must be one of the following values:
1 = Only (.dll) OLE server 4 = only local (.exe) OLE server 5 = process OLE server and local OLE servers can
If not specified, its default is 5. This value will be passed as a dwclscontext parameter when calling CocreateInstance.
If the process is allowed to use the OLE server (by using the context value 1 or 5 or the context value), the server will access memory and other resources owned by SQL Server. The OLE server in the process may destroy the SQL Server's memory or resources and lead to unpredictable results, such as SQL Server access violations.
When the upper and lower text value is specified as 4, the local OLE server cannot access any SQL Server resources, so that the memory or resources of SQL Server cannot be destroyed.
Explain that the parameters of this stored procedure are specified by location, not by name.
Returns the code value 0 (success) or non-zero (failure), is an integer value of the HRESULT returned by the OLE automation object.
Let's take a look at a specific example. In this example we will call a COM component written, this component is the function of providing the read sequence number.
CREATE PROCEDURE [dbo]. [Sp_MyCheckSN] @ENCRYPTION nvarchar (255) out, @ type int out AS DECLARE @object intDECLARE @hr intDECLARE @property varchar (255) DECLARE @return varchar (255) DECLARE @src varchar (255), @Desc varchar (255) - Create a COM object EXEC @hr = sp_oacreate 'securitySn.csecurity', @Object outif @hr <> 0begin set @ encryption = 'err' Returne
- Call Method DECLARE @AA NVARCHAR (255) - The final return value exec @hr = sp_oamethod @object, 'getDisksn', @AA out @ hr <> 0begin set @ encryption = 'Err' Returnendelsebegin set @ type = 8 - Indicate registration reading mode END
--------------------- if @aa = '' begin - Read EXEC @hr = sp_oamethod @Object, 'getSnbyApi', @AA OUT IF @ HR <> 0 begin set @ encryption = 'Err' return ELSE BEGIN SET @ type = 5 - Indicate which way to read the end end of the registration number
- Destroy this object EXEC @hr = sp_oadestroy @Objectif @hr <> 0begin set @ encryption = 'err' returnendset @ encryption = @ aago
In order to prevent the user from modifying the code of the stored procedure. We can encrypt this stored procedure. However, the encrypted stored procedure may also be decomposed.
