AD query limit

[Brian Shelden]

I am querying AD using LDP and OpenQuery in SQL and I
cannot get more that 1000 rows to come back. Is there a
setting in AD that is limiting this?

As an alternative, is there a syntax in the LDAP filter
that would exclude anything where the distinguishedName
includes the string "Utility" or "Resource"?

Thanks in advance,

-- Brian

 

[Eric Fleischman [MSFT]]

Hi Brian,

In W2K the default max page size in 1000. In 2003 we increased this to 1500.
The reason is that this is a 'page' size, not result size. You would want to
use what are called paged searches in order to properly get a larger result
size.

It is possible to increase this 1000 value but I would *strongly* encourage
you not to do so. Increasing that value can have some serious implications
on the directory. I won't get in to the details, but trust me, you want to
try and avoid this.

So, please look in to using paged searches. This is also covered up on MSDN
if you would like more information. Sometimes it is as simple as setting a
page size (say 500 or 1000) and from there the platform on which your code
is running will take care of the rest.

Hope this helps!
~Eric

 

[Brian Shelden]

Thanks Eric.

My real goal is to have a SQL view that lists all of
the "users" in AD. The LDAP filter returns more that
1000 rows and then I further constrain the view with a
WHERE clause. I suspect I can add these constraints in
the LDAP filter but I can't figure out a syntax that
works. Is there a way to page the results using a linked
SQL Server and an OpenQuery statement?

Thanks for your help,

-- Brian

-----Original Message-----
Hi Brian,

In W2K the default max page size in 1000. In 2003 we increased this to 1500.
The reason is that this is a 'page' size, not result size. You would want to
use what are called paged searches in order to properly get a larger result
size.

It is possible to increase this 1000 value but I would *strongly* encourage
you not to do so. Increasing that value can have some serious implications
on the directory. I won't get in to the details, but trust me, you want to
try and avoid this.

So, please look in to using paged searches. This is also covered up on MSDN
if you would like more information. Sometimes it is as simple as setting a
page size (say 500 or 1000) and from there the platform on which your code
is running will take care of the rest.

Hope this helps!
~Eric

--
Eric Fleischman [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.

I am querying AD using LDP and OpenQuery in SQL and I
cannot get more that 1000 rows to come back. Is there a
setting in AD that is limiting this?

As an alternative, is there a syntax in the LDAP filter
that would exclude anything where the distinguishedName
includes the string "Utility" or "Resource"?

Thanks in advance,

-- Brian

.

 

[Eric Fleischman [MSFT]]

I would imagine that someone over on the SQL side of the house would be the
expert here. The key term to keep in mind is "paged search". That's the
goal.

I don't know exactly how you're doing things but a quick search of MSDN
turned up this sample code if it is at all helpful in terms of how you are
issuing the query:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adsi/adsi/searching_for_objects.asp

~Eric

--
Eric Fleischman [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.

Thanks Eric.

My real goal is to have a SQL view that lists all of
the "users" in AD. The LDAP filter returns more that
1000 rows and then I further constrain the view with a
WHERE clause. I suspect I can add these constraints in
the LDAP filter but I can't figure out a syntax that
works. Is there a way to page the results using a linked
SQL Server and an OpenQuery statement?

Thanks for your help,

-- Brian

-----Original Message-----
Hi Brian,

In W2K the default max page size in 1000. In 2003 we increased this to 1500.
The reason is that this is a 'page' size, not result size. You would want to
use what are called paged searches in order to properly get a larger result
size.

It is possible to increase this 1000 value but I would *strongly* encourage
you not to do so. Increasing that value can have some serious implications
on the directory. I won't get in to the details, but trust me, you want to
try and avoid this.

So, please look in to using paged searches. This is also covered up on MSDN
if you would like more information. Sometimes it is as simple as setting a
page size (say 500 or 1000) and from there the platform on which your code
is running will take care of the rest.

Hope this helps!
~Eric

--
Eric Fleischman [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.

I am querying AD using LDP and OpenQuery in SQL and I
cannot get more that 1000 rows to come back. Is there a
setting in AD that is limiting this?

As an alternative, is there a syntax in the LDAP filter
that would exclude anything where the distinguishedName
includes the string "Utility" or "Resource"?

Thanks in advance,

-- Brian

.

 

[Eric Fleischman [MSFT]]

BTW I just did a bit of searching and based upon what I saw I think
OpenQuery in this context may be unable to do a paged search. You may have
to go to some sort of script scenario that has the capability (VBS, C#,
etc.)
That's just based upon some searching of the 'net and previous newsgroup
posts. Again, I'm not the SQL guru here I'm afraid....if others do know
please holler.

~Eric

--
Eric Fleischman [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.

I would imagine that someone over on the SQL side of the house would be the
expert here. The key term to keep in mind is "paged search". That's the
goal.

I don't know exactly how you're doing things but a quick search of MSDN
turned up this sample code if it is at all helpful in terms of how you are
issuing the query:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adsi/adsi/searching_for_objects.asp

~Eric

--
Eric Fleischman [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.

Thanks Eric.

My real goal is to have a SQL view that lists all of
the "users" in AD. The LDAP filter returns more that
1000 rows and then I further constrain the view with a
WHERE clause. I suspect I can add these constraints in
the LDAP filter but I can't figure out a syntax that
works. Is there a way to page the results using a linked
SQL Server and an OpenQuery statement?

Thanks for your help,

-- Brian

-----Original Message-----
Hi Brian,

In W2K the default max page size in 1000. In 2003 we increased this to 1500.
The reason is that this is a 'page' size, not result size. You would want to
use what are called paged searches in order to properly get a larger result
size.

It is possible to increase this 1000 value but I would *strongly* encourage
you not to do so. Increasing that value can have some serious implications
on the directory. I won't get in to the details, but trust me, you want to
try and avoid this.

So, please look in to using paged searches. This is also covered up on MSDN
if you would like more information. Sometimes it is as simple as setting a
page size (say 500 or 1000) and from there the platform on which your code
is running will take care of the rest.

Hope this helps!
~Eric

--
Eric Fleischman [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.


I am querying AD using LDP and OpenQuery in SQL and I
cannot get more that 1000 rows to come back. Is there a
setting in AD that is limiting this?

As an alternative, is there a syntax in the LDAP filter
that would exclude anything where the distinguishedName
includes the string "Utility" or "Resource"?

Thanks in advance,

-- Brian


.