Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SP_Oacreate and SP_OAMethod Invocation limit Expand / Collapse
Author
Message
Posted Wednesday, September 5, 2012 4:52 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 20, 2014 4:30 AM
Points: 342, Visits: 122
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
Post #1354431
Posted Thursday, September 6, 2012 1:51 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:52 PM
Points: 7,079, Visits: 12,569
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
Post #1355601
Posted Thursday, September 6, 2012 6:18 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:06 AM
Points: 36,712, Visits: 31,163
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1355674
Posted Friday, September 7, 2012 2:11 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 20, 2014 4:30 AM
Points: 342, Visits: 122
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
Post #1355807
Posted Friday, September 7, 2012 2:13 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 20, 2014 4:30 AM
Points: 342, Visits: 122
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
Post #1355811
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse