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
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.