Active Directory OpenQuery 1000 row limit

Hello. When doing an OPENQUERY against Active Directory,
I hit the 1000 row limit. THat's all this query below
will return. We can get this to work, by writing in
VBSCRIPT and then reading multiple sets of 1000. Would
anyone know a way to use the SQL format below to get
beyond the 1000 row limit? I know there's a RANGE
parameter in ADO, but not sure IF or HOW to code for that
in an OPENQUERY... THanks, Bruce
SELECT givenName, sn, cn, mail, samAccountName
FROM OPENQUERY( ADSI, '<LDAP://domain_name>;
(&(objectCategory=person)(objectClass=User));
givenName,sn,cn,mail,samAccountName;subtree')Bruce,
You can do something like this:
1. Order the data returning from ADSI.
2. Find the max value returned.
3. Query again, starting after the last max value.
4. Loop until done.
I don't know of a way to get the entire result set back with TSQL.
Russell Fields
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:090301c3bdaa$70026140$a501280a@.phx.gbl...
> Hello. When doing an OPENQUERY against Active Directory,
> I hit the 1000 row limit. THat's all this query below
> will return. We can get this to work, by writing in
> VBSCRIPT and then reading multiple sets of 1000. Would
> anyone know a way to use the SQL format below to get
> beyond the 1000 row limit? I know there's a RANGE
> parameter in ADO, but not sure IF or HOW to code for that
> in an OPENQUERY... THanks, Bruce
>
> SELECT givenName, sn, cn, mail, samAccountName
> FROM OPENQUERY( ADSI, '<LDAP://domain_name>;
> (&(objectCategory=person)(objectClass=User));
> givenName,sn,cn,mail,samAccountName;subtree')
>
>|||Thanks Russell. Yes, it doesn't appear that you can get
ALL rows returned in one SQL statement, and I'm fine with
the loop method via SQL. But I can't seem to get that
working either. Seems like howeer I feliter in my query,
it's filtering within the 1000 max rows... Any way you
know to get more then the 1000 rows? Is there a range
parameter in LDAP, that will do the NEXT 1000 rows,
something like that' THanks, Bruce
>--Original Message--
>Bruce,
>You can do something like this:
>1. Order the data returning from ADSI.
>2. Find the max value returned.
>3. Query again, starting after the last max value.
>4. Loop until done.
>I don't know of a way to get the entire result set back
with TSQL.
>Russell Fields
>"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
>news:090301c3bdaa$70026140$a501280a@.phx.gbl...
>> Hello. When doing an OPENQUERY against Active
Directory,
>> I hit the 1000 row limit. THat's all this query below
>> will return. We can get this to work, by writing in
>> VBSCRIPT and then reading multiple sets of 1000. Would
>> anyone know a way to use the SQL format below to get
>> beyond the 1000 row limit? I know there's a RANGE
>> parameter in ADO, but not sure IF or HOW to code for
that
>> in an OPENQUERY... THanks, Bruce
>>
>> SELECT givenName, sn, cn, mail, samAccountName
>> FROM OPENQUERY( ADSI, '<LDAP://domain_name>;
>> (&(objectCategory=person)(objectClass=User));
>> givenName,sn,cn,mail,samAccountName;subtree')
>>
>
>.
>|||Bruce,
I don't see my earlier reply, so I will try again.
Actually you need to plan your queries to bring back less that 1000 rows.
(NOT as clean as getting 1000, then the next 1000, etc.) For example,
within the query you might have:
x = 'smtp:Pager1*' -- brings back everything that matches this pattern
(in SQL terms "Like 'Pager1%'")
x = 'smtp:Pager2*' etc.
Russell Fields
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:078801c3be5b$44f05b90$a001280a@.phx.gbl...
> Thanks Russell. Yes, it doesn't appear that you can get
> ALL rows returned in one SQL statement, and I'm fine with
> the loop method via SQL. But I can't seem to get that
> working either. Seems like howeer I feliter in my query,
> it's filtering within the 1000 max rows... Any way you
> know to get more then the 1000 rows? Is there a range
> parameter in LDAP, that will do the NEXT 1000 rows,
> something like that' THanks, Bruce
>
> >--Original Message--
> >Bruce,
> >
> >You can do something like this:
> >1. Order the data returning from ADSI.
> >2. Find the max value returned.
> >3. Query again, starting after the last max value.
> >4. Loop until done.
> >
> >I don't know of a way to get the entire result set back
> with TSQL.
> >
> >Russell Fields
> >
> >"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
> >news:090301c3bdaa$70026140$a501280a@.phx.gbl...
> >> Hello. When doing an OPENQUERY against Active
> Directory,
> >> I hit the 1000 row limit. THat's all this query below
> >> will return. We can get this to work, by writing in
> >> VBSCRIPT and then reading multiple sets of 1000. Would
> >> anyone know a way to use the SQL format below to get
> >> beyond the 1000 row limit? I know there's a RANGE
> >> parameter in ADO, but not sure IF or HOW to code for
> that
> >> in an OPENQUERY... THanks, Bruce
> >>
> >>
> >> SELECT givenName, sn, cn, mail, samAccountName
> >> FROM OPENQUERY( ADSI, '<LDAP://domain_name>;
> >> (&(objectCategory=person)(objectClass=User));
> >> givenName,sn,cn,mail,samAccountName;subtree')
> >>
> >>
> >>
> >
> >
> >.
> >