Click here to Skip to main content
Click here to Skip to main content

How to Integrate an OLE Object with SQL Server v2

, 22 Aug 2009
Rate this:
Please Sign up or sign in to vote.
This article will give you an idea about how to integrate an OLE Object that is a COM+ API with SQL Server and write a message into a Windows event log.

Table of Contents

Introduction

If you read some of my CodeProject articles, probably you know that I'm not very much bashful to share with you, what I learned? Actually if I find something interesting, I will always try to share this with you. I'm not well experienced on article writing, but I try because of the wonderful knowledge sharing platform of CodeProject. I hope that everybody will agree with me that CodeProject provides us an excellent platform to share knowledge.

Before I start about this article in detail, I would like to share a little incident; few months ago, I was working to update an ERP system, where I found huge bugs of Transact-SQL and table design. Most of the time, the system fails to process data transaction. To find out the reason, I assign one of my team members. After few days, he reports to me that some of the main reasons are data type mismatch, data conversion error, stored procedure parameters issues, etc. So we decided that at first we have to find out the sources of the error and the types of error as well. So the question is how we can achieve this?

When we started to work on that, we were very confused, Should we maintain a text file to keep a log or create a table where we stored the log but the problem is a number of stored procedures will work with various OLE objects. So finally, we decided that we need to use Windows event log API. This article is about how to create a Windows event log and write a custom message using Transact-SQL.

A Little Basic Knowledge on OLE Object

When I start to learn about Microsoft OLE Objects, I found a huge theoretical explanation on Online resources specially on Microsoft Development Network (MSDN). I just try to give you some summary from them.

You can also find some more detail on OLE automation from my CodeProject article. The links are given below:

What is an OLE Object

OLE (Object Linking and Embedding) is Microsoft's framework for a compound document technology. Briefly, a compound document is something like a display desktop that can contain visual and information objects of all kinds: text, calendars, animations, sound, motion video, 3-D, continually updated news, controls, and so forth. Each desktop object is an independent program entity that can interact with a user and also communicate with other objects on the desktop. Part of Microsoft's ActiveX technologies, OLE takes advantage and is part of a larger, more general concept, the Component Object Model (COM) and its distributed version, DCOM. An OLE object is necessarily also a component (or COM object).

So we can define an OLE object as “OLE is a compound document standard developed by Microsoft Corporation. It enables you to create objects with one application and then link or embed them in a second application. Embedded objects retain their original format and links to the application that created them.”

More details can be found at this link.

Using the Code

This is a very simple way. I wrote some methods to create or write any custom message in a Windows event log. The methods with a short description are given below:

Methods

  • GetAuthor
  • IsExist
  • CreateLog
  • Write_INFO_EventLog
  • Write_WARNING_EventLog
  • Write_FAILUR_EventLog
  • Write_SUCCESS_EventLog
  • Write_ERROR_EventLog

More details can be found at this link.

GetAuthor

This is a simple method, actually when I start writing code at first I write this method which just returns a string type data. It was just for testing purposes.

IsExist

This method will check whether the requested log name is registered or not. If it is registered, then it will return true otherwise false.

Arguments: string_expression is an expression of the type string / varchar

Return type: bool

Sample Code Example
private bool IsExist(string strLogName)
{
   bool Reasult = false;

   if (System.Diagnostics.EventLog.SourceExists(strLogName)) return Reasult = true;
    return Reasult;
} 

CreateLog

This method will create a new Windows event log if the requested log name is not registered. Once it successfully registers the log, it will return true otherwise false.

Arguments (Log name): string_expression is an expression of the type string / varchar

Return type: bool

Sample Code Example
public bool CreateLog(string strLogName)
 {
         bool Reasult = false;
          try
         {
             if (!this.IsExist(strLogName))
             {
                   System.Diagnostics.EventLog.CreateEventSource(strLogName, strLogName);
                   System.Diagnostics.EventLog SQLEventLog = 
					new System.Diagnostics.EventLog();

                    SQLEventLog.Source = strLogName;
                    SQLEventLog.Log = strLogName;

                    SQLEventLog.Source = strLogName;
                    SQLEventLog.WriteEntry("The " + strLogName + 
			" was successfully initialize component.", 
			EventLogEntryType.Information);

                    Reasult = true;
                }
            }
            catch
            { 
                Reasult = false; 
            }

            return Reasult;
  }

Write_INFO_EventLog

This method writes a user defined string into the specified registered event log as an information message.

Arguments

  1. Event log name as string
  2. Message source as string
  3. Message detail as string

Info_message

Figure - 1 showing the output of the method "Write_INFO_EventLog".
Sample Code Example
public void Write_INFO_EventLog(string strLogName
                              , string strSource
                              , string strErrDetail)
 {
                if (this.IsExist(strLogName))
                {

                    System.Diagnostics.EventLog SQLEventLog = 
				new System.Diagnostics.EventLog();

                    try
                    {

                        SQLEventLog.Source = strLogName;
                        SQLEventLog.WriteEntry(Convert.ToString(strSource)
                                              + Convert.ToString(strErrDetail), 
					EventLogEntryType.Information);
                    }
                    catch (Exception ex)
                    {
                        SQLEventLog.Source = strLogName;
                        SQLEventLog.WriteEntry(Convert.ToString("INFORMATION: ")
                                              + Convert.ToString(ex.Message), 
					EventLogEntryType.Information);
                    }
                    finally
                    {
                        SQLEventLog.Dispose();
                        SQLEventLog = null;
                    }
                }
 }

Write_WARNING_EventLog

This method writes a user defined string into the specified registered event log as a warning message.

Arguments

  1. Event log name as string
  2. Message source as string
  3. Message detail as string

Figure - 2 showing the output of the method "Write_WARNING_EventLog"
Sample Code Example
public void Write_WARNING_EventLog(string strLogName
                                 , string strSource
                                 , string strErrDetail)
 {
                if (this.IsExist(strLogName))
                {
                    System.Diagnostics.EventLog SQLEventLog = 
				new System.Diagnostics.EventLog();

                    try
                    {
                        SQLEventLog.Source = strLogName;
                        SQLEventLog.WriteEntry(Convert.ToString(strSource)
                                              + Convert.ToString(strErrDetail), 
					EventLogEntryType.Warning);
                    }
                    catch (Exception ex)
                    {
                        SQLEventLog.Source = strLogName;
                        SQLEventLog.WriteEntry(Convert.ToString("WARNING: ")
                                              + Convert.ToString(ex.Message), 
					EventLogEntryType.Warning);
                    }
                    finally
                    {
                        SQLEventLog.Dispose();
                        SQLEventLog = null;
                    }
                }
 }

Write_FAILUR_EventLog

This method writes a user defined string into the specified registered event log as a failure audit message.

Arguments

  1. Event log name as string
  2. Message source as string
  3. Message detail as string
Sample Code Example
public void Write_FAILUR_EventLog(string strLogName
                                , string strSource
                                , string strErrDetail)
 {
                if (this.IsExist(strLogName))
                {
                    System.Diagnostics.EventLog SQLEventLog = 
				new System.Diagnostics.EventLog();

                    try
                    {
                        SQLEventLog.Source = strLogName;
                        SQLEventLog.WriteEntry(Convert.ToString(strSource)
                                              + Convert.ToString(strErrDetail), 
					EventLogEntryType.FailureAudit );
                    }
                    catch (Exception ex)
                    {
                        SQLEventLog.Source = strLogName;
                        SQLEventLog.WriteEntry(Convert.ToString("FAILUR: ")
                                              + Convert.ToString(ex.Message), 
					EventLogEntryType.FailureAudit );
                    }
                    finally
                    {
                        SQLEventLog.Dispose();
                        SQLEventLog = null;
                    }
                }
  }

Write_SUCCESS_EventLog

This method writes a user defined string into the specified registered event log as a success audit message.

Arguments

  1. Event log name as string
  2. Message source as string
  3. Message detail as string
Sample Code Example
 public void Write_SUCCESS_EventLog(string strLogName
                                  , string strSource
                                  , string strErrDetail)
 {
                if (this.IsExist(strLogName))
                {
                    System.Diagnostics.EventLog SQLEventLog = 
				new System.Diagnostics.EventLog();

                    try
                    {
                        SQLEventLog.Source = strLogName;
                        SQLEventLog.WriteEntry(Convert.ToString(strSource)
                                              + Convert.ToString(strErrDetail), 
					EventLogEntryType.SuccessAudit );
                    }
                    catch (Exception ex)
                    {
                        SQLEventLog.Source = strLogName;
                        SQLEventLog.WriteEntry(Convert.ToString("FAILUR: ")
                                              + Convert.ToString(ex.Message), 
					EventLogEntryType.SuccessAudit );
                    }
                    finally
                    {
                        SQLEventLog.Dispose();
                        SQLEventLog = null;
                    }
                }
 }

Figure - 3 showing the output of the methods "Write_SUCCESS_EventLog" and "Write_FAILUR_EventLog".

Write_ERROR_EventLog

This method writes a user defined string into the specified registered event log as an error message.

Arguments

  1. Event log name as string
  2. Message source as string
  3. Message detail as string
Sample Code Example
public void Write_ERROR_EventLog(string strLogName
                               , string strSource
                               , string strErrDetail)
 {
                if (this.IsExist(strLogName))
                {
                    System.Diagnostics.EventLog SQLEventLog = 
					new System.Diagnostics.EventLog();

                    try
                    {
                        SQLEventLog.Source = strLogName;
                        SQLEventLog.WriteEntry(Convert.ToString(strSource)
                                              + Convert.ToString(strErrDetail), 
					EventLogEntryType.Error);
                    }
                    catch (Exception ex)
                    {
                        SQLEventLog.Source = strLogName;
                        SQLEventLog.WriteEntry(Convert.ToString("ERROR: ")
                                              + Convert.ToString(ex.Message), 
					EventLogEntryType.Error);
                    }
                    finally
                    {
                        SQLEventLog.Dispose();
                        SQLEventLog = null;
                    }
                } 
}

Figure - 4 showing the output of the method "Write_ERROR_EventLog".

OLE Automation

To know about OLE automation, I would like to request you to read my CodeProject article at this link.

Transact-SQL Script

-- =============================================
-- Author: Md. Marufuzzaman
-- Create date: 
-- Description: Create a new Windows Event Log File and	
-- Write a user define message to event log.
-- =============================================
--ALTER
CREATE PROCEDURE [dbo].[spEventLog]
AS
BEGIN

DECLARE @intResult INT
DECLARE @comHandle INT
DECLARE @errorSource VARCHAR(8000)
DECLARE @errorDescription VARCHAR(8000)
DECLARE @inputText VARCHAR(500)
DECLARE @Author VARCHAR(500)
DECLARE @isSuccess INT     

--Example(1): Call a simple function [GetAuthor] which is return a string type value.
-- Create COM object 
    EXEC @intResult = sp_OACreate 'OLEExample.ClsExample', @comHandle OUTPUT, 1

    IF (@intResult <> 0)
        BEGIN
        -- Error Handling 
            EXEC sp_OAGetErrorInfo @comHandle, @errorSource OUTPUT, 
				@errorDescription OUTPUT
            SELECT [Error Source] = @errorSource, [Description] = @errorDescription
            RETURN
        END
    
    -- Call a method into the component
    EXEC @intResult = sp_OAMethod @comHandle, 'GetAuthor',@Author OUTPUT

    SELECT @Author
--End of Example(1)

--Example(2): Calling a function [CreateLog] to create a new windows 
--event log name as "OLEExample".
SET @isSuccess = -1
IF (@intResult <> 0)
        BEGIN
        -- Error Handling 
            EXEC sp_OAGetErrorInfo @comHandle, @errorSource OUTPUT, 
					@errorDescription OUTPUT
            SELECT [Error Source] = @errorSource, [Description] = @errorDescription
            RETURN
        END

     -- Call a method into the component
      EXEC @intResult = sp_OAMethod @comHandle,
	'CreateLog',@isSuccess OUTPUT, 'OLEExample'

    IF (@intResult <> 0)
        BEGIN
        -- Error Handling 
            EXEC sp_OAGetErrorInfo @comHandle, 
		@errorSource OUTPUT, @errorDescription OUTPUT
            SELECT [Error Source] = @errorSource, [Description] = @errorDescription
            RETURN
        END
    
     SELECT @isSuccess -- IF the value of @isSuccess is 1 that indicates new event 
                       -- log is successfully created, otherwise fail to create.

--End of Example(2)

--Example(3): Calling a function to write your information into the event log.

IF (@intResult <> 0)
        BEGIN
        -- Error Handling 
            EXEC sp_OAGetErrorInfo @comHandle, @errorSource OUTPUT, 
					@errorDescription OUTPUT
            SELECT [Error Source] = @errorSource, [Description] = @errorDescription
            RETURN
        END

     -- Call a method into the component
      EXEC @intResult = sp_OAMethod @comHandle,
	'Write_INFO_EventLog',@isSuccess OUTPUT, 'OLEExample',_
		'Message source. ','Your Message.'

    IF (@intResult <> 0)
        BEGIN
        -- Error Handling 
            EXEC sp_OAGetErrorInfo @comHandle, 
		@errorSource OUTPUT, @errorDescription OUTPUT
            SELECT [Error Source] = @errorSource, [Description] = @errorDescription
            RETURN
        END
    
--End of Example(3)

--Example(4): Calling a function to write your WARNING information into the event log.

IF (@intResult <> 0)
        BEGIN
        -- Error Handling 
            EXEC sp_OAGetErrorInfo @comHandle, @errorSource OUTPUT, 
					@errorDescription OUTPUT
            SELECT [Error Source] = @errorSource, [Description] = @errorDescription
            RETURN
        END

     -- Call a method into the component
      EXEC @intResult = sp_OAMethod @comHandle,
	'Write_WARNING_EventLog',@isSuccess OUTPUT, _
	'OLEExample','WARNING Message source. ','Your WARNING Message.'

    IF (@intResult <> 0)
        BEGIN
        -- Error Handling 
            EXEC sp_OAGetErrorInfo @comHandle, 
		@errorSource OUTPUT, @errorDescription OUTPUT
            SELECT [Error Source] = @errorSource, [Description] = @errorDescription
            RETURN
        END
    
--End of Example(4)

--Example(5): Calling a function to write your WARNING information into the event log.

IF (@intResult <> 0)
        BEGIN
        -- Error Handling 
            EXEC sp_OAGetErrorInfo @comHandle, @errorSource OUTPUT, 
					@errorDescription OUTPUT
            SELECT [Error Source] = @errorSource, [Description] = @errorDescription
            RETURN
        END

     -- Call a method into the component
      EXEC @intResult = sp_OAMethod @comHandle,
	'Write_FAILUR_EventLog',@isSuccess OUTPUT, 'OLEExample',_
		'FAILUR Message source. ','Your FAILUR Message.'

    IF (@intResult <> 0)
        BEGIN
        -- Error Handling 
            EXEC sp_OAGetErrorInfo @comHandle, 
		@errorSource OUTPUT, @errorDescription OUTPUT
            SELECT [Error Source] = @errorSource, [Description] = @errorDescription
            RETURN
        END
    
--End of Example(5)

--Example(6): Calling a function to write your SUCCESS Audit 
--information into the event log.

IF (@intResult <> 0)
        BEGIN
        -- Error Handling 
            EXEC sp_OAGetErrorInfo @comHandle, @errorSource OUTPUT, 
					@errorDescription OUTPUT
            SELECT [Error Source] = @errorSource, [Description] = @errorDescription
            RETURN
        END

     -- Call a method into the component
      EXEC @intResult = sp_OAMethod @comHandle,
	'Write_SUCCESS_EventLog',@isSuccess OUTPUT, 'OLEExample',_
	'SUCCESS Audit Message source. ','Your SUCCESS Audit Message.'

    IF (@intResult <> 0)
        BEGIN
        -- Error Handling 
            EXEC sp_OAGetErrorInfo @comHandle, 
		@errorSource OUTPUT, @errorDescription OUTPUT
            SELECT [Error Source] = @errorSource, [Description] = @errorDescription
            RETURN
        END
    
--End of Example(6)

--Example(7): Calling a function to write your ERROR information into the event log.

IF (@intResult <> 0)
        BEGIN
        -- Error Handling 
            EXEC sp_OAGetErrorInfo @comHandle, @errorSource OUTPUT, 
					@errorDescription OUTPUT
            SELECT [Error Source] = @errorSource, [Description] = @errorDescription
            RETURN
        END

     -- Call a method into the component
      EXEC @intResult = sp_OAMethod @comHandle,
	'Write_ERROR_EventLog',@isSuccess OUTPUT, 'OLEExample',_
	'ERROR Message source. ','Your ERROR Message.'

    IF (@intResult <> 0)
        BEGIN
        -- Error Handling 
            EXEC sp_OAGetErrorInfo @comHandle, 
		@errorSource OUTPUT, @errorDescription OUTPUT
            SELECT [Error Source] = @errorSource, [Description] = @errorDescription
            RETURN
        END
    
--End of Example(7)
--Example(8): Calling a function to write your ERROR information into the event log.

IF (@intResult <> 0)
        BEGIN
        -- Error Handling 
            EXEC sp_OAGetErrorInfo @comHandle, @errorSource OUTPUT, 
					@errorDescription OUTPUT
            SELECT [Error Source] = @errorSource, [Description] = @errorDescription
            RETURN
        END

     -- Call a method into the component
      EXEC @intResult = sp_OAMethod @comHandle,
	'Write_AUTHOR_EventLog',@isSuccess OUTPUT, 'OLEExample',_
	'[dbo].[spEventLog]. ','AUTHOR: MD. MARUFUZZAMAN'

    IF (@intResult <> 0)
        BEGIN
        -- Error Handling 
            EXEC sp_OAGetErrorInfo @comHandle, 
		@errorSource OUTPUT, @errorDescription OUTPUT
            SELECT [Error Source] = @errorSource, [Description] = @errorDescription
            RETURN
        END
    
--End of Example(8)

    -- Release the reference to the COM object */
    EXEC sp_OADestroy @comHandle

END
GO

Conclusion

I hope that this article might be helpful to you. Enjoy!

Reference

Points of Interest

When registering COM+, we must use the following command by using Visual Studio command prompt.

Command:

C:\Program Files\Microsoft Visual Studio 9.0\VC> 
	RegAsm OLEExample.dll /tlb:OLEExample.tlb /codebase 

Note: Do not use regsvr32 for registering COM+.

History

  • 23rd August 2009: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Md. Marufuzzaman
CEO
Bangladesh Bangladesh
A highly experienced leader with successful track record of software development, product innovations, brand management and corporate communication etc. Some successful product innovations have also achieved and awards “Most Valuable Professional” (MVP) at 2010 and 2011 by codeproject.com and also selected as a mentor of codeproject.com. Published over 100 technical articles in various software development resource sites (i.e., codeprojetc.com, Microsoft MSDN, and IEEE & IBM (In progress)) and various IT Forums, Blogs etc.
 
Over ten years of professional experiences in ICT field having extensive experience in formulating corporate vision and long term strategy. Leading development related functions including design, development, services, data management and analytics, customer experience management, content services, digital analytics and optimization.
 
An individual with results-driven approach and relentless in pursuit of excellence from a business and organizational standpoint. Believes in transparency, commitment and teamwork.
 
Expertise: Software/Solution Architect, SaaS platform base application, Large scale win32/web based business software solutions, enterprise applications, integration, etc.
 
Technologies/Tools: Microsoft.Net version 05/08/10/12, Microsoft SQL Server version 7/2K/05/08/12 , Oracle version 10/11, MySql version 5.1, 5.5, PS2, Visual C#, R, VB.NET, ASP.NET, PHP, API, MVC, WebAPI , Add-In Visual Basic etc.,. I have also more than two years’ of strong experience in mobile-VAS (platform development).
 
Points of Interest: Technology and research & development especially focused on business functionalities and social business areas as well, few stuff including:
 
1.R&D on new techniques as required to increase business revenue.
2.Urban and rural sectors to improve people’s lifestyle, better medical facilities, education, social business etc., using innovative technical solutions.
3.Research and innovative product development.
4.Etc.
Follow on   Twitter   LinkedIn

Comments and Discussions

 
GeneralMy vote of 5 PinmemberАslam Iqbal3-Aug-12 11:06 
Generalcannot oompile OLEExample in VS2010... "sKey.pfx" errors PinmemberRedDK25-May-11 8:53 
GeneralRe: cannot oompile OLEExample in VS2010... "sKey.pfx" errors PinmvpMd. Marufuzzaman25-May-11 9:33 
Generalresult not reasult.. Pinmemberpkoftear3-Sep-09 16:26 
GeneralRe: result not reasult.. PingroupMd. Marufuzzaman5-Sep-09 1:37 
GeneralGood one ! PinmvpAbhijit Jana31-Aug-09 19:21 
This is good one ! have my 5 . Thumbs Up | :thumbsup:
 
Abhijit Jana | Codeproject MVP
Web Site : abhijitjana.net
Don't forget to click "Good Answer" on the post(s) that helped you.

GeneralRe: Good one ! PingroupMd. Marufuzzaman31-Aug-09 21:04 
GeneralRe: Good one ! PinmvpAbhijit Jana1-Sep-09 18:17 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140827.1 | Last Updated 22 Aug 2009
Article Copyright 2009 by Md. Marufuzzaman
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid