How to Use sp_OAMethod

How to Use sp_OAMethod

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:

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

Share the Post:
Heading photo, Metadata.

What is Metadata?

What is metadata? Well, It’s an odd concept to wrap your head around. Metadata is essentially the secondary layer of data that tracks details about the “regular” data. The regular

XDR solutions

The Benefits of Using XDR Solutions

Cybercriminals constantly adapt their strategies, developing newer, more powerful, and intelligent ways to attack your network. Since security professionals must innovate as well, more conventional endpoint detection solutions have evolved

AI is revolutionizing fraud detection

How AI is Revolutionizing Fraud Detection

Artificial intelligence – commonly known as AI – means a form of technology with multiple uses. As a result, it has become extremely valuable to a number of businesses across

AI innovation

Companies Leading AI Innovation in 2023

Artificial intelligence (AI) has been transforming industries and revolutionizing business operations. AI’s potential to enhance efficiency and productivity has become crucial to many businesses. As we move into 2023, several