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.

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. "

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
[quoted text, click to view]