FormatException: Index (zero based) must be greater than or equal to

PowerShell Help
1

Here is my code which works perfectly from the ISE. I am running it in SQL Server Agent. The job still exports the data from a table to a .csv but it shows the task as failed and throws the error below. Here is my code. I have marked what I think is causing the error (!!!)

#boiler plate function to connect to a database, run a query and export results.
Function Execute-SqlSelect($command,$serverName,$dbName)
{
    $resultDataSet = New-Object System.Data.DataSet
    
    $sqlConn = New-Object System.Data.SqlClient.SqlConnection
    $sqlConn.ConnectionString = "Server=$serverName;Database=$dbName;Integrated Security=SSPI"
    
    $sqlConn.Open()
    
    $sqlDataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $sqlCommand = New-Object System.Data.SqlClient.SqlCommand
    
    if($sqlConn.State -eq 1)
    {
        $sqlCommand.CommandText = $command
        $SqlCommand.Connection = $sqlConn
	$sqlCommand.CommandTimeout = 0
        $sqlDataAdapter.SelectCommand = $sqlCommand
        $sqlDataAdapter.Fill($resultDataSet,"result") | Out-Null
        
        $sqlConn.Close()
    }
    
    return $resultDataSet
}

$query = "
SELECT distinct server_instance_name,event_time,server_principal_name,[database_name],[object_name],
REPLACE(REPLACE([statement] , CHAR(13), ''), CHAR(10), '') as [statement],sequence_number, application_name, host
from myDatabase.dbo.audit_rows  where LoadDate >  DATEADD(HOUR,-1,GETDATE())
                "
#!!!!!I think the error is coming from the next 5 lines !!!!!!!!!!

$SQLInstances = Invoke-Command -ComputerName $env:COMPUTERNAME {
 (Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server').InstalledInstances
                                                                }
    foreach ($sql in $SQLInstances) {
    [string]$combo = $sql.PSComputerName+'\'+$sql
    $combo = $combo -replace "\\MSSQLSERVER",""
                                    }

$results = Execute-SqlSelect $query $combo "myDatabase"

$dt = get-date -Format yyyy_MM_dd_hh_mm_ss
[string]$file =  "c:\auditLogs\sqlserverAudits_$env:computername$dt.csv"

$results.Tables[0] | export-csv ($file ) -notypeinformation -Delimiter ',' -encoding ASCII

Here is the error:
The job script encountered the following errors. These errors did not stop the script: Unhandled Exception: System.FormatException: Index (zero based) must be greater than or equal to zero and less than the size of the argument list. at System.Text.StringBuilder.AppendFormatHelper(IFormatProvider provider, String format, ParamsArray args) at System.String.FormatHelper(IFormatProvider provider, String format, ParamsArray args) at Microsoft.SqlServer.Management.PowerShell.Res.GetString(String key, Object arg0) at Microsoft.SqlServer.Management.PowerShell.SqlPowerShell.GetErrorRecordMessage(ErrorRecord errorRecord) at Microsoft.SqlServer.Management.PowerShell.SqlPowerShell.HandleAgentJob(RunspaceConfiguration config, IList`1 listOfScriptConfigurationEntries) at Microsoft.SqlServer.Management.PowerShell.SqlPowerShell.Main(String args). Process Exit Code -532462766. The step failed.

2

There a several things that you should look at.

Use a here-string for the query, it’s much cleaner:

$query = @"
SELECT DISTINCT server_instance_name
               ,event_time
               ,server_principal_name
               ,[database_name]
               ,[object_name]
               ,REPLACE(REPLACE([statement], CHAR(13), ''), CHAR(10), '') as [statement]
               ,sequence_number
               ,application_name
               ,host
FROM myDatabase.dbo.audit_rows
WHERE LoadDate > DATEADD(HOUR,-1,GETDATE())
"@

This appears to connect to the local server and do an Invoke-Command, which you can just call Get-Item directly on the local server:

#Are you invoking the command on the local server or a remote server?
$SQLInstances = Invoke-Command -ComputerName $env:COMPUTERNAME {
    (Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server').InstalledInstances
}

#$sql is an object, so you use $sql.PSComputerName on the first string 
#concantenation, but the reference $sql on the last portion, which is the 
#entire object.  If there were 10 instances on this sql server, $combo will
#only populate with the last instance.   
foreach ($sql in $SQLInstances) {
    [string]$combo = $sql.PSComputerName + '\' + $sql
    $combo = $combo -replace "\\MSSQLSERVER",""
}

That code block above can probably be much more simplified, but you’ll need to provide an example of the .Instances data.

1 Like