n last month’s 10-Minute Solution, we looked at how to use the OLE automation stored procedures that come with SQL Server to call an object’s properties. These extended stored procedures allow you to call properties and methods from any COM component, including those written in Visual Basic.
This time, we’ll look at sp_OAMethod, which calls an object’s method and is similar to sp_OAGetProperty. A reader alerted me to some problems that can occur when using these stored procedures to call COM objects, so I’ll also discuss how SQL Server functions internally when you create these objects. I’ll explain some of the problems you could encounter and how to work around them.
The sp_OAMethod stored procedure is used to call an object method, and it takes four parameters:
- The object token
- The method name
- The method’s return value
- Any parameters that the method requires
The following code uses the Application object from the Word object model and calls the CheckSpelling method:
DECLARE @Object intDECLARE @Hresult intDECLARE @Word varchar(255)DECLARE @SpelledCorrectly bitDECLARE @ErrorSource varchar (255)DECLARE @ErrorDesc varchar (255)SET @Word = 'philanthropist'--SET @Word = 'filanthropist'--Create the objectEXEC @Hresult = sp_OACreate 'Word.Application', @Object OUT --Call the object's property and return the valueEXEC @Hresult = sp_OAMethod @Object, 'CheckSpelling', @SpelledCorrectly OUT, @Word--Destroy the object EXEC @Hresult = sp_OADestroy @ObjectIF @Hresult <> 0BEGIN EXEC sp_OAGetErrorInfo @Object, @ErrorSource OUT, @ErrorDesc OUT PRINT 'Error Occurred Calling Object: ' + @ErrorSource + ' ' + @ErrorDesc RETURNENDIF @SpelledCorrectly = 1BEGIN PRINT 'The word ' + @Word + ' was spelled correctly.'ENDELSE BEGIN PRINT 'The word ' + @Word + ' was spelled incorrectly.'END
The first parameter is the Word object created with the sp_OACreate stored procedure. The next parameter is the method name, which is CheckSpelling in this case. The variable @SpelledCorrectly contains the return value of the method, and is defined as bit because the return value of the CheckSpelling method is Boolean. The remaining parameter is the only one this method requires, and it contains the word to be checked.
By default, SQL Server loads COM objects as in-process servers. This means that they are loaded in the SQL Server process space and have full access to this memory address. A misbehaved DLL that doesn’t handle memory correctly could overwrite a memory location used by another process or leak resources, resulting in memory exception errors. For this reason, Microsoft enables users to instantiate COM objects as out-of-process servers, which means they actually are created outside the SQL Server process space. This process, by which an object is loaded and accessed out-of-process, is called remoting. SQL Server accomplishes it through the use of DCOM. In this case, we loaded the object in a surrogate process space and marshaled calls between the DLL (the server) and SQL Server (which is the client in this case).
To load a DLL as an out-of-process server, you must specify the context as a parameter when the object is created using sp_OACreate. The possible context values are:
In-process OLE server
Out-of-process OLE server
Both in-process and out-of-process server allowed
When an object is loaded out of process, it has no access to SQL Server’s memory space. The following code explicitly tells SQL Server to create the Word object out of process:
--Create the objectEXEC @Hresult = sp_OACreate 'Word.Application', @Object OUT, 4
In this Solution, we’ve taken a more in-depth look at the OLE automation procedures and some of the problems you might encounter when using them. Whether you load components in process or out of process, there’s no substitute for thorough testing. For more information on issues with the OLE automation stored procedures see Microsoft Knowledge Base articles Q198891 and Q180780.