Sign in
 
HomeLibraryLearnDownloadsRepositoryCommunityForumsBlog

none
Having Problems With SystemIndex (I Love It But Can't Make It Work How I Want)

 > 

    Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Win7 Pro (x64), 8GB Memory
    WScript solutions only please

    My Dear Friends,

    First of all, I must say that that having "Windows Desktop Search" with an SQL interface is just great.  It will greatly simplify the handling of datasets with a large number of entries much easier (I hope).

    As a first attempt to use the new feature I tried the following:

    'Enumerate the files on a disk.  The disk has approximately 500K files on it.  The code is below:

    Set adoConnection           = CreateObject("ADODB.Connection")
    Set adoRecordSet            = CreateObject("ADODB.Recordset")
    adoConnection.Provider    = "ADsDSOObject"
    adoConnection.Open "Provider=Search.CollatorDSO;Extended Properties='Application=Windows';"

    adoRecordSet.Open "SELECT System.ItemPathDisplay FROM SYSTEMINDEX " & _
        " WHERE CONTAINS('Kishinevsky')"          & _
        " AND System.ItemPathDisplay LIKE 'G:\%'" & _
        " AND System.FileExtension = '.XLSM'"     & _
        " AND System.Size > 10", adoConnection

    adoRecordSet.MoveFirst
    Wscript.Echo adoRecordset.RecordCount '(This statement also does not work)
    Results = "Record Count: " & adoRecordSet.RecordCount & vbCrLf
    Do Until adoRecordSet.EOF
       <Code to save the results left out for clarity>
       adoRecordSet.MoveNext
    Loop 'Until adoRecordSet.EOF

    WScript.Quit


    The code above works great (and fast) on some of the G-drive subfolders but as soon as the number of files exceeds about 12,000 the program errors on the "adoRecordset.MoveNext" statement with error code 80041607 (Query_E_Timeout).

    So I start searching around and I come up with the code and comments by Richard Mueller below:

    ========== Code By Richard Mueller ==========
    'http://social.technet.microsoft.com/Forums/zh/ITCG/thread/c262d5de-da6b-4e9e-9aab-2965f3109b8a

    'When I use ADO in VBScript programs to run queries, I use a command object so I can specify a timeout, among other properties. Assuming your query is valid, I would code your example as follows:

    ' Setup ADO objects.
    Set adoCommand    = CreateObject("ADODB.Command")
    Set adoConnection = CreateObject("ADODB.Connection")
    adoConnection.Open "Provider=Search.CollatorDSO;Extended Properties='Application=Windows';"
    Set adoCommand.ActiveConnection = adoConnection

    strQuery = "SELECT System.ItemName FROM SystemIndex"

    adoCommand.CommandText = strQuery
    adoCommand.Properties("Page Size") = 100
    adoCommand.Properties("Timeout") = 30
    adoCommand.Properties("Cache Results") = False

    ' Run the query.
    Set adoRecordset = adoCommand.Execute

    Wscript.Echo adoRecordset.RecordCount

    ' Clean up.
    adoRecordset.Close
    adoConnection.Close

    'You may not need the "Page Size" or "Cache Results" properties. I used the RecordCount method of the recordset object, but your code would work as well. I think I saw a Timeout property of Connection objects in the past, but I don't find an example now. I've used timeouts as long as 60 seconds, but I assume larger values are valid as well.

    'Richard Mueller
    ==================================

    I tried to run the code exactly as shown but the "adoCommand.Propereties" statements error with code 800A0cc1 (Item cannot be found in the collection corresponding to the requested name or ordinal).  I cannot figure out how to correct the error.


    Can someone please help?  I have searched till I am blue in the face but cannot figure out how to make the code work.


    As an aside, could someone please show me how to formulate the SQL search statement such as below (which works when included in the adoRecordSet.Open statement) as a defined string for use in the adoCommand.CommandText = strQuery statement.

    strQuery = "SELECT System.ItemPathDisplay FROM SYSTEMINDEX " & _
        " WHERE CONTAINS('Probability')" & _
        " AND System.ItemPathDisplay LIKE 'H:\%'" & _
        " AND System.FileExtension = '.XLSM'" & _
        " AND System.Size > 10", adoConnection

    I know that special rules apply when specifying a query string involving embedded single and double quote marks but I have been unable to figure it out.

    Thanks to all,

    SirHorace

    Monday, March 11, 2013 11:44 PM
    |
    |

Answers

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Wrong.  In this case the rules do not apply.

    Start with a simpler method.  Be sure you have at least Desktop[ Search 3.0 installed and running.

    With CreateObject("ADODB.Connection")
	.Open "Provider=Search.CollatorDSO;Extended Properties='Application=Windows';"
	Set rs = .Execute("SELECT System.ItemPathDisplay FROM SYSTEMINDEX WHERE Not Contains('adusers') " & _
    "AND System.ItemPathDisplay LIKE 'C:\Scripts\%'")
End With 
Do Until rs.EOF
    Wscript.Echo rs.Fields("System.ItemPathDisplay").Value
    rs.MoveNext
Loop

    The above is an actual running query that lists all files in C:\scripts\% that do not contain 'adusers.' It does not require any escapes.  This is not SQLServer and not T-SQL.  It is a SQL-like syntax specific to teh search provider.

    Just substitute a path that exisits on your machine for 'c:\scripts\%'.  Run it exactly as is and it will display all files in that folder.  Work from there.  To understand the syntax and options go to the website for desktop search development and read about how it works. It is a very subtle syntax and ios highly sensitive to teh system and version you are running.  Post question specific to teh search provider in teh DEsktop Search forum.  Post questions specific to vbscript in this forum.

    Good Luck.

    ¯\_(ツ)_/¯

    Tuesday, March 12, 2013 7:03 AM
    |
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    As a short bit of further comment, the changes that I made are because we do not need to use a recordset when we are just running a simple SELECT query.  The connection object of ADODB has a convenient 'Execute' method that will execute any simple statement and return a recordset. It is the fastest method to use.  It will automatically choose the correct options for a query on nearly all providers.  The recordset object is useful when we want to alter the nature of multiple recordsets for reading, inserting and modifying.  In this case we can only do one thing - read.

    In the first part I use a 'With' constuct becuse we only need the connection object temporarily and this is a very convenient way to load the recordset. 

    The second part is a simple loop that walks through the records.  When EOF occurs the loop ends.  There is never any need to do anything more complicated than this with a 'ForwardOnly' cursor.  The recordset will always start at the first record.  If we want to read the recordset again we must re-execute the query because this very light weight and very fast query cannot be positioned backwards.  If we need a recordset that can be positioned we must use a recordset object set up correctly for a dynamic query.  I do not believe that the search provider supports anything but ForwardOnly recordsets.

    ¯\_(ツ)_/¯


    • Edited by jrv Tuesday, March 12, 2013 7:13 AM
    Tuesday, March 12, 2013 7:11 AM
    |
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    My Dear jrv,

    Thank you for looking into my problem.  I am afraid, however, that I did not make myself clear in the problem description.  The confusion may have arisen because I used too complex an example which possibly obscured the problem.  I will use the example you presented and maybe that will make clear the problem I am encountering.

    I have slightly modified your example as below.  The script tries to count the number of files in a folder.

    With CreateObject("ADODB.Connection")

         .Open "Provider=Search.CollatorDSO;Extended Properties='Application=Windows';"

         Set rs = .Execute("SELECT System.ItemPathDisplay FROM SYSTEMINDEX WHERE System.ItemPathDisplay LIKE 'G:\Pictures%'")

    End With

    i = 0

    Do Until rs.EOF

       i = i + 1

       rs.MoveNext

    Loop

    Wscript.Echo "File Count: " & i

    Wscript.Quit

    The example above reports 26,674 files in the "G:\Pictures%" folder.  When I try to count the total number of files on the disk using "G:\% " the program throws the error 80041607 (Query_E_Timeout) indicating that the program may need more time to complete the task.  So my question was really how to set the time parameter mentioned in the code example by Richard Mueller.  I appreciate your simplified code example but as you see it too fails when trying to read the entire disk.

    Thanks again for looking into the problem

    SirHorace

    Thursday, March 14, 2013 7:40 PM
    |
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    Try:

    .CommandTimeOut = 0 

    This should set no timeout but not all providers support this.

    The timeout on teh Sarch service is propbaly settable in th registry.

     

    ¯\_(ツ)_/¯



    • Edited by jrv Thursday, March 14, 2013 9:29 PM
    Thursday, March 14, 2013 9:27 PM
    |
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    Sorry I posted the wrong property. It should be:
    .CommandTimeOut

    ¯\_(ツ)_/¯

    Thursday, March 14, 2013 9:30 PM
    |
    |