iSeries to SQL Server - Linked Server Error - catastrophic failure
-
Sunday, May 27, 2012 11:04 AM
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
All Replies
-
Sunday, May 27, 2012 11:11 AM
You're not using the schema in your OPENQUERY(). Also the schema name looks like a hexadecimal number. So I would try two things:
- SELECT * FROM CALVIN..DVBDCSTC.BDRPFCOD;
- SELECT * FROM [CALVIN].[A10B5181].[DVBDCSTC].[BDRPFCOD];
-
Sunday, May 27, 2012 11:15 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:30 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:44 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
-
Thursday, June 07, 2012 3: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
- Marked As Answer by RJT124 Monday, June 11, 2012 12:13 PM
-
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
-
Wednesday, June 13, 2012 5: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
Same here, used it with Client Access 7.1 and I believe 5.2, wouldn't work till I used Tony suggestion.
.png)
