Search code examples
powershellsqlcmd

Powershell : sqlcmd error handling with multiple files

We have folder with multiple sql files. How to catch error with particular file and log it that file name.

We are using following script to execute sqlcmd.

$ServerName=$args[0]
$DatabaseName=$args[1]
$UserName=$args[2]
$Passcode=$args[3]
$FolderPath=$args[4]
$errorpath=$args[5]

foreach ($f in Get-ChildItem -path  $FolderPath -Filter *.sql | sort-object) {
    $fileCurrent = $f.fullname
     try { 
         invoke-sqlcmd -ServerInstance $ServerName -Database $DatabaseName -U  $UserName  -P $Passcode -InputFile $f.fullname 
}
catch {
   $_ | Out-File $errorpath -Append
}

}

we have 100 files in that folder and two files are erroring out we want to log error and file name.

Solution

  • If you aren't hitting the catch block, it's because you are encountering a "non-terminating" error. Non-terminating errors don't trigger catch blocks.

    To force your error to be terminating, you can either pass -ErrorAction 1 to Invoke-SQLCmd or before the loop, set $errorActionPreference = 'Stop'.

    With this change, your catch block should now be triggered when Invoke-SQLCmd fails.