Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Calling COM From T-SQL

By Dinesh Asanka,

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 like MathCoProcessorAvailable, 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.

Total article views: 11053 | Views in the last 30 days: 9
 
Related Articles
FORUM

Variable Declaration

Variable Declaration

BLOG

SQL Server- Output clause

Output Clause:- The SQL SERVER 2005 gives us an Output Clause which gives us the information about e...

FORUM

Must declare the variable '@ComputerName'.

Must declare the variable '@ComputerName'.

FORUM

Table variable as Output Parameter

Can I declare table variable as output parameter in stored procedure?

FORUM

declaring global variables

declaring global variables

Tags
miscellaneous    
programming    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones