SP_Oacreate and SP_OAMethod Invocation limit

Question

SP_Oacreate and SP_OAMethod Invocation limit

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

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

Orlando Colamatteo SSC Guru Points: 182293 More actions · Answer 1

I just learned about MAX_ODSOLE_OBJECTS. I traced your code and it looks like you're destroying all your created objects, i.e. no leaks, but it is quite odd that you;re dying on the call to sp_OAMethod and not on an spOA_Create.

Some relevant links:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=82456

http://mssql.meetholland.com/message/30123.aspx

May I suggest that you convert this code to use SQLCLR?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 2

Perhaps I'm misunderstanding something obvious but I don't understand why someone is building ADO result sets using loops, dynamic sql, and OLE Automation for this. It seems like a SELECT statement with correct criteria and a CASE statement or two would more easily solve this problem not to mention being infinitely more scalable or being much, much faster.

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

David Gilkinson Ten Centuries Points: 1334 More actions · Answer 3

Ole is used as the data records are coming from various remote servers which are not able to be linked to our database. The code example here is a specific implementation made by the coder. The actual COM+ object itself is of course more generic and does more than running a simple dynamic query

David Gilkinson Ten Centuries Points: 1334 More actions · Answer 4

Thanks for the links and your time.. Our com+ object is old school C++ and will shortly be upgraded to c# or vb in the CLR. We will rework the code for this problem and shift the COM+ calls to the application and lift it out of the database for the time being.. Thanks for the comments