Safety of Using sp_OA

Question

Safety of Using sp_OA

Adam Campbell

SSC Eights!

Points: 982
More actions
May 5, 2005 at 6:09 pm

#92989

I have Xml order files that I create from SQL. I recently created a SQL script that utilizes sp_OA to create and write the Xml files. Afterwards I read that sp_OA can cause performance issues and even SQL server crashes. I am going to be using this script throughout the day on servers across the country, so I need to know is sp_OA programming in SQL that dangerous????
Thanks,
Adam

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 1

Well, dangerous can ean different things, but in a way yes. The sp_OA* procs have always been known as 'troublemakers'. First of all they themselves have had several fixes for memoryleaks and other issues in service packs over the years, so who knows how well they are functioning at any moment. Secondly, if not 100% correctly used, their usage can mean leaked memory. If you are not correctly removing objects that are created in memory then you could run out of memory soon. For instance if your proc that creates the object encounters a problem and does not remove the object from memory.

Also note that they often allocate memory from the MemToLeave region, which is a) much smaller than the large data and procedure cache area, and b) used for several other important allocations. My recommendation would be to create the xml files in an outside application that reads data from SQL Server and then processes the xml.

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 2

Ken Henderson just posted a very good blog post describing these dangers.

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 3

Hopefully, SP4 will fix some of the memory leaks. It came out this last Friday.

The problem in our shop used to be that no-one did a code review of other's work. When we did start code reviews, we found that a lot of people forgot one of the most important things about sp_OA's... they forgot to close the connection and we started having some sever connection allocation problems. The fix, of course was to add code to close the connections but that didn't solve the memory leak problems. We reverted back to Linked Servers (which have their own set of nuances or is that nuisances?) to solve the memory leaks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)