none
iSeries to SQL Server - Linked Server Error - catastrophic failure

    Question

  • We are getting an error anytime we use a four part name in a SQL query accessing an iSeries server that is defined as a linked server. Any assistance you  can provide in resolving this issue is greatly appreciated.

    System configuration: Windows Server 2008 R2 Standard (64 bit) Service Pack 1 installed, 12GB memory, 2.53GHZ and SQL Server 2008 R2 RTM - 10.50.1600 – no service packs installed

    In query analyzer the following statement will run fine: SELECT * FROM OPENQUERY (CALVIN, 'SELECT * FROM DVBDCSTC.BDRPFCOD')

    In query analyzer the following statement gives an error when first run and hangs on subsequent executions:

    SELECT * FROM CALVIN.A10B5181.DVBDCSTC.BDRPFCOD

    Here is the error on the first execution:

    Msg 7399, Level 16, State 1, Line 1

    The OLE DB provider "MSDASQL" for linked server "CALVIN" reported an error. The provider reported an unexpected catastrophic failure.

    Msg 7330, Level 16, State 2, Line 1

    Cannot fetch a row from OLE DB provider "MSDASQL" for linked server "CALVIN".

    The query window has to be closed and reopened in order to be able to run these queries again. Otherwise the execution of the queries appears to hang.

    The linked server is using IBM iSeries Access for Windows(V6R1M0) through ODBC data source defintions as defined below:

    Linked server definition

    General

    Linked Server: CALVIN

    Server Type: Other data source

    Provider: Microsoft OLE DB Provider for ODBC drivers

    Product name: iSeries

    Data source: CALVIN

     

    Security

    Be made using this security context

    Remote loging: XXXXXX(valid iSeries account

    With password: XXXXXXX(valid password for iSeries account)

     

    Server Options

    Collation Compatible: False

    Data Access: True

    RPC: False

    RPC Out: True

    Use Remote Collation: True

    Collation Name: Blank

    Connection Timeout: 0

    Query Timeout: 0

    Distributor: False

    Publisher: False

    Subscriber: False

    Lazy Schema Validation: False

    Enable Promotion of Distributed Transactions: True

     

     

    ODBC Data Source Definition(iSeries)

    General

    Data Source Name: Calvin

    Description: System i Access for Windows ODBC data source

    System: CALVIN

     

    Connection Options

    Default user ID: Use System i Navigator default

    Signon dialog prompting: Prompt for SQLConnect if needed

    Security: Use same security as System i Navigator connection


    Server

    Naming convention: SQL naming convention

    SQL default library: QGPL

    Library List: SQDBCAPP SQBDCME SQBDCOBJ *USELIBL SQNDCSTC2

    Connection type: Use ODBC access mode, Call allowed

     

    Commit Mode: Read uncommitted

     

    Data Types

    Double Byte Character Set:Report as supported: Off

    Double Byte Character Set: Report length in bytes: On

    Date and time data types: Report date as character data type: Off

    Date and time data types: Report time as character data type: Off

    Date and time data types: Report timestamp as character data type: Off

    Decimal Floating Point Data Type: Report as character data types: On

    Decimal Floating Point Data Type: Report as double data type: Off

    Rounding mode: Half Even

     

    Packages<o:p></o:p>

    Enable extended dynamic (package) support: On

    Package library: QGPL

    Cache package locally: Off

    Package usage: Use: Off

    Package usage: Use/Add: On

    Return code for unusable package: Warning

     

    Performance

    Enable lazy close support: Off

    Enable pre-fetch of data for queries: On

    Enable data compression: On

    Use blocking with a fetch of 1 row Record blocking size: 256

     

    Language<o:p></o:p>

    Sort type: Sort based on HEX values

     

    Catalog

    Library view: Default library list

    Object description type: i5/OS object description

    Enable search patterns: On

     

    Conversions

    Convert binary data(CCSID 65535) to text: Off

     

    Diagnostic

    Enable Database Monitoring: Off

    Enable the Start Debug(STRDBG) command: Off

    Print job log at disconnect: Off

    Enable job trace: Off

    






    • Edited by RJT124 Sunday, May 27, 2012 11:13 AM
    Sunday, May 27, 2012 11:04 AM

Answers

  • Was having exactly the same problem - finally saw a hint elsewhere that worked for me:

    In the ODBC Data Source Administrator panel on the server, configure your linked server from System DSN.
    Under the Performance tab, uncheck the box 'Enable pre-fetch of data for queries'.  OK, OK. Delete and re-create your linked server in SQL Management Studio.

    Hope this helps.

    Tony

    • Marked as answer by RJT124 Monday, June 11, 2012 12:13 PM
    Thursday, June 07, 2012 3:10 PM

All replies

  • You're not using the schema in your OPENQUERY(). Also the schema name looks like a hexadecimal number. So I would try two things:

    1. SELECT * FROM CALVIN..DVBDCSTC.BDRPFCOD;
    2. SELECT * FROM [CALVIN].[A10B5181].[DVBDCSTC].[BDRPFCOD];
    Sunday, May 27, 2012 11:11 AM
  • Hello,

    Four part qualifier works only with a linked MS SQL Server, not with other RDBMS, so you have to use OpenQuery command.

    Otherwise the SQL Server would have to translated T-SQL commands into the other SQL language; how could it?


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    Sunday, May 27, 2012 11:15 AM
  • Hi Olaf,

    Thank you for the quick reply. We have been using four part qualifiers for iSeries linked server since SQL 2000. They run slower then openquery which I believe is a passthru, but they do work on our other servers. I don't know the mechanics of how they work. Unfortunately, they do not work on this server. 

    This server is located in another domain. Our other working servers are in a different domain. The network engineers maintain that that is not an issue, but I do not share their certainty.

    Best Regards,

    Robert T


    Robert T

    Sunday, May 27, 2012 11:30 AM
  • Hi Stefan,

    I tried the suggestions with the following results:

    Option 1 gave Msg 7313, Level 16, State 1, Line 2 An invalid schema or catalog was specified for the provider "MSDASQL" for linked server "CALVIN". As I understand it, the schema is required when entering a four part qualifier.

    Opton 2 gave the same catastrophic error

    OpenQuery is working fine on this server. Only the four part qualifier fails. My understanding is that openquery is a passthru so the schema name is not needed. The iSeries does not know about the Microsoft schema and it is not necessary to run the query in iSeries Navigator or iSeries command line. 

    The schema name looks like a hexadecimal number: I don't know where this schema is created, but it is not part of the iSeries library or file name.

    Thanks for the suggestions.

    Best Regards,,

    Robert T


    Robert T

    Sunday, May 27, 2012 11:44 AM
  • Was having exactly the same problem - finally saw a hint elsewhere that worked for me:

    In the ODBC Data Source Administrator panel on the server, configure your linked server from System DSN.
    Under the Performance tab, uncheck the box 'Enable pre-fetch of data for queries'.  OK, OK. Delete and re-create your linked server in SQL Management Studio.

    Hope this helps.

    Tony

    • Marked as answer by RJT124 Monday, June 11, 2012 12:13 PM
    Thursday, June 07, 2012 3:10 PM
  • Hi Tony,

    Thank you so much for this fix. I have tested it on my PC and was able to duplicate the catastrophic error and fix it by unchecking the "Enable pre-fetch of data for queries" checkbox. I will move the fix to the production server in the near future. You are the first person in the past two weeks, including paid consultants, that had an answer for this issue.

    Best Regards,

    Robert


    Robert T

    Monday, June 11, 2012 12:21 PM
  • Hi Tony,

    Thank you so much for this fix. I have tested it on my PC and was able to duplicate the catastrophic error and fix it by unchecking the "Enable pre-fetch of data for queries" checkbox. I will move the fix to the production server in the near future. You are the first person in the past two weeks, including paid consultants, that had an answer for this issue.

    Best Regards,

    Robert


    Robert T

    Same here, used it with Client Access 7.1 and I believe 5.2, wouldn't work till I used Tony suggestion.

    Wednesday, June 13, 2012 5:10 PM
  • Was having exactly the same problem - finally saw a hint elsewhere that worked for me:

    In the ODBC Data Source Administrator panel on the server, configure your linked server from System DSN.
    Under the Performance tab, uncheck the box 'Enable pre-fetch of data for queries'.  OK, OK. Delete and re-create your linked server in SQL Management Studio.

    Hope this helps.

    Tony


    Excellent!  I was having the same problem -- this workaround totally fixed it.  I reconfigured my iseries DSN and unchecked the 'Enable pre-fetch of data for queries'   option in the Performance tab in the ODBC manager for this DSN.  Now I can run my queries!

    Rich P

    19 hours 1 minutes ago