ADO/LDAP RecordSet Pagesize issue

Posted on 2009-04-21
Last Modified: 2013-12-24
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

Question by:FADVMSAdmin
    LVL 12

    Accepted Solution

    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

    	Call ADOLDAP
    Sub ADOLDAP()
    ' 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
    End Sub ' ADOLDAP

    Open in new window

    LVL 1

    Author Comment

    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.  

    LVL 12

    Expert Comment

    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
    LVL 13

    Expert Comment

    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,
    LVL 1

    Author Comment

    @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*
    LVL 13

    Expert Comment

    My apologies for trying to fool you. At least "would seem to make sense" is not the worst grade possible :=)
    LVL 1

    Author Comment

    :).  Thanks for chiming in though.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Creating and Managing Databases with phpMyAdmin in cPanel.
    In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
    In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now