Solved

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

Posted on 2007-03-28
6
995 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

813 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

10 Experts available now in Live!

Get 1:1 Help Now