Powershell SQL Error

Question

Powershell SQL Error

Ikkarus13 96

Hello everybody,

I use Powershell and SQL to query the current search index status with the following code (found on the Internet).

$sql = "SELECT System.ItemName, System.DateCreated FROM SYSTEMINDEX"
$provider = "provider=search.collatordso;extended properties=’application=windows’;" 
$connector = new-object system.data.oledb.oledbdataadapter -argument $sql, $provider 
$dataset = new-object system.data.dataset 
$index = if ($connector.fill($dataset)) { $dataset.tables[0] }
$Anzahl = ($index | measure).count

This works quite well for a while, but at some point when the number of files that are indexed gets too large, I get the following error.

Exception calling "Fill" with "1" argument(s): "IErrorInfo.GetDescription failed with E_FAIL(0x80004005)."
At C:\ProgramData\checkmk\agent\local\check_index_status.ps1:5 char:14

$index = if ($connector.fill($dataset)) { $dataset.tables[0] }
~~~~~~~~~~~~~~~~~~~~~~~~~
CategoryInfo : NotSpecified: (:) [], MethodInvocationException
FullyQualifiedErrorId : OleDbException

Is there any way to work around this? Unfortunately, I don't know enough about it myself.

Bye, Ikkarus13

0 comments

Answer accepted by question author

0 additional answers

Your answer

Answer 1

OK, I found another code that is faster and works.

    try{
        $objConn = New-Object System.Data.OleDb.OleDbConnection("Provider=Search.CollatorDSO;Extended Properties='Application=Windows'")
        $sqlCommand = New-Object System.Data.OleDb.OleDbCommand("GROUP ON workid [0] AGGREGATE COUNT() as 'Total' OVER (SELECT workid FROM systemindex)")
        $sqlCommand.Connection = $objConn
        $objConn.open()
        $reader = $sqlCommand.ExecuteReader()
        try{
            $ignore = $reader.Read() #will cause an error but we can ignore it... without this we won't have data
        } catch {}
        $value = $reader[2]
        $value = "{0:N0}" -f $value
    } catch {}

So my problem is solved