Calling COM From T-SQL

Calling COM From T-SQL

Introduction

If you need to store data or retrieve stored in SQL Server, no second thought is necessary and you will go for T-SQL. However, how can you integrate T-SQL with external applications? For example if you need to integrate SQL Server with Microsoft Exchange Server or MS Word, how are you going to accomplish this? There are seven extended stored procedures to call external applications like DLLs.

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

Source :SQL Server BOL

Implementation

Let us take a simple example to implement this. Our requirement will be accessing a third party dll inside a table trigger.

From the above table, user will enter "a" and "b" and the"sum" is needed to have the results of "a + b", which is calculated by using a DLL function. Yes I know you can do this simple update statement. However, you will understand that I am going to do a simple demo using a DLL. In addition, users will enter ComString and that needs to be written to a text file.

The following are the two functions which are developed in VB. AddTwoNumbers will give you the sum of the two inputs while the WriteToFile will write to text file called COMTEXT.txt with the value given by the parameter. Basically one function have input,output while the other has only an input.

Public Function AddTwoNumbers (i As Integer, j As

Integer) As Integer

    AddTwoNumbers = (i + j)

End Function

 Public Sub WriteToFile(strText As String)

    Dim fso As New FileSystemObject

    Dim ts As TextStream

    Set ts = fso.CreateTextFile("C:\COMTEST.txt")

    ts.WriteLine (strText)

    ts.Close

End Sub

CREATE  TRIGGER [trgcom] ON [dbo].[TRIGGERCOM]

FOR INSERT, UPDATE

AS

DECLARE @retVal smallint

DECLARE @comHandle INT

DECLARE @errorSource VARCHAR(8000)

DECLARE @errorDescription VARCHAR(8000)

DECLARE @retString VARCHAR(100)

DECLARE @retTot smallint

DECLARE @nval1 smallint

DECLARE @nval2 smallint

DECLARE @ID int

 

Select @nval1 = a, @nval2 = b,@ID=id,@retString = t

from inserted

EXEC @retVal = sp_OACreate ' TestClass.TestClass', @comHandle

OUTPUT, 4

IF (@retVal <> 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 @retVal = sp_OAMethod @comHandle, 'WriteToFile',NULL, 

@retString

IF (@retVal <> 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 @retVal = sp_OAMethod @comHandle, 'AddTwoNumbers',@retTot

OUTPUT, @nval1,@nval2

IF (@retVal <> 0)

BEGIN

              

-- Error Handling

           

EXEC sp_OAGetErrorInfo @comHandle, @errorSource OUTPUT, @errorDescription

OUTPUT

           

SELECT [Error Source] = @errorSource, [Description] = @errorDescription

           

RETURN

END

-- Update table

update TRIGGERCOM  set [sum] = @rettot where id = @id

-- Release the reference to the COM object

EXEC sp_OADestroy @comHandle

Now we will see what the above code does.

sp_OACreate will create an instance of a the DLL to SQL Server. If it is successfully created output, the will be 0. If it is non-zero then you can send it to OAGetErrorInfo and get the error messages. sp_OAMethod is to call the method of the DLL. In first case you are calling the method WriteToFile which does not have any returned values. 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 you 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.

(From BOL)

sp_OAMethod @comHandle, 'WriteToFile',NULL,

@retString

@comHandle is the handle to the object which is the

output that you got from the sp_OACreate.

sp_OAMethod @comHandle, 'AddTwoNumbers',@retTot OUTPUT,

@nval1,@nval2

In this case you can see that two parameters at the end

of the function call. Finally sp_OADestroy will destroy a created OLE object

in the SQL Server. After that, T-SQL is used to update the tables.

More Examples

Let us see how we can use this further. In MS Word there are many properties, it is much better to know whether that property is available before doing an operation with regards to that specified property. You can check properties likeMathCoProcessorAvailable, MapiAvailable. You can find the MSWord properties

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbawd11/html/woobjApplication1_HV05209908.asp

What if you need to check spelling from SQL Server? Isn’t it useful to have this functionality from the SQL Server?

DECLARE @retVal int

DECLARE @comHandle  int

DECLARE @IsSpellingCorrect bit

DECLARE @errorSource VARCHAR(8000)

DECLARE @errorDescription VARCHAR(8000)

DECLARE @SpellWord varchar(255)

SET @SpellWord = 'Server'

EXEC @retVal = sp_OACreate 'Word.Application', @comHandle  OUT ,4

EXEC @retVal = sp_OAMethod @comHandle , 'CheckSpelling'

             , @IsSpellingCorrect OUT, @SpellWord

EXEC @retVal = sp_OADestroy @comHandle 

IF @retVal <> 0

 BEGIN          

EXEC sp_OAGetErrorInfo @comHandle, @errorSource OUTPUT, @errorDescription OUTPUT

 

SELECT [Error Source] = @errorSource, [Description] = @errorDescription

RETURN

 END

Print @IsSpellingCorrect

Above code will return you 1 if the @SpellWord is spelled correctly. You can create an stored procedure an use the word as a parameter.

You can print documents, create documents by the above methods. If you need the code for those functions drop me an email.

Other than the above there are more practical cases where you need this above automation scripts. For example if you need to communicate with the Windows services where you won’t find any SQL Server built-in methods, you can easily build an third party Activex DLL and call those methods from the SQL Server stored procedure.

Using 'CDO.Message', you can send emails from SQL Server. You can attach documents as well.

For More information you can visit http://support.microsoft.com/kb/152801/EN-US/

Conclusion

Basically, you can call whatever the method available in the OLE. This is very useful as you can integrate the other functionalities and making SQL Server a rich application platform.