Dodgy Results AS400 linked server

Hi
I have an AS400 linked server in SQL Server 2000. Using IBM's iSeries Access
ODBC Driver (filename : CWBODBC.DLL, Ver 9.00.08.00).
When I run a query in query analyser it does not return all of the records.
I am getting "Error converting data type DBTYPE_DBTIMESTAMP to datetime."
It definitely is not returning all of rows as SELECT COUNT(*) FROM ...
reveals that there are more records. Whereas SELECT * ... returns only a
subset of the data and reports the error above.
If I omit the date field I get all of the rows. i.e. SELECT ColA as
NonDateField FROM ...
Also if I select just the datefield, I get more rows i.e. SELECT ColB as
ThisIsTheDateField FROM ... I get more rows than SELECT * FROM ... but not
all of them.
Whats going on?!!!
It almost seems as though there is some sort of buffer/bytes limit on the
data that it can return.
Do you guys who have a linked AS400 get all of your required data ?
Sorry this bit in my previous post ;
"Also if I select just the datefield, I get more rows i.e. SELECT ColB as
ThisIsTheDateField FROM ... I get more rows than SELECT * FROM ... but not
all of them. "
is incorrect please ignore (I get the same number of rows)
|||Interfacing with an AS400 can work and be stable but you
need the right driver or provider, need to keep it current,
need to keep up on any necessary service packs for it, etc.
I used Client Access and the HIT software providers for
AS400s before. HIT providers were good and we didn't run
into many problems(if any really) with their drivers or
providers. IBMs drivers weren't as stable.
What you are hitting isn't likely to be a SQL Server issue -
more likely related to the driver or the client setup,
dependent files for the driver.
-Sue
On Wed, 2 May 2007 07:18:01 -0700, Jane
<Jane@.discussions.microsoft.com> wrote:

>Sorry this bit in my previous post ;
>"Also if I select just the datefield, I get more rows i.e. SELECT ColB as
>ThisIsTheDateField FROM ... I get more rows than SELECT * FROM ... but not
>all of them. "
>is incorrect please ignore (I get the same number of rows)