SP_Oacreate and SP_OAMethod Invocation limit

Question

SP_Oacreate and SP_OAMethod Invocation limit

David Gilkinson

Ten Centuries

Points: 1334
More actions
September 5, 2012 at 4:52 am

#261128

Does anyone know if there is a limit on the number of objects or methods that can be created/called with sp_OACreate within a single stored procedure? We are getting a 10077 error after 255 calls to sp_OAcreate within a stored procedure. Each object is destroyed after the results have been returned... The 255th Create works, but the error is returned on the next SP_OAMethod. The stored proc calls the following function:
create function dbo.fn_GetTradeUnits(@Fund_Code varchar(8),
@AssetID varchar(9),
@TransTypeCode char(1),
@StartDate varchar(10),
@EndDate varchar(10)
)
returns decimal(18,6)
with execute as owner
as
begin
declare @error int,
@count int,
@Ret int,
@SQLState varchar(8000),
@EDRObject int,
@EDRRet int,
@rs int,
@Sharesnumeric(18,6),
@TempShares varchar(255)
-- Assume it is going to work --
select @Ret = 0,
@Shares = null
if @StartDate is null --No point in even trying - this could happen if this is a first event setup in the application
begin
return @Shares
end
-- Build the SQL --
select @SQLState = 'select sum(shrpar_qty)' +
' from v_txn_trans_adjst' +
' where Fund_ID = ''' + @Fund_Code + '''' +
' and asset_id = ''' + @AssetID + '''' +
' and txn_type_bs_cd = ''' + @TransTypeCode + '''' +
' and cncl_ind <> ''Y''' +
' and trd_dt > ''' + @StartDate + '''' +
' and trd_dt <= ''' + @EndDate + ''''
-- Create an ADO Recordset Object --
exec @Ret = sp_OACreate 'ADODB.RecordSet', @rs OUT
if @Ret != 0
begin
return @Shares
end
-- Create an EDR Object --
exec @Ret = sp_OACreate 'SSB_RCC_EDR_DSM.EDRGetData', @EDRObject OUT
if @Ret != 0
begin
exec sp_OADestroy @rs
return @Shares
end
-- Run SQL Against EDR Component and return the ADO Recordset -- Code Dies here on 255th call
exec @Ret = sp_OAMethod @EDRObject, 'RunSQL', @EDRRet OUT, 'FDR', @SQLState, @rs OUT
if @Ret != 0
begin
exec sp_OADestroy @rs
exec sp_OADestroy @EDRObject
return @Shares
end
if @EDRRet != 0
begin
exec sp_OADestroy @rs
exec sp_OADestroy @EDRObject
return @Shares
end
-- Process the Recordset to get the Exchange Rate --
declare @Moving integer
exec @Moving = sp_OAMethod @rs, 'MoveFirst'
while @Moving = 0
Begin
exec @Ret = sp_OAGetProperty @rs, 'Fields.Item(0).Value', @TempShares OUT
if @Ret != 0
begin
exec sp_OADestroy @rs
exec sp_OADestroy @EDRObject
return @Shares
end
-- Check if we have an Exchange Rate --
select @TempShares = rtrim(@TempShares)
if isnull(@TempShares, '') != '' and isnumeric(@TempShares) != 0
begin
-- Store it --
select @Shares = isnull(@Shares,0.0) + convert(decimal(18,6), @TempShares)
end
exec @Moving = sp_OAMethod @rs, 'MoveNext'
end
-- Tidy Up --
exec sp_OADestroy @rs
exec sp_OADestroy @EDRObject
return @Shares
end
Thanks Dave

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

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

Orlando Colamatteo SSC Guru Points: 182279 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: 1004356 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