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:
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:
EXEC master.dbo.sp_MSset_oledb_prop N'IBMDA400', N'AllowInProcess', 1
EXEC master.dbo.sp_MSset_oledb_prop N'IBMDA400', N'DynamicParameters', 1
and – voilà – everthing will start to work.