question

FlipTop avatar image
FlipTop asked

Query via linked server to DB2 gives different results

I have a DB2 9.7 database I need to query. I set up a linked server from a SQL 2012 SP2 box, run QueryA, and am happy with the results. I create a linked server from the production machine, using the same script. I run QueryA and get the message, "Error converting data type DBTYPE_DBDATE to datetime." If I convert the datetime to a varchar, call it QueryB, it mostly works, but will be missing a small percentage of datetime values from that converted column. If I run QueryB on the first server, those missing values are there. I loaded MS OLE DB Provider for DB2 Version 4.0 in both places. I used the same script and connection string in both places. I used the same query, which uses OPENQUERY, in both places. I did the same test on a third server. This server is SQL 2012 SP1 CU1. It works fine. The only difference I can see is the service pack and CU level. I can't find any documentation on updates that are relevant, but doesn't mean much. ---------- I just did the same test on fourth server, with SQL2012 SP1, same as the second machine, which is the only server to have the problem, and the one where I need it to work. This fourth one did not fail. Apparently it's not the service pack or cumulative update level making the difference.
datetimeopenquerysql 2012db2
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WaterOx avatar image
WaterOx answered
Is the driver/provider used to connect to DB2 the exact same version on all machines?
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

The driver is the same. I loaded it on each at the time of testing. Microsoft OLE DB Provider for DB2 Version 4.0 -- 9.0.1390.0
0 Likes 0 ·
FlipTop avatar image
FlipTop answered
I see this has been accepted as an answer. What am I missing?
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.