Browse DevX
Sign up for e-mail newsletters from DevX


How to Use sp_OAMethod

The sp_OAMethod stored procedure is used to call an object method. The article explains some of the problems you could encounter and how to work around them.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

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 int DECLARE @Hresult int DECLARE @Word varchar(255) DECLARE @SpelledCorrectly bit DECLARE @ErrorSource varchar (255) DECLARE @ErrorDesc varchar (255) SET @Word = 'philanthropist' --SET @Word = 'filanthropist' --Create the object EXEC @Hresult = sp_OACreate 'Word.Application', @Object OUT --Call the object's property and return the value EXEC @Hresult = sp_OAMethod @Object, 'CheckSpelling', @SpelledCorrectly OUT, @Word --Destroy the object EXEC @Hresult = sp_OADestroy @Object IF @Hresult <> 0 BEGIN EXEC sp_OAGetErrorInfo @Object, @ErrorSource OUT, @ErrorDesc OUT PRINT 'Error Occurred Calling Object: ' + @ErrorSource + ' ' + @ErrorDesc RETURN END IF @SpelledCorrectly = 1 BEGIN PRINT 'The word ' + @Word + ' was spelled correctly.' END ELSE 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:

Parameter Value



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 object EXEC @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.

Dianne Siebold is a consultant specializing in Visual Basic and SQL Server programming. She is also the author of the "Visual Basic Developer's Guide to SQL Server" (Sybex). Reach her by e-mail here.
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date