Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2007-03-28
6
Medium Priority
?
1,011 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

721 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