SQL Server 2000 and Linked server queries

[bearit]

Hi All,
I've recently started working with linked server functionality within SQL Server 2000. After setting up a linked server to an AS400 db without any issues the problem comes when I try to query the db. Using the Openquery method I try to pull ALL the records from one table (no filters or specific criteria).
The results show only 64 records of the 17000+ records that I know exist.
Anyone have any ideas why this would be happening and how it can be fixed?

Thanks.....

 

[sguslan]

Hmmm ,,,

May be DB2/400 has some sort of DB Governor which is a watch dog for IBM databases that manages DB engine resources. It may be allowing only a number of records to go out through (what ever subsystem your oledb/odbc driver connect to, may be DRDA/DDF).
I know on mainframe OS390/DB2 systems this is common practice!, but normally it would be limited to a lot more than 64 rows..

 

[bearit]

Thanks for your response. I did think about the possibility of row limitation but when I use the same driver to set up link tables from an Access db to the same files on the AS400 it works fine and pulls all records. Thats what makes me think it's either the way I set up the Linked server or the SQL server setup itself.
I'll keep digging ...

 

[dhay1999]

did you figure this out? I am having the same type issue, except I get the first 642 records..

Thanks!
David

 

[bearit]

I still haven't found a fix for this. I'm working around it by pulling the data through ODBC and copying to a local database. This is not a preferred method b/c the data is updated only twice a day rather than being dynamic.

 

[tman24m]

I'm trying to find some documentation that I used to have lying around about the DSN set up for Client Access. I had that problem before, I'm pretty sure the fix lies in the ODBC setup.

 

[dhay1999]

thanks, I should have remembered to follow up when I fixed it. Under performance tab advanced button, Uncheck the Use blocking with a fetch of 1 row option, also be sure to go into translation and check translate ebcdic

David