Solved

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

Posted on 2007-03-28
6
1,005 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

622 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