Solved

Incomplete result sets connecting through MSSQL linked server to AS400 DB2 using ODBC driver

Posted on 2007-03-28
6
992 Views
Last Modified: 2008-09-22
I have a linked server going from MSSQL 2000 sp3a to DB2 on an AS400 server using the client access 32bit odbc driver and am getting back odd results from the database.

Depending on what column I choose, I get back different record counts.  It has been very consistent in behavior but no pattern has emerged, other than the byte count seems to be divisible by 1024 bytes.  We have tried the IBM DB2 UDB for iseries OLE DB driver and had similar results but different row counts.

koeng--2 bytes
nrklx--6 bytes
koenl--3 bytes
bever--15 bytes
This table has 26446 records total.  None of the queries bring back anything close to that.

select * from openquery(as400_dsn,'select koeng from AS4GF900.KLLEVPF')--16384
select * from openquery(as400_dsn,'select nrklx from AS4GF900.KLLEVPF')--8192
select * from openquery(as400_dsn,'select bever from AS4GF900.KLLEVPF')--4096
select * from openquery(as400_dsn,'select koenl from AS4GF900.KLLEVPF')--10922
select nrklx from openquery(as400_dsn,'select * from AS4GF900.KLLEVPF')--4096
select * from openquery(as400_dsn,'select * from AS4GF900.KLLEVPF')--284

How do I get the entire result set from this connection mechanism?

0
Comment
Question by:Summit-IT
  • 2
  • 2
6 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 18808817
what happens if you just try to select the records and not count(*) ?
0
 

Accepted Solution

by:
Summit-IT earned 0 total points
ID: 18809264
We have isolated the problem to the version of the Client Access OLE DB driver and the version of the IBM ISeries.  The version of the ISeries was v5r3 and the client access driver was v5r2.  Once we changed the client access driver to version v5r3 the correct information was correct.  So to clarify it was a mismatch in versions.
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 18809850
so what is the question ?
0
 

Author Comment

by:Summit-IT
ID: 18816297
There are no further questions.  We resolved the issue on our own.  I just wanted to post the resolution so that anyone else that has this same issue can know the steps taken.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now