Click here to monitor SSC
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
Mr or Mrs. 500
Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)

Group: General Forum Members
Points: 597 Visits: 266
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
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8231 Visits: 14368
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-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44962 Visits: 39862
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
David Gilkinson
David Gilkinson
Mr or Mrs. 500
Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)

Group: General Forum Members
Points: 597 Visits: 266
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
Mr or Mrs. 500
Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)

Group: General Forum Members
Points: 597 Visits: 266
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