Solved VBScript broke - ADODB.Recordset no longer working?

December 25, 2012 at 15:03:11
Specs: Windows 2008 Rs, Athlon XP dual core
I wrote a script some time ago that searches through a folder on my PC, grabbing file properties from the search indexer (photo Date Taken property). This worked just fine on Windows 2003 R2, but now that we've upgraded to Windows 2008 R2, it's finding no entries. Here is the beginning of my script (ignore my extra Dim variables):

Option Explicit
Dim objFSO, objConnection, objRecordSet, objFolder, objFolderItem, objShell
Dim OS, DSTCheck, NoDateTakenProp
Dim FilePath, FileNameAndExtension, FileName, FileExtension
Dim strYear, strMonth, strDay, strHour, strMinute, strSec
Dim OldFileAndPath, NewFileAndPath, NewFileName, SubDirectory
Dim SplitFileNameAndExtension, SplitFolderPath, dtmPhotoDate
Dim SplitFileName, bUnknownFileFormat

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
Set objShell = CreateObject("Shell.Application")

objConnection.Open "Provider=Search.CollatorDSO;Extended Properties='Application=Windows';"
objRecordSet.Open "SELECT System.ItemPathDisplay, System.DateCreated, System.ItemNameDisplay, System.DateModified, System.ItemFolderPathDisplay, System.Photo.DateTaken FROM SYSTEMINDEX Where System.ItemFolderPathDisplay = 'D:\Dropbox\DCIM\100MEDIA'", objConnection

'Start at the Beginning of the RecordSet

My script bombs at the objRecordSet.MoveFirst command with error 800a0bcd, which tells me that it's found no records. I replaced that part with the following:

'Start at the Beginning of the RecordSet
If objRecordSet.EOF Then
Msgbox("No records found!")
End If

Now I get a popup and the script exits. Which is the same as was happening before, but I've confirmed it's finding no file entries. There are files in the folder. I have installed the File Server role with the search indexer feature on the server in question. I have added the "D:\Dropbox\DCIM\100MEDIA" folder as the only indexed location (so as not to waste time indexing the rest of the drive when all I want is in this folder).

So I'm wondering, does anyone know of something that's changed in scripting against search indexer records between Win2k3 R2 and Win2k8 R2, and what do I need to change in my script to help sort out the change?

See More: VBScript broke - ADODB.Recordset no longer working?

Report •

December 27, 2012 at 18:53:22
I don't have a running copy of Win2K8R2, but I do have Win7, and the query works. I find it improbable that MS would break ADO in such a way that would break every modern database application, so I'm more inclined to suspect the data source.

I would suggest running a query like "SELECT System.ItemPathDisplay FROM SYSTEMINDEX" to see what, if anything, is in the index. You could also manually rebuild the index, and see if that solves the problem.

How To Ask Questions The Smart Way

Report •

December 28, 2012 at 10:30:31
Forgive my ignorance, but where do I go to execute the SELECT command you posted? I'm editing the script via Notepad++ and just running the .VBS file without a special editing/compiling software.

What you say makes sense, so I'm inclined to agree, although the roles and features thing adds a little bit of confusion. I did try deleting and rebuilding the index, both by selecting the rebuild option and also by unchecking all indexed locations, rebooting and checking only the one I care about. I also noted that Win2k3 has two separate features for the File Server role: "Windows Search Service" and "Windows Server 2003 File Services" which includes a sub-option for "Indexing Service". The two features cannot be installed at the same time. Originally I thought maybe my problem was that I needed the Indexing Service and to be using the 2003 File Services option, so I uninstalled Windows Search Service and installed and configured the other, and then my script came back with an error indicating that it couldn't find the Search service, so I went back. Long story short, I tried both options, and am not 100% sure I'm using the right one, but having Windows Search Service installed at least gives my script a recordset to query, albeit an empty one. I assume this is the right option to use?

Report •

December 28, 2012 at 21:46:48
✔ Best Answer
I would suggest a script like the following to see what's in the database. Also, go with the option that doesn't give an error.

Set rs = CreateObject("ADODB.Recordset")
rs.CursorType = 3
rs.Open "SELECT System.ItemPathDisplay FROM SYSTEMINDEX", _
  "Provider=Search.CollatorDSO;Extended Properties='Application=Windows';"
With CreateObject("Scripting.FileSystemObject").OpenTextFile( _
 "out.txt", 2, True, -1)
  .WriteLine "Total: " & rs.RecordCount
  Do Until rs.EOF
    .WriteLine rs(0)
End With

How To Ask Questions The Smart Way

Report •

Related Solutions

December 29, 2012 at 12:19:00
Running that script gives me the following in out.txt:

Total: 147
D:\Dropbox\My Pictures\100MEDIA\IMAG0158.jpg
D:\Dropbox\My Pictures\100MEDIA\IMAG0163.jpg
D:\Dropbox\My Pictures\100MEDIA\IMAG0159.jpg
D:\Dropbox\My Pictures\100MEDIA\IMAG0164.jpg
D:\Dropbox\My Pictures\100MEDIA\IMAG0156.jpg
D:\Dropbox\My Pictures\100MEDIA\IMAG0166.jpg
D:\Dropbox\My Pictures\100MEDIA\IMAG0165.jpg
D:\Dropbox\My Pictures\100MEDIA\IMAG0167.jpg...

I see the difference between this path and the one specified in my script:

objRecordSet.Open "SELECT System.ItemPathDisplay, System.DateCreated, System.ItemNameDisplay, System.DateModified, System.ItemFolderPathDisplay, System.Photo.DateTaken FROM SYSTEMINDEX Where System.ItemFolderPathDisplay = 'D:\Dropbox\DCIM\100MEDIA'", objConnection

This, I now see, is a result of me storing my My Pictures folder in my Dropbox, which is synced with the camera folder on my phones and tablet. Apparently in Win2003 Windows indexing service recognized the path by its actual on-disk path of "DCIM", while Win2008 now uses the alias of "My Pictures". I tried simply changing this in the above line and it is able to process the files, but then it is unable to move the files within the aliased folder.

I'll have to add a little string editing function to make it work again, but your script helped me see what I'm actually working with. Thanks so much!

Edit: The extra script I needed to add to make this work, (if someone else runs into this issue) is a simple string replace statement anywhere you're using the file path (in my case for the rename portion of my script):

OldFileAndPath = objRecordset.Fields.Item("System.ItemPathDisplay")
OldFileAndPath = Replace(OldFileAndPath,"My Pictures","DCIM")
FilePath = objRecordset.Fields.Item("System.ItemFolderPathDisplay")
FilePath = Replace(FilePath,"My Pictures","DCIM")

Report •

Ask Question