Post reply

ADSI, OpenRowset and Page Size

  • December 9, 2009 at 12:55 pm

    #136754

    Another property I cannot seem to set correctly, someone I'm sure has the answer.

    Trying to query AD using OPENROWSET. Works great, but only returns 1000 rows. My problem is where to set the Page Size=XXXXX property.

    This syntax DOES WORK, but returns only 1000 rows:

    OPENROWSET('ADsDSOObject','adsdatasource';'DOMAIN\USERNAME';'PASSWORD', 'Select mail, name from 'LDAP://XYZ'', etc)

    But I need to return more than the default 1000 rows. I've read in various places that you must set the 'Page Size' property in the OPENROWSET command to returns more than the default number of rows, but I cannot get it working, because I don't know how to implement it. I've tried this variation I saw in several places on the web, but it doesn't work:

    OPENROWSET('ADsDSOObject','User ID=DOMAIN\USERNAME;Password=PASSWORD;ADSI Flag=0x11;Page Size=10000', 'Select mail, name from 'LDAP://XYZ'', etc)

    And this doesn't work either, where I'm simply trying to replace the 'adsdatasource' section above with the user=;password= combination.

    OPENROWSET('ADsDSOObject','User ID=DOMAIN\USERNAME;Password=PASSWORD;','Select mail, name from 'LDAP://XYZ'', etc)

    Does anyone know how to implement 'Page size' in an OPENROWSET query against AD?

    Thanks

    Crab

  • December 9, 2009 at 1:15 pm

    #1090184

    There was an article posted to SSC today regarding this; check out the comments section. It was suggested that this setting is configurable in AD and an alternative was load data into a temp table using alpha-numeric ranges....

    http://www.databasejournal.com/features/mssql/article.php/3849891/Query-Active-Directory-Data-from-SQL-Server-using-T-SQL.htm

    This appears to be another good alternative...

    http://blogs.msdn.com/ikovalenko/archive/2007/03/22/how-to-avoid-1000-rows-limitation-when-querying-active-directory-ad-from-sql-2005-with-using-custom-code.aspx

    Tommy

    Follow @sqlscribe

  • December 9, 2009 at 1:31 pm

    #1090195

    Thanks. I can do all this in .Net managed code, if fact I have, but I was trying do it all in SQL. our AD is barebones, our SQL USERS table is bereft of contact info, and we have a person who updates a spreadsheet to keep all the users/titles/roles/contact info straight. Don't ask. Ridiculous, I know, but every shop Ive ever worked at had big holes in this area.

    I'm trying to get AD to be the mother of all contact info. at least for the softphones.

    Maybe I'll try the UNION technique. Not right, but it would work.

    thanks

  • December 9, 2009 at 2:13 pm

    #1090222

    NP. It is my understanding that the OLEDB provider for Microsoft Directory Services doesn't expose the page size property....I'm pretty sure I've seen this logged as a feature request on MS connect before but it might not hurt to log it again 🙂

    Tommy

    Follow @sqlscribe

  • December 9, 2009 at 2:53 pm

    #1090253

    thanks, that'll keep me from searching for the answer any further.

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

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