sql script execution through powershell always shows success

sagar · April 2, 2014 4:25AM

In one of our Deployment Manager (DM) projects we let Deploy.ps1 run a customized SQL script:

Sql script is basically a insert statement into a table where the table is not existing .
In the below sql code the tablet Ã¢â‚¬Å“testÃ¢â‚¬

Mike U · April 7, 2014 2:49AM

Deployment Manager does normally fail deployments when a PowerShell exception is thrown. Can you post the entire contents of the PowerShell script to this forum? It could be that something else in the script is changing the error handling behaviour.

sagar · April 7, 2014 3:00AM

Mike Upton wrote:

Deployment Manager does normally fail deployments when a PowerShell exception is thrown. Can you post the entire contents of the PowerShell script to this forum? It could be that something else in the script is changing the error handling behaviour.

Hi Mike ,

Thanks for the reply . Powershell script mentioned above is the complete powershell script. In this ,we are only executing the SQL script from powershell.

Thanks,
Sagar

Mike U · April 7, 2014 3:51AM

Hi Sagar,

I'm afraid that the error message clearly indicates that the exception occurred at line 16 of Deploy.ps1, so there must have been other lines before the call to Invoke-Sqlcmd:

Invoke-Sqlcmd : Invalid object name 'test'.
At C:\ProgramData\Red Gate\DeploymentAgent\Applications\Development\testy\1.0.0.9\Deploy.ps1:16 char:15

sagar · April 7, 2014 4:31AM

Mike Upton wrote:

Hi Sagar,

I'm afraid that the error message clearly indicates that the exception occurred at line 16 of Deploy.ps1, so there must have been other lines before the call to Invoke-Sqlcmd:

Invoke-Sqlcmd : Invalid object name 'test'.
At C:\ProgramData\Red Gate\DeploymentAgent\Applications\Development\testy\1.0.0.9\Deploy.ps1:16 char:15

Hi Mike ,

We understand that there is a error executing sql script, reason being the sql script is trying to insert a row in the table named "Test" , which is not existing in the database. SQL script errors out alright , but in the deployment manager it shows as successfully deployed . But if we look into the deployment logs we can see the error saying "Invalid object 'Test'", Our issue is deployment manager still shows the deployment as successful even though there was error executing the the sql script instead deployment should have shown as failure.

Thanks,
Sagar

Mike U · April 7, 2014 4:46AM

Hi Sagar,

I understand your problem. However, my point is that if you gave Deployment Manager a Deploy.ps1 script that contained only the single line

Invoke-Sqlcmd -InputFile $sqlFilePath -ServerInstance $databaseServer -Database $databaseName -Username $databaseUserName -Password $databasePassword  | Out-Default

then Deployment Manager would fail the deployment if executing the T-SQL failed.

The only way that this would not occur is if you have something else in your PowerShell script that is affecting the error handling; for example, if the call to Invoke-Sqlcmd is surrounded by a try-catch that swallows the exception, or if you have changed the ErrorActionPreference to SilentlyContinue.

This is the reason that I'm asking for the rest of the content of the Deploy.ps1 file, even if you don't think that the other lines are relevant. If you have sensitive information in your Deploy.ps1 (e.g. passwords) feel free to replace those bits with ****.

sagar · April 7, 2014 5:10AM

Mike Upton wrote:
Hi Sagar,

I understand your problem. However, my point is that if you gave Deployment Manager a Deploy.ps1 script that contained only the single line
Invoke-Sqlcmd -InputFile $sqlFilePath -ServerInstance $databaseServer -Database $databaseName -Username $databaseUserName -Password $databasePassword  | Out-Default
then Deployment Manager would fail the deployment if executing the T-SQL failed.

The only way that this would not occur is if you have something else in your PowerShell script that is affecting the error handling; for example, if the call to Invoke-Sqlcmd is surrounded by a try-catch that swallows the exception, or if you have changed the ErrorActionPreference to SilentlyContinue.

This is the reason that I'm asking for the rest of the content of the Deploy.ps1 file, even if you don't think that the other lines are relevant. If you have sensitive information in your Deploy.ps1 (e.g. passwords) feel free to replace those bits with ****.

Hi Mike,
Please find the complete contents of Deploy.ps1

[code]

echo Ã¢â‚¬Å“Executing $sqlFilePathÃ¢â‚¬

Mike U · April 7, 2014 5:36AM

Thanks for that. I'll use that PowerShell script together with your test T-SQL to try to reproduce the problem here. I'm in a planning meeting for the next couple of hours, so I'll get back to you after that.

Mike U · April 7, 2014 8:48AM

Hi Sagar,

I've reproduced the problem locally. The problem is that Invoke-Sqlcmd by default treats most errors as non-terminating, so the PowerShell script execution continues normally rather than throwing an exception.

You need to change the error behaviour so that an error causes the script to fail. The simplest way to do this is to add -ErrorAction Stop to your Invoke-Sqlcmd line. So, the full line would be

Invoke-Sqlcmd -InputFile $sqlFilePath -ServerInstance $databaseServer -Database $databaseName -Username $databaseUserName -Password $databasePassword -ErrorAction Stop | Out-Default

You could also set $ErrorActionPreference = "Stop", or check $? to get a boolean indicating whether the last operation succeeded, or look at the contents of the $error array.

This blog post has some useful detail on PowerShell error handling. The important thing to note when running any PowerShell within Deployment Manager is that the deployment will fail if an exception reaches the Deployment Manager powershell runner, or if the script returns a non-zero value. It will not fail simply because output is written to the error stream.

sagar · April 7, 2014 9:45AM

Mike Upton wrote:

Hi Sagar,

I've reproduced the problem locally. The problem is that Invoke-Sqlcmd by default treats most errors as non-terminating, so the PowerShell script execution continues normally rather than throwing an exception.

You need to change the error behaviour so that an error causes the script to fail. The simplest way to do this is to add -ErrorAction Stop to your Invoke-Sqlcmd line. So, the full line would be

Invoke-Sqlcmd -InputFile $sqlFilePath -ServerInstance $databaseServer -Database $databaseName -Username $databaseUserName -Password $databasePassword -ErrorAction Stop | Out-Default

You could also set $ErrorActionPreference = "Stop", or check $? to get a boolean indicating whether the last operation succeeded, or look at the contents of the $error array.

This blog post has some useful detail on PowerShell error handling. The important thing to note when running any PowerShell within Deployment Manager is that the deployment will fail if an exception reaches the Deployment Manager powershell runner, or if the script returns a non-zero value. It will not fail simply because output is written to the error stream.

Thanks for the reply Mike, We will give it a try in our environment and let you know.

sagar · April 14, 2014 1:48AM

Thanks a lot Mike.
We had problem using "Invoke-Sqlcmd" in SQL 2005 versions, eventually figured it out and its working fine :-)

akhilgarg81 · August 4, 2014 7:10AM

Hi Mike

I am also facing same issue. I have tried using "-ErrorAction Stop". Using this i script execution has stopped but sqlcmd is returing 0 and deployment manager is showing successfull deployment. I tried by returning 1 on fail command but still no changes.

I want deployment manager to show deployment fail when sql script is having any issue.

This is my powershell script.

Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100
Invoke-Sqlcmd -InputFile E:\CustomScripts\ErrorMsg.sql -ServerInstance USPVL8K01-Dev.rdigest.com -Database Main_Bk -Username sdbdeploy -Password BuildUseR@123
if(!$?) {
Write-Host "ExceptionMessage:" $error[0].Exception.Message
Write-Host "Target Object: " $error[0].TargetObject
Write-Host "Category Info: " $error[0].CategoryInfo
Write-Host "ErrorID: " $Error[0].FullyQualifiedErrorId
return 1
}

Invoke-Sqlcmd -InputFile E:\CustomScripts\Poll.sql -ServerInstance USPVL8K01-Dev.rdigest.com -Database Main_Bk -Username sdbdeploy -Password BuildUseR@123
if(!$?) {
Write-Host "ExceptionMessage:" $error[0].Exception.Message
Write-Host "Target Object: " $error[0].TargetObject
Write-Host "Category Info: " $error[0].CategoryInfo
Write-Host "ErrorID: " $Error[0].FullyQualifiedErrorId
return 1
}

sql script execution through powershell always shows success

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk