question

Ikkarus13-6686 avatar image
0 Votes"
Ikkarus13-6686 asked Ikkarus13-6686 answered

Powershell SQL Error

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

windows-server-powershell
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Ikkarus13-6686 avatar image
0 Votes"
Ikkarus13-6686 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.