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
}

AdminOfThings · Accepted Answer · 2019-06-08 13:47:44Z

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.

Moerwald · Accepted Answer · 2019-06-08 08:01:19Z

0

Not sure if Invoke-Sqlcmd throws an exception, but if so, you can catch and log it:

try {
   Invoke-Sqlcmd ... -ErrorAction Stop -Verbose *>> Production.log
} catch {
   Write-Error $_
   Write-Error "Exception: $($_.Exception)"
}

Docu:

You can display SQL Server message output, such as those that result from the SQL PRINT statement, by specifying the Verbose parameter.

Via *>> we redirect and attach all PowerShell streams to a file.

Hope that helps.

answered Jun 8, 2019 at 8:01

Moerwald

11.4k10 gold badges54 silver badges92 bronze badges

1 Comment

Nathan Kamenar Over a year ago

Unfortunately not, I believe that would work but as I said I can’t use Invoke-Sqlcmd because that would require my script the add the snapin to make that cmdlet available which I don’t have permission to do because of tight security where I work. I am trying to find a way to do it using just sqlcmd.

Collectives™ on Stack Overflow

Getting errors from Sqlcmd in PowerShell script

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related