Why a select query only returns 1000 rows?

Question

Why a select query only returns 1000 rows?

Coriolan

SSCommitted

Points: 1931
More actions
August 9, 2005 at 3:11 pm

#164552

I ran the following select from SQL Query Analyzer.
select count(*) as totalCount FROM OPENROWSET('ADSDSOObject',
'adsdatasource;', 'SELECT displayName from ''LDAP://uspld104.amer.corp.xxxxxx.com'' where objectClass = ''User'' ')

It only returns 1000 rows. Any ideas what or where the limit is set?
I know the result set has a large number of rows. Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

sushila SSC-Dedicated Points: 35293 More actions · Answer 1

???shouldn't count(*) return only one row??? - I'm confused...

however, if it was a "select * from..", I believe "Set Rowcount 0" before the query should be a possible fix (came across this on some website - sorry, lost the link...)

**ASCII stupid question, get a stupid ANSI !!!**

Coriolan SSCommitted Points: 1931 More actions · Answer 2

Yes, you are correct. Count(*) returns only 1 row but it is the value of count that is incorrect. That count value should not be 1000 but approximately 155,000+. thanks.

Coriolan SSCommitted Points: 1931 More actions · Answer 3

I did SET ROWCOUNT 0 but the count value is still 1000.

David A. Long SSCrazy Eights Points: 8421 More actions · Answer 4

I think that you will find that the reason is in the ADSDSOObject, re:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adsi/adsi/ldap_dialect.asp

http://support.microsoft.com/default.aspx?scid=kb;en-us;269361

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adsi/adsi/example_code_for_using_ranging_to_retrieve_members_of_a_group.asp

Andy