Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2006
    Posts
    17

    Question Unanswered: Help--A error about memory!

    I use linkedserver to get data from Oracle, but sometimes I get the error below:

    Server: Msg 7399, Level 16, State 1, Procedure GetDataFromERP, Line 21
    OLE DB provider 'OraOLEDB.Oracle' reported an error.
    2006-02-09
    [OLE/DB provider returned message: ]
    [OLE/DB provider returned message: ROW-00001: Cannot allocate memory]
    OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' ICommandText::Execute returned 0x80004005: ].


    I think maybe it is because the data is very large.
    How do I solve this problem?

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What is the statement you are executing against the linked server?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2006
    Posts
    17
    The statement is "select ...... from openquery(.......)"
    The provider of linkedserver is "Oracle provider for OLE DB"

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    That doesn't help much. That is what you are executing locally.

    What is the statement you are executing against the linked server?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Feb 2006
    Posts
    17
    This is the main statment of my store procedure:


    INSERT INTO
    [ARA].[dbo].[AgingData]
    ([RO], [NS], [CustomerID], [CustomerCode], [CustomerName],
    [TransactionalCurrency], [ROCurrency], [CurrentByTrx], [Over1_15ByTrx],
    [Over16_30ByTrx], [Over31_60ByTrx], [Over61_90ByTrx], [Over90ByTrx],
    [CurrentByRO], [Over1_15ByRO], [Over16_30ByRO], [Over31_60ByRO],
    [Over61_90ByRO], [Over90ByRO], [CurrentByUSD], [Over1_15ByUSD],
    [Over16_30ByUSD], [Over31_60ByUSD], [Over61_90ByUSD], [Over90ByUSD],
    [TotalAmountByTraCur], [TotalAmountByROCur], [TotalAmountByUSD],
    [ProductLine], [AgedDate], [ProcessDate], [Tenor_Flag])
    SELECT [RO_NAME], [NS_NAME], [CUST_ID], [CUST_NO], [CUST_NAME], [TRX_CURR], [RO_CURR],
    [TRX_AMOUNT_BUCKET_1], [TRX_AMOUNT_BUCKET_2], [TRX_AMOUNT_BUCKET_3],
    [TRX_AMOUNT_BUCKET_4], [TRX_AMOUNT_BUCKET_5], [TRX_AMOUNT_BUCKET_6],
    [RO_CURR_AMOUNT_BUCKET_1], [RO_CURR_AMOUNT_BUCKET_2], [RO_CURR_AMOUNT_BUCKET_3],
    [RO_CURR_AMOUNT_BUCKET_4], [RO_CURR_AMOUNT_BUCKET_5], [RO_CURR_AMOUNT_BUCKET_6],
    [USD_CURR_AMOUNT_BUCKET_1], [USD_CURR_AMOUNT_BUCKET_2], [USD_CURR_AMOUNT_BUCKET_3],
    [USD_CURR_AMOUNT_BUCKET_4], [USD_CURR_AMOUNT_BUCKET_5], [USD_CURR_AMOUNT_BUCKET_6],
    [TOTAL_TRX_AMOUNT], [TOTAL_AMOUNT_WITH_RO_CURR], [TOTAL_AMOUNT_WITH_USD_CURR],
    [PRODLINE], [AGED_DATE], [PROCESS_DATE], 1
    FROM OPENQUERY(ORACLE_BQE,
    'SELECT RO_NAME, NS_NAME, CUST_ID, CUST_NO, CUST_NAME, TRX_CURR, RO_CURR,
    TRX_AMOUNT_BUCKET_1, TRX_AMOUNT_BUCKET_2, TRX_AMOUNT_BUCKET_3,
    TRX_AMOUNT_BUCKET_4, TRX_AMOUNT_BUCKET_5, TRX_AMOUNT_BUCKET_6,
    RO_CURR_AMOUNT_BUCKET_1, RO_CURR_AMOUNT_BUCKET_2, RO_CURR_AMOUNT_BUCKET_3,
    RO_CURR_AMOUNT_BUCKET_4, RO_CURR_AMOUNT_BUCKET_5, RO_CURR_AMOUNT_BUCKET_6,
    USD_CURR_AMOUNT_BUCKET_1, USD_CURR_AMOUNT_BUCKET_2, USD_CURR_AMOUNT_BUCKET_3,
    USD_CURR_AMOUNT_BUCKET_4, USD_CURR_AMOUNT_BUCKET_5, USD_CURR_AMOUNT_BUCKET_6,
    TOTAL_TRX_AMOUNT, TOTAL_AMOUNT_WITH_RO_CURR, TOTAL_AMOUNT_WITH_USD_CURR,
    PRODLINE, TO_CHAR(AGED_DATE) AS AGED_DATE, TO_CHAR(PROCESS_DATE) AS PROCESS_DATE
    FROM APPS.XXAR_ARA_AGE_DETAIL'
    )
    WHERE CONVERT(NVARCHAR(10), AGED_DATE) = @BEGINDATE


    I just get data from Oracle and insert them to the local table, it is not complicated. But there are another three similar statement in this store procedure.
    Last edited by yunkun_yang; 02-16-06 at 20:12.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Any way you can filter the data on the Oracle side? Try executing your statement as dynamic SQL, which will allow you to pass the @BEGINDATE value within your OPENQUERY statement, and should reduce the volume of data returned.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Feb 2006
    Posts
    17
    I have solved this problem by filtering data on the Oracle side!
    Thank you very much!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •