SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SP_Oacreate and SP_OAMethod Invocation limit


SP_Oacreate and SP_OAMethod Invocation limit

Author
Message
David Gilkinson
David Gilkinson
SSChasing Mays
SSChasing Mays (628 reputation)SSChasing Mays (628 reputation)SSChasing Mays (628 reputation)SSChasing Mays (628 reputation)SSChasing Mays (628 reputation)SSChasing Mays (628 reputation)SSChasing Mays (628 reputation)SSChasing Mays (628 reputation)

Group: General Forum Members
Points: 628 Visits: 267
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,
@Shares numeric(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
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14855 Visits: 14396
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
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85160 Visits: 41077
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
David Gilkinson
David Gilkinson
SSChasing Mays
SSChasing Mays (628 reputation)SSChasing Mays (628 reputation)SSChasing Mays (628 reputation)SSChasing Mays (628 reputation)SSChasing Mays (628 reputation)SSChasing Mays (628 reputation)SSChasing Mays (628 reputation)SSChasing Mays (628 reputation)

Group: General Forum Members
Points: 628 Visits: 267
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
David Gilkinson
SSChasing Mays
SSChasing Mays (628 reputation)SSChasing Mays (628 reputation)SSChasing Mays (628 reputation)SSChasing Mays (628 reputation)SSChasing Mays (628 reputation)SSChasing Mays (628 reputation)SSChasing Mays (628 reputation)SSChasing Mays (628 reputation)

Group: General Forum Members
Points: 628 Visits: 267
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search