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