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

How to Integrate an OLE Object with SQL Server

, 15 Jun 2009 CPOL
Rate this:
Please Sign up or sign in to vote.
This article will demonstrate how we can integrate T-SQL with an external application.

Introduction

This article will demonstrate how we can integrate T-SQL with an external application.

Background

When we need to store or retrieve data in SQL server database, we will go for T-SQL. But the thing is how can we integrate T-SQL with an external application? For example, we want to integrate SQL server with Microsoft Word, Excel or Microsoft exchange server as well. So how can we achieve this?

Using the Code

There are seven extended stored procedures to call external applications like DLLs. The name and description of those stored procedures are given below:

Stored Procedure Description
sp_OACreate Creates an instance of the OLE object on an instance of Microsoft SQL Server
sp_OADestroy Destroys a created OLE object
sp_OAGetErrorInfo Obtains OLE Automation error information
sp_OAGetProperty Gets a property value of an OLE object
sp_OASetProperty Sets a property of an OLE object to a new value
sp_OAMethod Calls a method of an OLE object
sp_OAStop Stops the server-wide OLE Automation stored procedure execution environment

Reference: SQL Server Books Online.

Let’s take a simple example to implement this. Our requirement will be accessing a third party DLL from a SQL server stored procedure.

The following are the two functions which are developed in C# .NET.
Function (a) getOSPlatform will return the System platform and the function (b) writeToFile will write text into a text file called CLRTempE.txt with the value given by the parameter.

Sample COM+ Code (C#. NET)

public string getOSPlatform()
{    // Return the System Platform. 
      return Convert.ToString(System.Environment.OSVersion.Platform);      
}
          
public int writeToFile(
                        string strPath
                      , string strText)
{
    int intResult = -1;
    try
    {
        if (strPath != "")
        {
            if (System.IO.File.Exists(strPath))
            {
                FileStream stream = new FileStream(
                                            strPath
                                          , FileMode.Open
                                          , FileAccess.Write);

                StreamWriter writer = new StreamWriter(stream);
                writer.BaseStream.Seek(0, SeekOrigin.End);
                writer.Write("TimeStamp: [ "
                                    + Convert.ToString(System.DateTime.Now)
                                    + " ] "
                                    + strText
                                    + " || (:-> || Info ! [Successfully inserted.]");
                writer.Write(Environment.NewLine);
                writer.Flush();
                writer.Close();

              }
               else
                    {
                        FileStream stream = new FileStream(
                            strPath
                          , FileMode.CreateNew
                          , FileAccess.Write);

                        StreamWriter writer = new StreamWriter(stream);
                        writer.BaseStream.Seek(0, SeekOrigin.End);
                        writer.Write("TimeStamp: [ "
                                    + Convert.ToString(System.DateTime.Now)
                                    + " ] "
                                    + strText
                                    + " || (:-> || Info ! [Successfully inserted.]");
                        writer.Write(Environment.NewLine);
                        writer.Flush();
                        writer.Close();

                    }
                    intResult = 0;
         }
    }
         catch (Exception ex)
         { throw ex; }

            return intResult;
}

Sample SQL Stored Procedure

--EXEC dbo.spCLRExample
CREATE PROCEDURE dbo.spCLRExample
AS
BEGIN

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

    set @inputText = 'This is a simple text.' 

    EXEC @intResult = sp_OACreate 'CLRExample.Example', @comHandle OUTPUT, 4

    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, 'getOSPlatform',@OSPlatform OUTPUT

    Print @OSPlatform

    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, _
	'writeToFile',@isSuccess OUTPUT, 'C:\CLRTempE.txt', @inputText

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

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

END

GO

Now we are going to discuss how the above code works. sp_OACreate will create an instance of the DLL to SQL Server. If it is a successfully created output, that will be 0. If it is non-zero, then send it to sp_OAGetErrorInfo and get the error messages. sp_OAMethod is to call the method of the DLL. In the first case, we are calling the method getOSPlatform which only has a returned value. The last parameter of sp_OACreate must be either of 1, 4 or 5. It specifies the execution context in which the newly created OLE object runs. If specified, this value must be one of the following:

  • 1 = In-process (.dll) OLE server only
  • 4 = Local (.exe) OLE server only
  • 5 = Both in-process and local OLE server allowed

If not specified, the default value is 5.

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 may damage SQL Server memory or resources and cause unpredictable results, such as a SQL Server access violation.

When we 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. Finally sp_OADestroy will destroy a created OLE object in the SQL Server.

Reference SQL Server Books Online.

Points of Interest 

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

Commands:
C:\Program Files\Microsoft Visual Studio 9.0\VC> RegAsm CLRExample.dll \ tlb: CLRExample.tlb \ codebase
Note: Do not use regsvr32 for registering the COM+.

Conclusion

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

History

  • 15th June, 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
Architect
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

 
QuestionNeed help with a strange issue with a COM dll in SQL 2008 PinmemberMember 429190620-Jun-13 23:33 
GeneralMy vote of 5 Pinmembernevergiveupc3-Mar-13 3:53 
Last minute I just think how the function can be recognised by the procedure,but now I know it,Through RegAsm,Just Fantastic!
GeneralRe: My vote of 5 PinmentorMd. Marufuzzaman3-Mar-13 4:32 
GeneralMy vote of 5 Pinmembermanoj kumar choubey17-Apr-12 3:29 
GeneralRe: My vote of 5 PinmentorMd. Marufuzzaman3-Mar-13 4:31 
GeneralThis is a .dll that I made with VS2010, but I don't know how to use it. PinmemberRedDK25-May-11 9:47 
GeneralHits Error "OLSOLE Extended Procedures" Pinmembercheeleongliau3-Feb-10 23:50 
GeneralRe: Hits Error "OLSOLE Extended Procedures" PinmvpMd. Marufuzzaman4-Feb-10 3:54 

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 | Terms of Use | Mobile
Web01 | 2.8.141216.1 | Last Updated 15 Jun 2009
Article Copyright 2009 by Md. Marufuzzaman
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid