Getting errors from Sqlcmd in PowerShell script

Question

I work for a government client and our deployments are controlled by another contractor who has requested we provide powershell scripts to run our database scripts. Unfortunately security is tight and I don't have permissions to add powershell snapins so I have to use good old sqlcmd instead of Invoke-Sqlcmd. I am having trouble getting errors generated from running the scripts to be passed back to powershell.

For example imagine in the scenario below that "02 test2.sql" has an error that causes it to fail to run. I would like this to terminate the script, log a message to the console and also log the output of sqlcmd to a file. No matter what I try I can't seem to get a sqlcmd failure to be recognized by PowerShell.

function TerminateScript {
    ECHO "Please email `"Production.log.txt`" to [email protected]"
    Read-Host -Prompt "Press Enter to exit"
    exit 1
}

ECHO "Running `"01 test1.sql`"" | tee -a Production.log.txt
sqlcmd -r -b /S serverName /d dbName -i "01 test1.sql" >> Production.log.txt
if ($LASTEXITCODE -ne 0) {
    Write-Error "Error occured while running `"01 test1.sql`". Terminating script."
    TerminateScript
}

ECHO "Running `"02 test2.sql`"" | tee -a Production.log.txt
sqlcmd -r -b /S serverName /d dbName -i "02 test2.sql" >> Production.log.txt
if ($LASTEXITCODE -ne 0) {
    Write-Error "Error occured while running `"02 test2.sql`". Terminating script."
    TerminateScript
}

ECHO "Running `"03 test3.sql`"" | tee -a Production.log.txt
sqlcmd -r -b /S serverName /d dbName -i "03 test3.sql" >> Production.log.txt
if ($LASTEXITCODE -ne 0) {
    Write-Error "Error occured while running `"03 test3.sql`". Terminating script."
    TerminateScript
}

Answer 1

Your method should work if you just redirect the proper output streams to your file. >> by default only redirects the success stream. The error stream is 2 (2>>). All streams is * (*>>). If you want to redirect only the error and success streams, you can send error stream to the success stream and then send the success stream to your file via 2>&1 >> Production.log.txt. $LastExitCode will still provide a 1 if the query produces an error.

Example: Redirecting All Streams

ECHO "Running `"02 test2.sql`"" | tee -a Production.log.txt
sqlcmd -r -b /S serverName /d dbName -i "02 test2.sql" *>> Production.log.txt
if ($LASTEXITCODE -ne 0) {
    Write-Error "Error occured while running `"02 test2.sql`". Terminating script."
    TerminateScript
}

Example: Redirecting Error and Success Streams

sqlcmd -r -b /S serverName /d dbName -i "02 test2.sql" 2>&1 >> Production.log.txt

I know this is not EXACTLY what you want, but it is an alternative. You can use Start-Process to call sqlcmd and redirect the error and success streams. Keep in mind that the output files are overwritten every time it is run. The error.txt file will be empty if there were no errors.

Start-Process sqlcmd -ArgumentList "-r -b /S poc-vtxrtndb2 /d test -i `"02 test2.sql`"" -NoNewWindow -RedirectStandardError error.txt -RedirectStandardoutput log.txt
if (Get-Content error.txt) {
    "There was an error in the script"
}

See About_Redirection for more information on PowerShell output redirection.