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

**URL:** <https://forums.powershell.org/t/formatexception-index-zero-based-must-be-greater-than-or-equal-to/16320>\
**Category:** PowerShell Help\
**Created:** [April 23, 2021, 6:30pm UTC](https://forums.powershell.org/t/formatexception-index-zero-based-must-be-greater-than-or-equal-to/16320 "2021-04-23T18:30:59Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![kevins](https://avatars.discourse-cdn.com/v4/letter/k/ee59a6/32.png) [@kevins](https://forums.powershell.org/u/kevins)\
**Post date:** [April 23, 2021, 6:30pm UTC](https://forums.powershell.org/t/formatexception-index-zero-based-must-be-greater-than-or-equal-to/16320/1 "2021-04-23T18:30:59Z")

</div>

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 (!!!)

```auto
#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.

---

<div class="post-metadata">

**Author:** ![rob-simmers](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/rob-simmers/32/1010_2.png) [@rob-simmers](https://forums.powershell.org/u/rob-simmers)\
**Post date:** [April 26, 2021, 3:32pm UTC](https://forums.powershell.org/t/formatexception-index-zero-based-must-be-greater-than-or-equal-to/16320/2 "2021-04-26T15:32:10Z")

</div>

There a several things that you should look at.

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

```auto
$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:

```auto
#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.

---

<div class="post-metadata">

**Author:** ![dotnVo](https://avatars.discourse-cdn.com/v4/letter/d/4af34b/32.png) [@dotnVo](https://forums.powershell.org/u/dotnVo)\
**Post date:** [May 16, 2024, 8:26pm UTC](https://forums.powershell.org/t/formatexception-index-zero-based-must-be-greater-than-or-equal-to/16320/3 "2024-05-16T20:26:32Z")

</div>