Powershell catch SQL errors and redirect to log file

Question

I've try, catch statement in my powershell to handling errors exception, but below statement didn't output sql error messages to .log file.

Try{

Invoke-SqlCmd -InputFile "D:\DropBoxFolder\Dropbox\Public\WIKI_POWERSHELL\AdminTask\ExecutePowerShell_From_BAT\Map_AU.sql"
   }

Catch{
$ErrorMessage = $_.Exception.Message
$Time=Get-Date
"This script failed at $Time and error message was $ErrorMessage" | out-file D:\DropBoxFolder\Dropbox\Public\WIKI_POWERSHELL\AdminTask\ExecutePowerShell_From_BAT\Inovke_SQLScript.log -append
   }

Finally{

$Time=Get-Date
"This script made a read attempt at $Time" | out-file D:\DropBoxFolder\Dropbox\Public\WIKI_POWERSHELL\AdminTask\ExecutePowerShell_From_BAT\Inovke_SQLScript.log -append

$con.Close()
   }

Here the errors returned from powershell and I would like to redirect the errors to .log

Invoke-Sqlcmd : There is already an object named 'Map_AU234' in the database. At D:\DropBoxFolder\Dropbox\Public\WIKI_POWERSHELL\AdminTask\ExecutePowerShell_ From_BAT\Inovke_SQLScript.ps1:49 char:14 + Invoke-SqlCmd <<<< -InputFile "D:\DropBoxFolder\Dropbox\Public\WIKI_POWERSHE LL\AdminTask\ExecutePowerShell_From_BAT\Map_AU.sql" + CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerS hellSqlExecutionException + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShe ll.GetScriptCommand

I think you may have to enclose your out-file in quotes as shown here - $Time=Get-Date "This script made a read attempt at $Time" | out-file "D:\DropBoxFolder\Dropbox\Public\WIKI_POWERSHELL\AdminTask\ExecutePowerShell_From_BAT\Inovke_SQLScript.log" -append — vmachan
– vmachan, Commented Jan 10, 2016 at 6:48
I found out it doesn't go to catch block if error occurs in sql file. — user664481
– user664481, Commented Jan 10, 2016 at 7:42
It works after I added -ErrorAction Stop at the end of Invoke-SqlCmd — user664481
– user664481, Commented Jan 10, 2016 at 8:41

user664481 · Accepted Answer · 2016-01-10 08:42:28Z

1

It works after I added -ErrorAction Stop at the end of Invoke-SqlCmd

answered Jan 10, 2016 at 8:42

user664481

2,3119 gold badges26 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Powershell catch SQL errors and redirect to log file

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related