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.

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.