Link Server to AS/400 Issue 2

[silver2kgt]

I have two SQL servers, A and B that have a link server connection to an AS/400.

Server A has SQL 7 and Server B has SQL 2000 and both have an ODBC connection to the same AS/400. The link server for each of these use that ODBC connection to access the data on the AS/400 (not sure why whoever set this up didn't just use a direct connection, but oh well.) The drivers and settings used for these ODBC connections are the same on both servers. I have checked the SQL server settings on both servers and both are setup the same. I have connected to the AS/400 and made sure that what I am querying against does in fact have 379,865 rows.

In query analyzer on Server A I run the following query and I get the expected 379,865 rows.

select FIELD from AS400.AS400.LIBRARYNAME.TABLENAME


This same query on Server B only returns about 3,400 rows.

However, if I do the following query against either server A or B, I get the expected 379,865 rows!

select count(FIELD) from AS400.AS400.LIBRARYNAME.TABLENAME


If anyone has an idea what this might be, I would be most appreciative. Thanks in advance.


Trevor

 

[mercwrought]

are you using QA if you are check the tools->options-Connection property tab see if set rowcount is set to anything other than 0

 

[Skittle]

It my be a compatibility issue.
I read somewhere the other day that different versions of SQL server handle commands differently and can produce different results.

The 'Sp_dbcmptlevel' can set the compatibility for you to see if that makes a difference. You could try setting SQL 2000 to version 7 and re-running the Select to se if you get the same result as your SQL server 7 instance.

Just don't forget to put it back to SQL 2000 level afterwards.

Dazed and confused
(N+, MCP, MCAD)

 

[silver2kgt]

I found the issue.

It must be a compatibility issue with the Client Access ODBC driver and MSSQL.

The server with SQL 7.0 has the ODBC drivers from the V5R1M0 CD (ODBC driver version 08.00.00.00) and it works fine. The server with SQL 2000 would not work with these drivers (but would work fine with the drivers from V4R2 which is 07.00.05.00). Going back to old drivers wasn't an option as that would have broken something else...

I did some searching and found a cumulative patch/service pack for V5R1, applied that to the server (driver version after the upgrade is now 08.00.07.00) and it works fine!

Thanks to mercwrought and skittles for the help!