THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Davide Mauri

A place for my thoughts and experiences on SQL Server, Business Intelligence and .NET

Connecting to DB2 from SQL Server 2005 x64 and after using IBM Client Access

If you have to connect your SQL Server to a DB2 database server, you can quickly stumble upon this “nice” error:

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

After spending some time trying to figure out what was wrong, I found this very complete and useful article:

http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/8514b4bb-945a-423b-98fe-a4ec4d7366ea

The article is quite long and deals with all the problems you may find when trying to make SQL Server and DB2 work together, using SSIS or Linked Server.

If you’re using Linked Server and you have the aforementioned error, all you have to do is to set the “magic” properties:

USE [master]
GO 
EXEC master.dbo.sp_MSset_oledb_prop N'IBMDA400', N'AllowInProcess', 1
GO  
EXEC master.dbo.sp_MSset_oledb_prop N'IBMDA400', N'DynamicParameters', 1
GO

and – voilà – everthing will start to work.

Published Wednesday, July 20, 2011 5:13 PM by Davide Mauri
Filed under: , ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Michael Francis said:

Hi Davide

We (Red Gate Software) have a free tool – a data cleanser for SSIS – that we are looking for opinions on.

Information on the free tool, including the download, can be found at http://www.red-gate.com/products/sql-development/data-cleanser/

May I ask if you would be interested taking a look and letting us know your opinions?

Many thanks

Michael Francis

July 22, 2011 6:38 AM
 

Scott said:

Thanks Davide. Ran that code and it fixed my Sql2008 to IBM linked server issue.

May 13, 2013 10:51 AM
 

Devshish Shinde said:

Thanks so much Davide for providing accurate details.

I was facing same issue and able to fetch the query result from DB2 linked server after running your queries.

February 24, 2014 3:27 AM

Leave a Comment

(required) 
(required) 
Submit

About Davide Mauri

Davide Mauri - MCP, MCAD, MCDBA, MCT, MVP on SQL Server - has worked with SQL Server since version 6.5, and his interests cover the whole platform, from the Relational Engine to Analysis Services, from architecture definition to performance tuning. He also has a strong knowledge of XML, .NET and the Object Oriented Design principles, which allows him to have the correct vision and experience to handle development of complex business intelligence solutions. Having worked as a Microsoft Certified Teacher for many years, Davide is able to pass all his knowledge to his co-workers, allowing his team to deliver high-quality solutions. He currently works as a Mentor for SolidQ and can be found speaking in many Italian and internationals events.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement