none
iSeries Access ODBC Driver + MS SS OLE DB for ODBC + OPENQUERY = Catastrophic Failure...

    Question

  • Hi All,

    I've been having this problem, and researching the heck out of it - with no luck at all.

    I'm using a version of the following statement: SELECT * INTO ##tmp FROM OPENQUERY(LS_ACSC,'SELECT * FROM lib.tbl').

    Now, most of the time it works.  The LS_ACSC linked server is a SQL Server OLE DB for ODBC driver, the ODBC driver is IBM's iSeries Access ODBC Driver.
    When this error happens, it pretty much renders the LS_ACSC linked server useless, and any other proc, job, etc that uses it - fails with the same error.

    Only solution is to restart SQL Server.  But this is obviously not a solution.  I need to get to the heart of the matter.

    I am running different jobs where all the procs are using these drivers at the same time.  Not sure if it's a locking issue or such.

    Here's the error
    The OLE DB provider "MSDASQL" for linked server "LS_ACSC" reported an error. The provider reported an unexpected catastrophic failure. [SQLSTATE 42000] (Error 7399)  Cannot fetch a row from OLE DB provider "MSDASQL" for linked server "LS_ACSC". [SQLSTATE 42000] (Error 7330).  The step failed.

    I don't currently have the code in a TRY...CATCH block, and I'm not even sure if that would help.

    Any help would be appreciated.

    Thanks!
    Denvas


    Tuesday, July 07, 2009 7:24 PM

Answers

  • I accidentally posted the answer above me as an answer.  Sorry, did not mean to do that.

    I had a similar problem with SQL 2005 and Informix.  We were using an extremely old and out of date ODBC driver.  When I updated it to the latest version (and of course applied a update in Informix for the ODBC functionality), our problems went away.

    We were using Informix ODBC 2.8, which was old, so we updated to 3.5, which was the newest at the time.

    I know in the case of IBM iSeries Drivers, most people don't even update their AS/400 to the latest version.  There are most likely compatibility issues from SQL to iSeries (DB2).  So what I recommend there is to update your AS/400 to the latest version as long as there is no serious impact, then you can use the latest driver.  Funny thing with the AS/400 is that all versions (client, ODBC, server, etc) have to match the same version.

    So... in summary:
    Update the AS/400
    Update the ODBC driver
    Update the iSeries Client

    You should be okay then.  Otherwise you will more than likely experience compatibility issues.
    • Marked as answer by Denvas Saturday, February 20, 2010 4:34 AM
    Friday, January 22, 2010 2:04 PM
  • Friday, January 22, 2010 7:24 AM
    Answerer

All replies

  • Denvas - did you ever find a solution?
    I think I have the exact same problem, although I never get any error messages at all.  The query just runs FOREVER until I restart the server. Same behavior: the query will work once or twice and then just stop working.

    eric.hrnicek@woolpert.com if you have any suggestions.
    Wednesday, August 26, 2009 4:43 PM
  • I seem to be running into a similar problem and would be very appreciative of any updates you might have.

    Thursday, January 21, 2010 10:38 PM
  • Well nothing really hopeful...  My company actually PAID Microsoft to support this error, and they couldn't even figure it out after a week of trying (useless).

    The theory might be that the ODBC driver was written for SQL Server 2000.  So it may only work there and not SS 2005 - and ESPECIALLY NOT 2008.  It was horrific on SS 2008.

    Really hope this helps, b/c I truly know how annoying this error is.
    Friday, January 22, 2010 7:19 AM
  • Friday, January 22, 2010 7:24 AM
    Answerer
  • I accidentally posted the answer above me as an answer.  Sorry, did not mean to do that.

    I had a similar problem with SQL 2005 and Informix.  We were using an extremely old and out of date ODBC driver.  When I updated it to the latest version (and of course applied a update in Informix for the ODBC functionality), our problems went away.

    We were using Informix ODBC 2.8, which was old, so we updated to 3.5, which was the newest at the time.

    I know in the case of IBM iSeries Drivers, most people don't even update their AS/400 to the latest version.  There are most likely compatibility issues from SQL to iSeries (DB2).  So what I recommend there is to update your AS/400 to the latest version as long as there is no serious impact, then you can use the latest driver.  Funny thing with the AS/400 is that all versions (client, ODBC, server, etc) have to match the same version.

    So... in summary:
    Update the AS/400
    Update the ODBC driver
    Update the iSeries Client

    You should be okay then.  Otherwise you will more than likely experience compatibility issues.
    • Marked as answer by Denvas Saturday, February 20, 2010 4:34 AM
    Friday, January 22, 2010 2:04 PM
  • Thanks everyone for all your posts ...

    N/P Veranor...  I liked your previous post, and wish I would have had it a year ago LOL  I marked it as the final answer.

    Nevertheless - you got it!  It was like a freakin' Rubix Cube for me trying to figure that one out (and apparently Microsoft's).  ;)

    Take Care.

    - Denvas

    eFormula Strategies LLC
    Saturday, February 20, 2010 6:57 PM