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

x
?
Solved

ADO/LDAP RecordSet Pagesize issue

Posted on 2009-04-21
7
Medium Priority
?
4,989 Views
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
	oRecordset.MoveNext
Loop
 
oConn.Close

Open in new window

0
Comment
Question by:FADVMSAdmin
  • 3
  • 2
  • 2
7 Comments
 
LVL 12

Accepted Solution

by:
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
 
Regards
Krystian


	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
 
			adoRecordset.MoveNext
		Loop
 
' Close the Recordset and clear the variables
		adoConnection.Close
 
	Set adoRecordset = Nothing
	Set adoCommand = Nothing
	Set adoConnection = Nothing
 
 
 
End Sub ' ADOLDAP

Open in new window

0
 
LVL 1

Author Comment

by:FADVMSAdmin
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.  

0
 
LVL 12

Expert Comment

by:Krys_K
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
Regards
Krystian
Some links to help
0
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

by:StellanRosengren
ID: 24245105
FADVMSAdmin,
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,
Stellan
0
 
LVL 1

Author Comment

by:FADVMSAdmin
ID: 24245848
@Krys K:

Thank you.  That kinda makes sense to me now.

@StellanRosengren:

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.

@Myself

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

Expert Comment

by:StellanRosengren
ID: 24245939
FADVMSAdmin,
My apologies for trying to fool you. At least "would seem to make sense" is not the worst grade possible :=)
Thanks
0
 
LVL 1

Author Comment

by:FADVMSAdmin
ID: 24246020
:).  Thanks for chiming in though.
0

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