Total Query charecters limited on linked server? 2

[perfectchaoss]

Hello,
I have a query that i thought should be pretty straitforward:
SELECT *
FROM SEASIA.RUMBA400.FMSV3DB05.ARCUST CustomerList
33 records
(it is querying from a linked AS400 server)
The wierd think is there are 1500+ rows in the table on the AS400 and the query only returns 35 of them. The even wierder thing is... The more columns I take off the more records i get
SELECT CMNUMN, CMNAM1, CMADDR, CMPROV, CMPOST, CMAREA

'returns about 400 records

SELECT CMNUMN, CMNAME1

'returns ABOUT 1200 records

SELECT CMNUMN

returns all 1526 records
Another odd thing about the situation is that if i specify a record that was not returned in the one of the queries it will be displayed:
SELECT *
FROM SEASIA.RUMBA400.FMSV3DB05.ARCUST CustomerList
WHERE CMNUMN = 1896

returns a row that is not included in the query

SELECT *
FROM SEASIA.RUMBA400.FMSV3DB05.ARCUST CustomerList

Is there something in SQL that limits the total size or charecters in a query from a linked server? I spoke with our AS400 administrator and he told me that was the problem. If so would I change it?

Thanks,
JF

 

[aalmeida]

What kind of connection are you using(ODBC, Jet, OLEDB)? most likly that's where your limitation resides.

Is the AS400 serving a RDB or Text Db?

does you query have a RowCount % Option or a top % ?
Just some thoughts

AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"

 

[perfectchaoss]

AL Almeida,
Thank you for your quick responce I will look into limitations for the Driver. I am using a OLEDB connection using the Iseries access for windows driver(Client Access ODBC Driver 32-bit). The AS400 is servering a Text DB, I am Querying a "physical file" is what our AS400 admin calls it. No rowcount or top % although originally it was
SELECT TOP 100% ....
I deleted this thinking it might be the problem.

Thanks,

JF

 

[perfectchaoss]

AL Almeida,
You were right!!!! In ODBC Administration, I configured the driver I was using and for some reason the Large Objects Threshold was set to 64KB. Thank you for pointing out the problem to me.

JF

 

[aalmeida]

I'm glad I could help

AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"