ADO/LDAP RecordSet Pagesize issue

I'm having a really hard time with what should be a simple VBscript.  I'm trying to do some AD work using VBscript, and ran into an issue with the LDAP limit of 1000 records.  For the life of me I cannot resolve it.

I've seen many posts with varying ways to accomplish it, and most of them have an objCommand object, and they set the objCommand.Property("Page Size") value.  

Unfortunately, I needed to do some sorting on the DN of an object and for reasons unknown to me, ADO cannot sort properly on the distinguishedName attribute if using the adUseServer CursorLocation.  The code I found and ended up using broke away from the objCommand object altogether and ended up with a separate objConnection and objRecordset objects.  (There's just way too many ways to create objects with ADO).

At any rate, my objRecordset has a page size property, but setting it seems to do no good.  What am I missing here?  Using the code attached to export just all users and display their samAccountName along with a counter shows the script bombing after record 999.

I'd appreciate any kind of help to explain to me what is going on.  This is driving me batty.

*note*  For some reason, my sorting isn't working now either!  I had to comment it out of the code in order to do this testing.  I'd like to know why I can't run the sort either (it works in another script!)
Option Explicit
Const adUseClient = 3
Const adUseServer = 2
Const adCmdText = 1
Const adSecureAuthentication = 1
Const adLockReadOnly = 1
Dim sDNSDomain
sDNSDomain = "MyDomain.Net"
Dim sADFilter
sADFilter = "(&(objectClass=User)(ObjectCategory=Person))"
Dim sADProperties
sADProperties = "samAccountName,distinguishedName"
Dim sADSearchScope
sADSearchScope = "subtree"
Dim sADQuery
sADQuery = "<LDAP://" & sDNSDomain & ">;" & sADFilter & ";" & sADProperties & ";" & sADSearchScope
Dim oConn
Set oConn = CreateObject("ADODB.Connection")
oConn.Provider= "ADsDSOObject"
oConn.Properties("Encrypt Password") = True
oConn.Properties("ADSI Flag") = adSecureAuthentication
oConn.Open "Active Directory Provider"
Dim oRecordSet
Set oRecordSet = CreateObject("ADODB.Recordset")
oRecordSet.CursorLocation = adUseClient
'oRecordSet.Sort = "distinguishedName"  '<----generates error:  Microsoft Cursor Engine: Data provider or other service returned an E_FAIL status.
oRecordSet.PageSize = 10000  '<----tried all kinds of values, e.g., 10, 100, 1000, 10000
oRecordSet.Open sADQuery, oConn, , , adCmdText
Dim iCounter
iCounter = 0
Do Until oRecordset.EOF
	Wscript.Echo iCounter & vbtab & oRecordset.Fields("samAccountName")
	iCounter = iCounter + 1

Open in new window

Who is Participating?
Krys_KConnect With a Mentor Commented:
Hi There
Try this.
You will want to run it from a command prompt otherwise you will get a lot of popup boxes. :-)
cscript.exe scriptname.vbs
Hope it helps

' Version 1.0
' Declare variables	
	Dim oRoot
	Dim strBase, strFilter, strCriteria, strLevel
	Dim adoConnection, adoCommand, adoRecordset
	Dim strQuery
' ADO CursorLocation Constants
	Const adUseClient = 3 
' Get the Domain you are currently In
	Set oRoot = GetObject("LDAP://rootDSE")
		strBase = oRoot.Get("defaultNamingContext")
' Build the Filter string according to our computer type request
		strFilter = "(&(objectCategory=user)(cn=*))"
' Set our Attribute Criteria and Search Level
 		strCriteria = "sAMAccountName,distinguishedName"
 		strLevel = "SubTree"
' Build our complete query string using the DC Server Name if passed
		strQuery = "<LDAP://" & strBase & ">;" & strFilter & ";" & strCriteria & ";" & strLevel
' Set up the connection to Active Directory using ADO
	Set adoConnection = CreateObject("ADODB.Connection")
	Set adoCommand = CreateObject("ADODB.Command")
		adoConnection.Provider = "ADsDSOObject"
		adoConnection.Open = "Active Directory Provider"
		adoConnection.Cursorlocation = adUseClient
	Set adoCommand.ActiveConnection = adoConnection
		adoCommand.CommandText = strQuery
		adoCommand.Properties("Page Size") = 1000
		adoCommand.Properties("Timeout") = 30
		adoCommand.Properties("Cache Results") = False
' Get the recordset results of the query to Active Directory
	Set adoRecordset = adoCommand.Execute
		adoRecordset.Sort = "distinguishedName"
' Loop all the records that were found
		Do Until adoRecordset.EOF = True
			WScript.Echo adoRecordset.Fields("sAMAccountName").Value
			WScript.Echo adoRecordset.Fields("distinguishedName").Value
' Close the Recordset and clear the variables
	Set adoRecordset = Nothing
	Set adoCommand = Nothing
	Set adoConnection = Nothing

Open in new window

FADVMSAdminAuthor Commented:
Your sample does work.  Thanks alot.  The question now is, why?  Can you explain why your script seems to work, whereas other examples of scripts using the ado.command object do not when using the sort?  Ultimately why does my code not work?  This is more of the answer I'm looking for.

It's rather irritating to have so many different ways to do something with ADO, and whereas it seems no matter which way you go, you have the same/similar properties, but for whatever reason some alternate methods do not work.  

Hi there
I can only answer this given your code sample.
To enable paged searching in ADO, you must instantiate an ADO Command object. A Command object allows for various properties of a query to be set, such as size limit, time limit, and page size. See MSDN for the complete list.
What you were doing was using the recordset object page size property instead of the Command object. The difference how i understand it is that the command object properties are set to define your query and its results. the recordset is working on the returned records.
Hope that makes sense
Some links to help
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

the sorting in your code sample would work if you put it after you have opened the recordset. It is quite logical, because the recordset object cannot be aware of any content before the statement
oRecordSet.Open sADQuery, oConn, , , adCmdText
I am not sure if the paging would work if you set pagesize also after you have opened the recordset. You can try if you are curious (like I am).

Kind regards,
FADVMSAdminAuthor Commented:
@Krys K:

Thank you.  That kinda makes sense to me now.


While what you say would seem to make sense, I must disagree.  The code sample I am using is straight from MS:

Also, using my code, if I limit my AD filter to "(&(objectClass=User)(ObjectCategory=Person)(samAccountName=m*))" so that my result set is less than 1000, the sorting works just fine using my code.   Just an FYI.


My previous declaration about the sorting not working may be simply a side effect of the other issue: the recordset blowing up due to the 1000 result limit.  I'm not 100% sure but as stated above when under 1000 records are pulled, the sort works fine.  *shrug*
My apologies for trying to fool you. At least "would seem to make sense" is not the worst grade possible :=)
FADVMSAdminAuthor Commented:
:).  Thanks for chiming in though.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.