How to Integrate an OLE Object with SQL Server

• Download script - 739 B
• Download source - 10.61 KB  

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:

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

• 15^th June, 2009: Initial post