Results 1 to 12 of 12
  1. #1
    Join Date
    Jul 2004
    Posts
    12

    Angry Unanswered: Internally generated command failed to affect a row - SP using linked Sybase server

    As part of a nightly process, I must call a stored procedure that uses cursors to update a Sybase linked server from SQL Server. The stored proc was written by someone else and is intended to interface to a finance system to upload new transactions.

    The stored proc works fine most of the time but occasionally dies part-way through with the following confusing error message:

    [OLE/DB provider returned message: Internally generated command failed to affect a row.]
    OLE DB error trace [OLE/DB Provider 'Sybase.ASEOLEDBProvider' IRowsetChange:etData returned 0x80004005: ].
    Msg 7399, Level 16, State 1, Server ********, Procedure ********, Line 162
    OLE DB provider 'Sybase.ASEOLEDBProvider' reported an error.


    I initially ran this stored proc from a perl script via an ODBC link but in the futile hope of fixing this problem I tried manually executing it via isql.exe instead. Both return exactly the same error. I have also increased the OLE/DB query timeout to 15 minutes with no success in stopping the problem.

    There seems to be no connection I can spot between when it succeeds and when it fails. When scheduled to run just after midnight it fails about 30% of the time (at a time when there should be a light load on the servers). When run manually it almost always suceeds (I have only managed to get it to fail through running manually once in dozens of attempts).

    I can't seem to find anything relevant about this error via Google or any of my usual reference sources so hoped someone else might have a clue as to what the hell's going on.

    The stored procedure uses a cursor to traverse a SELECT statement then performs several INSERTs, UPDATEs and DELETEs depending upon each row processed and attempts (but usually fails) to perform a ROLLBACK upon an error.

    I'd appreciate any pointers in the right direction here. Is it because I'm performing cursor-based operations on a linked server? Is it due to the linked server being a Sybase server? Could it be due to locking problems or timeouts? Is my server in a bad mood and simply trying to taunt me? I'm tearing my hair out trying to find more information on this annoyingly intermittent problem and really appreciate any help or tales of similar errors.

    Thanks in advance
    Last edited by eviloverlord; 07-13-04 at 07:55.

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I think we'd have to have the contents of the offending procedure. I don't remember if Sybase is supporting DTC (what version of Sybase you're running?), but do you start your transaction as DISTRIBUTED?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Jul 2004
    Posts
    12
    SYNERGY_SERVER is the linked sybase server but the trans_det table is held on the MSSQL server.

    The new transactions to be transferred get put into the trans_det table then the SP is executed to interface to the financial system, which creates a batch header and batch lines for each product in the trans_det table.

    I doubt it's a problem with the query directly as it's only going wrong some 30% of the time and when I manually run the same query again it'll work the 2nd time.

    [tediously long script removed - suffice to say it referenced SYNERGY_SERVER directly through the 4-part naming convention]
    Last edited by eviloverlord; 07-23-04 at 07:11. Reason: removed tediously long script

  4. #4
    Join Date
    Sep 2003
    Posts
    364
    First off, always use the openquery() method when executing SQL against a Sybase linked server. If you don't performace will be totally unpredictable. I would re-write the proc to use openquery() and see if that fixes your problem.

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    ...Second, I don't see any transactional control you were referring to (BEGIN TRAN/DITRIBUTED TRAN...COMMIT TRAN)
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Jul 2004
    Posts
    12
    Thanks greatly for the tip about using openquery(), peterlemonjello. This SP was originally written by someone else to work on an entirely SQL Server based system and I had naively assumed I could simply reference a linked server instead to get it to interact with the sybase database we currently use for the finance system.

    Looking closely at the SP, there are a lot of variables used and passing these between Sybase and SQL Server using openquery is going to require a lot of alteration and result in lots of ugly query string concatenation and execution, or re-writing the whole damn thing from scratch. I have other projects to spend time on so really can't afford the time to do it that way.

    Consequently, I've decided to take the easy way out. If running queries without using openquery is the source of the problem, which now seems very likely, then moving our finance database to SQL Server could be the solution to the whole problem. Since this query was originally written to work on an entirely SQL-Server system I should then be able to remove the references to the linked server, sacrifice a chicken to appease the gods (I'm in Essex, UK so virgins are harder to find) and hope it all just works from then on.

    We'll just put up with the failures for another week until I can move the finance system to SQL Server then I'll let you know whether that was indeed the source of and solution to the problem.
    Last edited by eviloverlord; 07-14-04 at 08:08.

  7. #7
    Join Date
    Jul 2004
    Posts
    12
    Good point rdjabarov, you won't find the commit and rollback commands in there as the SP was written to do rollbacks using DELETE statements if an error is found. I have to admit it's not the way I'd have done it had I been the creator but it was supposedly tested and working on an entirely SQL Server system so I'd rather not rewrite it to suit my own coding style unless I have to.

    In case my references to transactions were confused with BEGIN TRAN/COMMIT TRAN in my previous posts I should point out the transactions in the script refer to financial transactions (the result of a product purchase from a web page) rather than sql transactions. If any of the transactions fail to get loaded onto the finance server then the entire batch is deleted and will be re-posted the following day when the next transfer process runs.

  8. #8
    Join Date
    Sep 2003
    Posts
    364
    Good luck.

  9. #9
    Join Date
    Jul 2004
    Posts
    12
    Out of curiosity, does anyone know what the difference is between using openquery and the four-part naming convention (linked_server.catalog.schema.table) used above?

    From researching online I understand you can use both but openquery performs much better than four-part naming. Is it simply the poor performance of not using openquery that's causing the query to time out or is there some other factor I'm unaware of?

  10. #10
    Join Date
    Sep 2003
    Posts
    364
    I don't know all of the details so someone correct me if I'm wrong, but essentially the openquery() lets the linked server process and execute the query and simply sends the results back to the requesting server (like a true passthrough query). If openquery() isn't used the requesting server tries to interpret the indexes, tables, data, etc... from the linked server to determine what it thinks the optimal execution plan is to process all or parts of the query on the requesting server vs. the linked server. My experience has shown that using the latter method SQL Server doesn't choose the optimal execution plan nor does it consistently choose the same plan when the linked server is Sybase ASE 12.5. I've had one query that performed subsecond consisently for a week and suddenly took 20 minutes to execute 80% of the time the following week. Using openquery() it's subsecond everytime. Hope this helps.

  11. #11
    Join Date
    Jul 2004
    Posts
    12
    That's cleared up the puzzling question of why the script used to fail 30% or less of the time but now fails almost every night.

    Thanks again for all the help. I'll report back in a week once we've moved entirely to SQL Server to confirm or deny whether that solved the problem.

  12. #12
    Join Date
    Jul 2004
    Posts
    12

    Worked a treat

    Moving the finance system from Sybase to SQL Server worked a treat. This problem is therefore associated with the way I was using the linked server before.

    It's also worth noting the incredible increase in performance I've noticed. The stored proc used to take several minutes to run and is now done in less than a second!

Posting Permissions

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