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

• sushila

  SSC-Dedicated

  Points: 35293

  More actions

  August 9, 2005 at 9:07 pm

  #580893

  ???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

  August 9, 2005 at 10:08 pm

  #580900

  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

  August 9, 2005 at 10:11 pm

  #580901

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

• David A. Long

  SSCrazy Eights

  Points: 8421

  More actions

  August 9, 2005 at 10:47 pm

  #580904

  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