Exit script after logging error
I am writing a script to deploy a bunch of sql files to a sql server. Requirements:
- Everything should be written to the log file
- The script should terminate when an error occurs (the logic is to fix this and then run the script again).
I have a function that runs a bunch of sql files from a folder.
function writeLog($comment,
$logfile="C:\Users\ndefontenay\Documents\Releases\logs\release-update.log"){
$date=Get-Date -Format "MM/dd/yyyy HH:mm:ss"
$fullComment="$date - $comment"
$fullComment | Out-File -Append $logfile
}
function runQueriesInFolder($folder, $server=".",$database){
Write-Host $folder
$files=Get-ChildItem -Recurse -Path $folder -Filter "*.sql" | Sort-Object -Descending
foreach ($file in $files){
invoke-sqlcmd -InputFile $file.FullName -Database $database -OutputSqlErrors $True -ErrorAction Stop -ServerInstance $server -queryTimeout 65536
}
}
It's great. This terminates and logs some errors to the console. But I need to log errors, so I use try..catch.
function runQueriesInFolder($folder, $server=".",$database){
Write-Host $folder
$files=Get-ChildItem -Recurse -Path $folder -Filter "*.sql" | Sort-Object -Descending
try{
foreach ($file in $files){
invoke-sqlcmd -InputFile $file.FullName -Database $database -OutputSqlErrors $True -ErrorAction Stop -ServerInstance $server -queryTimeout 65536
}
}
catch{
WriteLog("ERROR - $($_)")
}
}
Now my errors are being written to the logs, but the script no longer exits. So I am trying to use the "finally" statement. It doesn't seem to work better.
function runQueriesInFolder($folder, $server=".",$database){
Write-Host $folder
$files=Get-ChildItem -Recurse -Path $folder -Filter "*.sql" | Sort-Object -Descending
try{
foreach ($file in $files){
invoke-sqlcmd -InputFile $file.FullName -Database $database -OutputSqlErrors $True -ErrorAction Stop -ServerInstance $server -queryTimeout 65536
}
}
catch{
WriteLog("ERROR - $($_)")
}
finally{
exit 1
}
}
The error log should have ended on first entry into the error log Invalid object name:
07/23/2015 10:57:45 - ERROR - Invalid object name 'History.DOS.Test'.
07/23/2015 10:57:45 - ERROR - Cannot open database "blah" requested by the login. The login failed.
07/23/2015 10:57:45 - ERROR - The 'Query' and the 'InputFile' options are mutually exclusive.
07/23/2015 10:57:45 - Release update completed
So the question is, how do I get my application to log the stop error in a file and terminate the script entirely?
source to share
Now my errors are being written to the logs, but the script no longer exits.
This is because your block is catch
handling the error and takes no action.
If you want to reinstall the error, just do this:
catch{
WriteLog("ERROR - $($_)")
throw
}
Now your error will be logged and also the catch block will recover the error to bubble up.
As for the "bubbling" error, the calling code must handle it. The calling code might want to handle this exception, otherwise it might just exit the script.
# calling code
try {
runQueriesInFolder(...)
}
catch {
# hit an error, don't want to keep script running
exit
}
source to share