Unanswered: Title: Limited Dataset being Returned in SQL Server 2000 from Remote DB
I have SS2000 SP2 running on a Win2K server. I also have DB2 running on an AS400. I have an System DSN datasource set up on the Win2K server (using Client Access Express (V5R1M0) ODBC data source) pointing to DB2/400. It is working in other applications. For example, Crystal Reports, several web ASP pages, and JD Edwards all use it successfully (return all of the rows queried).
I am trying to accesses this same DB2 data from SS2000 (Query Analyzer, stored procedures, etc.).
At first, I tried setting up a SS Linked Server. It created fine, but when I tried to use Query Analyzer to run a query, it only retrieves a small subset of the data. Next, I tried accessing the datasource via Query Analyzer without using the Linked Server via openrowset, openquery, and opendatasource. Again, only the identical small subset of the data is retrieved.
The resultset in Query Analyzer seems to be buffer-limited. For example:
- if my query returns 2 columns of a table's row...
I'll get X rows returned
- if the query only returns 1 column of the same table
(with the same selection criteria)
a multiple of X rows will be returned
- if the query returns all of the columns from the same table (with the same selection critieria)
a partial fraction of X rows will be returned
The returned dataset seems to be terminated midstream, so I know my selection crtieria is not to blame (ie. column with data A-Z, queried to be returned sorted alphabetically will stop at 'M').
I've already tried using different ODBC/OLE drivers and changing the parameters as well as setting the Options for Query Analyzer up differently (including # of rows) without any difference. I've also tried increasing the size of tempdb. No change.
I truely believe the problem is not with the connections, because all of the other apps work correctly.... only SS has a problem.