Return records through a linked server that uses ODBC data source

[Lance]

Hi,

I used sp_addlinkedserver to link to a remote server through ODBC.
When I execute select count(*) from LinkSrv.SI.DBO.SIHeader in SQL Query
Analyzer. It returns 13705 records. But when I execute select * from
LinkSrv.SI.DBO.SIHeader. It only return 885 records. If I specify some
columns, select ODCOMP, ODPONO, ODVDCD from LinkSrv.SI.DBO.SIHeader.
It returns more records, 1213 records.

I guess there is something limit the return storage, but I can not
find it.

Any suggestion will be appreciated. Thank you

[Erland Sommarskog]

Lance (wi******@21cn.com) writes:

I used sp_addlinkedserver to link to a remote server through ODBC.
When I execute select count(*) from LinkSrv.SI.DBO.SIHeader in SQL Query
Analyzer. It returns 13705 records. But when I execute select * from
LinkSrv.SI.DBO.SIHeader. It only return 885 records. If I specify some
columns, select ODCOMP, ODPONO, ODVDCD from LinkSrv.SI.DBO.SIHeader.
It returns more records, 1213 records.

I guess there is something limit the return storage, but I can not
find it.

Judging from the names, the remote server is SQL Server as well. Any
particular reason that you are using the MSDASQL provider, and not
SQLOLEDB?

There is no reason that you should not get all rows back, so something
is indeed fishy here. But not knowing anything about the systems it is
difficult to tell. What does SELECT @@version say on the two boxes?

If you query the tables on the local server, do you get the correct result
then?
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Lance]

I am using MSDASQL to link to a AS400/DB2. Now, I think I have found
the answer. I tried to change some configure in ODBC. The records of
result set have been changed. So this problem is caused by ODBC DB2
drive provided by IBM. Anyway, thank you.

[Erland Sommarskog]

Lance (wi******@21cn.com) writes:

I am using MSDASQL to link to a AS400/DB2. Now, I think I have found
the answer. I tried to change some configure in ODBC. The records of
result set have been changed. So this problem is caused by ODBC DB2
drive provided by IBM. Anyway, thank you.

Thanks for reporting back. Permit me to point out that it's always a
good idea to give complete information. (Of course, I should have guess
from that wretched column names that it was an AS400. Our competitors
system looks like that too. :-)

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp