SP_Oacreate and SP_OAMethod Invocation limit

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » SP_Oacreate and SP_OAMethod Invocation limit

SP_Oacreate and SP_OAMethod Invocation limit

Rate Topic

Display Mode

Topic Options

Author

Message

David Gilkinson

Posted Wednesday, September 05, 2012 4:52 AM

Old Hand

Group: General Forum Members
Last Login: Friday, May 10, 2013 6:52 AM
Points: 342, Visits: 102

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

opc.three

Posted Thursday, September 06, 2012 1:51 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:27 PM
Points: 6,735, Visits: 11,788

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1355601

Jeff Moden

Posted Thursday, September 06, 2012 6:18 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 4:51 PM
Points: 32,923, Visits: 26,811

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1355674

David Gilkinson

Posted Friday, September 07, 2012 2:11 AM

Old Hand

Group: General Forum Members
Last Login: Friday, May 10, 2013 6:52 AM
Points: 342, Visits: 102

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

David Gilkinson

Posted Friday, September 07, 2012 2:13 AM

Old Hand

Group: General Forum Members
Last Login: Friday, May 10, 2013 6:52 AM
Points: 342, Visits: 102

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 »

Permissions