Home Dashboard Directory Help
Search

SQL Server 2008 R2 Linked Server to DB2 using Microsoft OLEDB provider for odbc. Fail by BobChauvin


Status: 

Closed
 as Not Reproducible Help for as Not Reproducible


2
0
Sign in
to vote
Type: Bug
ID: 615000
Opened: 10/19/2010 10:35:44 AM
Access Restriction: Public
4
Workaround(s)
view
4
User(s) can reproduce this bug

Description

There appears to be an incompatibility with the MS OleDB for ODBC driver method of creating linked servers and SQL 2008 R2. This method works with a SQL 2005 server.

A phone call to IBM support (see info below) and numerous tests and IBM review of odbc/ibm logs revealed odd handling of the odbc requests from SQL 2008 R2. This method still works on SQL 2005, which somewhat confirms an issue with SQL 2008 R2. Per IBM support another client recently reported SQL 2008 worked, R2 broke the functionality.
Details
Sign in to post a comment.
Posted by smardi on 5/2/2013 at 1:12 AM
/*********I, also, was struggling with this exact problem. We opened a support call with IBM who gave us a simple tip which appears to have resolved the problem - so I will share it with you.

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.

With this one change I was able to select, delete and insert records to and from our AS400 when previously only the OPENQUERY syntax would work. I hope someone else out there finds this useful!

Jim**************/


Thanks Jim,

That worked, I was struggling with this for quite a long time.

Regards,
Sudhir
Posted by Samuel Justin Gabay on 6/27/2012 at 12:22 PM
I was having the same problem: I could not create a linked server between SQL Server 2008R2 64-bit and a DB2 for i (iSeries) 7.1 database using the MSDASQL bridge to the iSeries Access ODBC driver. I would recieve the catastrophic error when using a syntax such as:

select *
from SYSTEM.RDB.SYSIBM.SYSDUMMY1

but it will work when using a syntax such as:

select *
from openquery(SYSTEM, '
    select *
    from SYSIBM.SYSDUMMY1')

Updating the SQL Server to Service Pack 2 (which as of 2012-06-27 was not officially released), but disabling the pre-fetch as Jim suggests did work.

You can disable the prefetch by clearing the "Enable pre-fetch of data for queries" check box in the Performance tab when creating a DSN or adding "PREFETCH=0" to the connection string when creating a DSN-less connection.
Posted by Jim Thurston on 5/31/2012 at 9:10 AM
I, also, was struggling with this exact problem. We opened a support call with IBM who gave us a simple tip which appears to have resolved the problem - so I will share it with you.

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.

With this one change I was able to select, delete and insert records to and from our AS400 when previously only the OPENQUERY syntax would work. I hope someone else out there finds this useful!

Jim
Posted by mbrossard on 2/14/2012 at 7:52 AM
I had precisely the same problem.

I managed to make it work by :
. Updating SQL Server 2008 R2 with SP2 (http://www.microsoft.com/downloads/fr-fr/details.aspx?familyid=8fbfc1de-d25e-4790-88b5-7dda1f1d4e17&displaylang=fr)
. Updating IBM iAccess for Windows to the last version (V7R1 - SI44594)

Hope that helps.
Matthieu
Posted by Labry on 5/18/2011 at 2:11 PM
I've been dealing with this issue for days now. I can reproduce the issue at will. When I create a linked server to the as4000 from r2/64 bit standard I can query using openquery but when I use the 4 part distributed query linked server name, SQL Server dumps. If this were an Enterprise edition server I could use the Microsoft DB2OLEDB driver but that won't install on Standard edition.

I can't roll out R2(64bit) in my shop any more because of this issue. I had to roll back to a 32 bit version to get around it. If someone from MS wants my code and a dump, I'd be happy to provide all my data on this issue as well as easy to use steps to recreate the problem.





Posted by shivanand_k on 4/20/2011 at 9:00 AM
Is there any fix available from Microsoft. I also has the same issue. We are upgrading from SQL 2005 (64 bit) to SQL 2008 R2 (64 bit). We are also moving from Windows 2003 to Windows 2008 R2. The linked server with AS400 with ODBC works perfect with SQL Server 2005 on Windows 2003.
But with SQL 2008 R2 on Windows 2008 R2 gives error when you try to execute query it gives an error as shown below.

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "AS400" 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 "AS400".
Posted by Microsoft on 3/2/2011 at 2:09 PM
Hi folks (seems like there are multiple individuals?)

I'm not entirely sure what specific detail is different between our registration scripts. In this case, I would advise contacting Customer Support so that they can help you out more interactively and determine the cause of your issues. As I mentioned below, using what looks like a similar registration script, I was able to create and access a linked server without issue for MSDASQL.

Sorry for the inconvenience, but in this case I think your best bet would be to get live help from Customer Support.

As I haven't heard from the original poster for some time, I am closing this issue.
Patrick
Posted by ktino on 1/23/2011 at 11:03 PM
hERE IS THE REGISTRATION SCRIPT:



/****** Object: LinkedServer [EQUATION]    Script Date: 01/24/2011 09:03:12 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'EQUATION', @srvproduct=N'EQUATION', @provider=N'MSDASQL', @datasrc=N'EQUATION', @provstr=N'Provider=MSDASQL.1;Persist Security Info=True;Data Source=EQUATION;Initial Catalog=CYA_CSI;'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'EQUATION',@useself=N'False',@locallogin=NULL,@rmtuser=N'sap',@rmtpassword='########'

GO

EXEC master.dbo.sp_serveroption @server=N'EQUATION', @optname=N'collation compatible', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'EQUATION', @optname=N'data access', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'EQUATION', @optname=N'dist', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'EQUATION', @optname=N'pub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'EQUATION', @optname=N'rpc', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'EQUATION', @optname=N'rpc out', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'EQUATION', @optname=N'sub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'EQUATION', @optname=N'connect timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'EQUATION', @optname=N'collation name', @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N'EQUATION', @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'EQUATION', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'EQUATION', @optname=N'use remote collation', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'EQUATION', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
Posted by ktino on 1/23/2011 at 10:42 PM
Hi,
I have succesfully registered an as400 db2 linked server from my Windows 2008 r2 64-bit machine, using sql server 2008 r2. The problem is that, when I try to run a query that runs without problems in windows 2003 servers using sql2005, i get the following error:
Msg 7399, Level 16, State 1, Procedure Portal_Transaction_History, Line 23
The OLE DB provider "STREAM" for linked server "(null)" reported an error. The provider reported an unexpected catastrophic failure.
Msg 7330, Level 16, State 2, Procedure Portal_Transaction_History, Line 23
Cannot fetch a row from OLE DB provider "STREAM" for linked server "(null)".
Posted by Microsoft on 1/18/2011 at 3:59 PM
Hi Bob,

Yes that's correct, I was able to establish a connection and query a remote AS400 DB2 source using the ODBC driver provided by HIS2009 (and therefore the particular provider MS OLEDB for ODBC drivers).

It could be that the driver I am using differs from the one you're using -- if it is not and if it is supplied by IBM, then I cannot try to repro the issue since I do not have access to it. In this case I'd highly recommend communicating with Customer Services and Support as they have more tools at their disposal for debugging these cases.

In my previous message I mentioned a few possible reasons that could be the case -- please try them if you have not already. If you have additional questions, feel free to ask and I can try to answer them.

Patrick
Posted by BobChauvin on 1/17/2011 at 5:55 AM
Patrick, Are you able to get the ODBC driver to work in the SQL2008R2 environment?
Posted by Microsoft on 1/13/2011 at 1:34 PM
An update on your request:

I have been able to produce a successful connection over MSDASQL at least with the ODBC driver that is provided via HIS 2009. From what I can tell of your item, this appears to be the same provider you've created.

A few things I can recommend in terms of helping resolve your issue:
- Verify that you've created a DSN for the connection you're making, and that the connection via the DSN successfully connects. If it does not, reevaluate the parameters provided.
- Make sure you've added the DSN using the appropriate ODBC Data Source Administrator and that the architecture of the provider matches your environment (assuming any of it has changed). (32 bit vs 64 bit)
- Make sure the datasrc parameter you provide matches the name of the DSN you've created
- Make sure the credentials are still valid

These are the only items I can see from the DDL you've provided that may need to be updated.

Let me know if any of this helps. If you have any questions that I can address, I will try my best.

If I cannot resolve the issue after these questions, one recommendation I have is to try the Microsoft OLEDB provider for DB2, which works with AS400 DB2.

Beyond that, the only other advice I can give is to contact CSS with your request, who can help you better with your specific environment settings and see if there is something more specific in your development environment that may be causing the issue. I've tried to duplicate the environment the best I can here, so this may be a necessary step in resolution if the above advice does not help.

Let me know if any of the information I've provided helps
Patrick
Posted by Microsoft on 1/11/2011 at 10:49 AM
Hi Bob,

We are currently looking at this item. However, I would like to know if there is a strict requirement to using the ODBC driver you've specified, as we generally suggest that customers use the Microsoft OLE DB Provider for DB2 that should be provided to you already. This provider should work correctly with the database you specified, and it generally performs better than MSDASQL. Furthermore, we are much more able to support this provider.

Please let me know if you require using the ODBC driver for any specific reason. Otherwise, I would highly encourage trying the provider I mentioned above.

Thanks,
Patrick Foubert
Software Engineer
Posted by BobChauvin on 11/24/2010 at 8:26 AM
Regarding the IBM DB2 ODBC Driver... I believe this is analgous to my suggested IBMDASQL driver work around, which offers some of the functionality, but is not a 1:1 replacement.

We would still prefer MSFT fix the regression.
Posted by Microsoft on 11/24/2010 at 8:16 AM
Hi,

Thank you for your feedback. We will investigate the issue and update you when we have more information. If the information provided by Alberto has helped, please let us know.

Thanks,
Pooja Harjani
Program Manager

Posted by Alberto Morillo on 11/20/2010 at 11:59 AM
Hello,

Try using "IBM DB2 ODBC driver". Please see the following article:

http://www.sqlcoffee.com/Tips0013.htm

Regards,

Alberto Morillo
SQLCoffee.com
Posted by BobChauvin on 10/19/2010 at 10:37 AM
Here is the linked server ddl:


/****** Object: LinkedServer [DB2ODBC_LMS]    Script Date: 10/12/2010 14:22:29 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'DB2ODBC_LMS', @srvproduct=N'iSeries Access for Windows ODBC data source', @provider=N'MSDASQL', @datasrc=N'LMSAndDW'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'DB2ODBC_LMS',@useself=N'False',@locallogin=NULL,@rmtuser=N'developer',@rmtpassword='########'

GO

EXEC master.dbo.sp_serveroption @server=N'DB2ODBC_LMS', @optname=N'collation compatible', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'DB2ODBC_LMS', @optname=N'data access', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'DB2ODBC_LMS', @optname=N'dist', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'DB2ODBC_LMS', @optname=N'pub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'DB2ODBC_LMS', @optname=N'rpc', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'DB2ODBC_LMS', @optname=N'rpc out', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'DB2ODBC_LMS', @optname=N'sub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'DB2ODBC_LMS', @optname=N'connect timeout', @optvalue=N'99999'
GO

EXEC master.dbo.sp_serveroption @server=N'DB2ODBC_LMS', @optname=N'collation name', @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N'DB2ODBC_LMS', @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'DB2ODBC_LMS', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'DB2ODBC_LMS', @optname=N'use remote collation', @optvalue=N'true'
GO

Identical test on a SQL 2005 server works as expected.
Sign in to post a workaround.
Posted by Vulcan900 on 6/28/2012 at 5:23 AM
Disabling the row pre-fetch eliminates this error (SQL 2008 R2 64bit , V6.2 iseries). Problem does not occur on 2008R2 32bit.
Posted by Samuel Justin Gabay on 6/27/2012 at 12:24 PM
Disabling the row pre-fetch eliminates this error. You can disable the prefetch by clearing the "Enable pre-fetch of data for queries" check box in the Performance tab when creating a DSN or adding "PREFETCH=0" to the connection string when creating a DSN-less connection.
Posted by Alberto Morillo on 11/20/2010 at 12:00 PM
Hello,

Try using "IBM DB2 ODBC driver". Please see the following article:

http://www.sqlcoffee.com/Tips0013.htm

Regards,

Alberto Morillo
SQLCoffee.com
Posted by BobChauvin on 10/19/2010 at 10:36 AM
Possibly use the IBMDASQL driver, but would prefer odbc which has more robust interface.