exit script after writing error to log

Question

I'm writing a script to deploy a bunch of sql files to sql server. Requirements are:

1. Everything should be written to a log file
2. The script should end when encountering an error (the logic is we fix it then re-run the script again).

I have a function which 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
    }
}

That's great. This exits and write some errors in the console. But I need to log the 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 written in the logs but the script doesn't exit anymore. So I try to use "finally" statement. It doesn't seem to work any 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
    }
}

Error log should have ended when logging first error 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 stopping error into a file, and exit the script completely?

Answer 1

Now my errors are written in the logs but the script doesn't exit anymore.

That's because your catch block is handling the error and doesn't take any action.

If you want to rethrow the error, then just do this:

catch{
    WriteLog("ERROR - $($_)")
    throw
}

Now you error will be logged, as well as the catch block will rethrow the error so that it bubbles up.

As far as "bubbling" the error up, the calling code should be handling this. The calling code might want to handle the exception accordingly, or it could just exit the script.

# calling code
try {
    runQueriesInFolder(...)
}
catch {
    # hit an error, don't want to keep script running
    exit
}