Use SQL to query a recordset in vbscript

So I have a script that updates our active directory based on data from our employee records database. Currently I pull all records from a view of the employee database, and cycle through each record, then query AD baesd on the users employee ID.

So this means I query AD for everyone employee I cycle through. My script takes about two hours to complete for around 2500 users. I was wondering if it would be quicker to pull all of AD into a recordset, and rather then querying AD for every user, I could query the AD recordset which would be in memory.

I can't seem to find if this is possible with vbscript. I know I can search the recordset, but not using SQL. If anyone has any ideas for me that would be great.
colesyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

exx1976Commented:
You can write SQL queries against AD, like the example code below.

HTH,
exx
On Error Resume Next
Const ADS_SCOPE_SUBTREE = 2
Set oConn = CreateObject("ADODB.Connection")
Set oComm = CreateObject("ADODB.Command")
oConn.Provider = ("ADsDSOObject")
oConn.Open "Active Directory Provider"
oComm.ActiveConnection = objConnection
oComm.CommandText =  "SELECT sAMAccountName, homeDirectory FROM 'LDAP://dc=domainname,dc=com' WHERE objectCategory='user' AND sAMAccountName='testusername'"
oComm.Properties("SearchScope") = ADS_SCOPE_SUBTREE
Set objRecordSet = objCommand.Execute
If objRecordSet.RecordCount = 0 Then
    Wscript.Echo "The samAccount was not found"
Else
    Wscript.Echo "The user Home drive is: " & objRecordSet.Fields("homeDirectory").Value
End If

Open in new window

0
colesyAuthor Commented:
I know you can do that, which is what I am currently doing..  What I would like to do is take your objRecordSet results, and query those. So in my AD query I would do something like this.

oComm.CommandText  = select samaccountname, employeeID, surname, etc from AD where objectCategory='user'
Set objRecordSet = oComm.Execute

select * from objRecordset where employeeID = strEmployeeID

So that recordset would include all users within AD. Then I could query against that entire recordset rather then query AD every time.
0
exx1976Commented:
Ahh.  You had failed to mention that you were already querying AD using ADODB.  I thought you might have been doing it old-school.  LOL


Ok..  Well, you can't *query* a disconnected recordset, but you CAN use filters.  See example.  It's kind of quick and dirty, so you may need to fiddle with it, but you'll get the idea.


HTH,
exx
On Error Resume Next 
Const ADS_SCOPE_SUBTREE = 2 
Set oConn = CreateObject("ADODB.Connection") 
Set oComm = CreateObject("ADODB.Command") 
oConn.Provider = ("ADsDSOObject") 
oConn.Open "Active Directory Provider" 
oComm.ActiveConnection = objConnection 
oComm.CommandText =  "SELECT SAMAccountName, employeeID, sn FROM 'LDAP://dc=domainname,dc=com' WHERE objectCategory='user'" 
oComm.Properties("SearchScope") = ADS_SCOPE_SUBTREE 
Set oRS = oComm.Execute 
oRS.Filter = "SAMAccountName = 'JohnDoe'"
oRS.MoveFirst
If oRS.RecordCount > 0 Then
	Do While Not oRS.EOF
		WScript.Echo oRS.Fields("EmployeeID")
		oRS.MoveNext
	Loop
End If
oRS.Filter = ""

Open in new window

0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

colesyAuthor Commented:
So could I set the filter inside my while statement as I cycle through employee_IDs from my employee records database?

Basically I would pull all records from the employee records database, then pull all records from AD.

Start my while to cycle through each record from the employee records, and inside that set my filter using the employee ID?
0
exx1976Commented:
That sounds like it should do what you want, yes.
0
colesyAuthor Commented:
I will test it out and let you know. Sounds like it might do what I want.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.