[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


ADO/LDAP RecordSet Pagesize issue

Posted on 2009-04-21
Medium Priority
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
  • 3
  • 2
  • 2
LVL 12

Accepted Solution

Krys_K earned 1500 total points
ID: 24205556
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


Author Comment

ID: 24224525
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

ID: 24238203
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 13

Expert Comment

ID: 24245105
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,

Author Comment

ID: 24245848
@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:  http://support.microsoft.com/kb/842637.

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

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

Author Comment

ID: 24246020
:).  Thanks for chiming in though.

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The SignAloud Glove is capable of translating American Sign Language signs into text and audio.
If you are a mobile app developer and especially develop hybrid mobile apps then these 4 mistakes you must avoid for hybrid app development to be the more genuine app developer.
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…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

834 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