How to invoke VB COM in store procedure

And then how to write event log to NT Server in the previous article, which will implement the function of writing event log to NT Server in store procedure.

In fact, write event log to NT Server or VB COM implementation, and what is done here is to invoke the VB COM in store procedure.

First, several SQL Server-brought store Procedure are introduced.

1:sp_oacreate establish an instance of an automatic action object
Here the automatic Operation object can be ActiveX EXE, COM, Shell and so on. It can also take advantage of WScript. The Shell exploits the intrusion (pulled away).
COM can be created in the following ways:
EXEC @hr = sp_OACreate ' Eventrecord.eventrecorder ', @EventRecorder OUTPUT
Here Eventrecord.eventrecorder is written for VB COM class, @EventRecorder for the return value, the return value is int type. The subsequent operation will be done around the @eventrecorder and can be considered as an object in the store procedure. @hr returns a value for the sp_OACreate call, or failure if @hr=0 represents a successful creation. Often make the wrong deal.

2:sp_oadestroy frees an instance of an object
If you need to release resources after you have completed the operation of the object you created, you can use the following methods:
EXEC sp_OADestroy @EventRecorder

3:sp_oageterrorinfo get an error message from an HRESULT returned by another procedure
Used primarily for error handling.

4:sp_oagetproperty stores the properties of an object in a result set or in a local variable
Implements communication with the object. Such as:
EXEC sp_oagetproperty @EventRecorder, ' Source ', @EventSource OUTPUT
Implementation gets the value of the Source property in the Eventrecorder object and puts it into the @eventsource variable

5:sp_oasetproperty the property of an object, it changes the property value of the object
Implements communication with the object. Such as:
EXEC sp_OASetProperty @EventRecorder, ' Source ', @EventSource
Implementation sets the source property of the @eventrecorder object, setting the Source property to the value of the @eventsource variable.

6:sp_oamethod the method that implements the object
Pass parameters to the object's method and get the return value.
There are parameters with return values: such as
EXEC sp_OAMethod @EventRecorder, ' Writeeventlog ', @ResultValue OUTPUT,
@EventMessage = @EventMessage, @EventType = @EventType,
@EventID = @EventID, @EventCategory = @EventCategory
No parameters have return values: such as
EXEC sp_OAMethod @EventRecorder, ' Writeeventlog ', @ResultValue OUTPUT
No parameter no return value: if
EXEC sp_OAMethod @EventRecorder, ' Writeeventlog ', NULL

7:sp_oastop shutdown SQL Server automatic stored procedures
Turn off SQL Server's automatic operating environment for COM without any parameters. is not necessary because the automatic operating environment is automatically turned on when sp_OACreate is first invoked, and the automatic environment is automatically closed when SQL Server shuts down. In addition, if a stored procedure is operating on a COM object, and another stored procedure shuts down the automatic environment, an error occurs, so this is not recommended.

OK, so far, the knowledge necessary to operate COM is sufficient.
Here is an example of this implementation:

/*
* * Write event Log to NT event Viewer
*/
CREATE PROCEDURE Dbo.sp_eventrecorder
(
@EventSource nvarchar,----Event Source
@EventID int,----Event ID, please refer the <application ID document>
@EventType int,----Event Type, 0--information;1--error;2--warning
@EventMessage nvarchar,----Event message,the Error Content
@EventCategory int = 0,----Event Category, default 0
@ReturnValue int OUTPUT----return Value 0--success;1--fail
)
As
DECLARE
@EventMachineName nvarchar (100),
@EventRecorder int,
@hr int,
@ResultValue nvarchar (10)

BEGIN
----Set Event Log Server Name, ' Means local machine,need to Config
SET @EventMachineName = '
End

----Create Eventrecorder Object
EXEC @hr = sp_OACreate ' Eventrecord.eventrecorder ', @EventRecorder OUTPUT
IF @hr = 0
BEGIN
----Set method 0--write to Event Viewer;1--write to Database.default 0
EXEC @hr = sp_OASetProperty @EventRecorder, ' method ', 0
IF @hr <> 0 GOTO Errorhandle

----Set Machine Name of Event Log Server,default local Server
EXEC @hr = sp_OASetProperty @EventRecorder, ' machinename ', @EventMachineName
IF @hr <> 0 GOTO Errorhandle

  ----Set Event source,must to do it!
  EXEC  @hr = sp_oasetproperty  @EventRecorder, ' Source ', @EventSource
  if @hr <> 0 GOTO Errorhandle

  ----Write Event Log
  exec   @hr = sp_oamethod  @EventRecorder, ' Writeeventlog ', @ResultValue OUTPUT,
         @EventMessage = @EventMessage, @EventType = @EventType,
         @EventID = @EventID, @EventCategory = @EventCategory
  if @hr <> 0 GOTO errorhandle
  
  ----Check IF write successfully!
  if @ResultValue = ' True '
  begin
   set @ReturnValue = 0
    exec  sp_OADestroy  @EventRecorder
   return
  end
   else
  begin
   set @ReturnValue = 1
   exec  sp_ oadestroy  @EventRecorder
   return
  end
 end
 else
 begin
  set @ReturnValue = 1
  return
 end

Errorhandle:

SET @ReturnValue = 1
EXEC sp_OADestroy @EventRecorder
Return