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
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
CEO
Bangladesh Bangladesh
A highly experienced leader with successful track record of brand management, product innovations and corporate communication. 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, IEEE & IBM -In progress) and verious IT Forums, Blogs as well.
 
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, 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.
 
Specialties: Developing client-server with multi-tier application, database design, web based business software solutions, enterprise applications, google analytics, etc., for Windows / Mac using Microsoft SQL Server, Oracle, MySql, PS, 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: Research and development where technologies used in business research areas, including (few stuff):
 
1. Urban and rural sectors to improve people’s lifestyle, better medical facilities, education, social business etc.
2. Biometric device integration with several platforms.
3. Social security services.
4. Eager to work with new technology.
5. 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 22:33 
GeneralMy vote of 5 Pinmembernevergiveupc3-Mar-13 2:53 
GeneralRe: My vote of 5 PinmentorMd. Marufuzzaman3-Mar-13 3:32 
GeneralMy vote of 5 Pinmembermanoj kumar choubey17-Apr-12 2:29 
GeneralRe: My vote of 5 PinmentorMd. Marufuzzaman3-Mar-13 3:31 
GeneralThis is a .dll that I made with VS2010, but I don't know how to use it. PinmemberRedDK25-May-11 8:47 
GeneralHits Error "OLSOLE Extended Procedures" Pinmembercheeleongliau3-Feb-10 22:50 
GeneralRe: Hits Error "OLSOLE Extended Procedures" PinmvpMd. Marufuzzaman4-Feb-10 2: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 | Mobile
Web04 | 2.8.140821.2 | Last Updated 15 Jun 2009
Article Copyright 2009 by Md. Marufuzzaman
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid