SP_Oacreate and SP_OAMethod Invocation limit

  • 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

  • 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

  • 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)

  • 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

  • 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

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

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