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

Context

1

In-process OLE server

4

Out-of-process OLE server

5

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:
Share on facebook
Share on twitter
Share on linkedin

Overview

The Latest

technology leadership

Why the World Needs More Technology Leadership

As a fact, technology has touched every single aspect of our lives. And there are some technology giants in today’s world which have been frequently opined to have a strong influence on recent overall technological influence. Moreover, those tech giants have popular technology leaders leading the companies toward achieving greatness.

iOS app development

The Future of iOS App Development: Trends to Watch

When it launched in 2008, the Apple App Store only had 500 apps available. By the first quarter of 2022, the store had about 2.18 million iOS-exclusive apps. Average monthly app releases for the platform reached 34,000 in the first half of 2022, indicating rapid growth in iOS app development.

microsoft careers

Top Careers at Microsoft

Microsoft has gained its position as one of the top companies in the world, and Microsoft careers are flourishing. This multinational company is efficiently developing popular software and computers with other consumer electronics. It is a dream come true for so many people to acquire a high paid, high-prestige job