Powershell : sqlcmd error handling with multiple files

Question

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.

score 0 · Answer 1 · answered Sep 25 '12 at 00:08

0

try {
  ...
} catch {
  "{0}: {1}" -f ($fileCurrent, $_) | Out-File $errorpath -Append
}

answered Sep 25 '12 at 00:08

Ansgar Wiechers

193,178
25
254
328

we are getting error like this but here there is no file path and its not logging to $errorpath too Invoke-Sqlcmd : Incorrect syntax near the keyword 'PROCEDURE'. A RETURN statement with a return value cannot be used in this context.At C:\psscripts\ip.ps1:15 char:22 + invoke-sqlcmd <<<< -ServerInstance $ServerName -Database $DatabaseName -U $UserName -P $Passcode -InputFile $f.fullname + CategoryInfo : InvalidOperation: (:) [Invoke-SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand – Kumar_2002 Sep 25 '12 at 00:34
One more thing its not even goin to catch block when debugging – Kumar_2002 Sep 25 '12 at 00:37
Does the script continue despite the error? If so, try setting `$ErrorActionPreference = "Stop"`. – Ansgar Wiechers Sep 25 '12 at 07:02
It will continue we want that one – Kumar_2002 Sep 25 '12 at 16:12
yes after adding this $ErrorActionPreference = "Stop" it comes to catch block thanks – Kumar_2002 Sep 25 '12 at 21:51

score 0 · Accepted Answer · answered Sep 25 '12 at 16:37

0

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.

answered Sep 25 '12 at 16:37

latkin

16,402
1
47
62

Ya thanks man after adding $errorActionPreference = 'Stop' now its coming to catch block – Kumar_2002 Sep 25 '12 at 21:51

Powershell : sqlcmd error handling with multiple files

2 Answers2