sql script execution through powershell always shows success

Share your PowerShell scripts for Deployment Manager

Moderators: Mike Upton, justin.caldicott, csmith, chirayu, DavidSimner

sql script execution through powershell always shows success

Postby sagar » Wed Apr 02, 2014 9:25 am

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â€
sagar
 
Posts: 7
Joined: Tue Feb 18, 2014 1:08 pm

Postby Mike Upton » Mon Apr 07, 2014 7:49 am

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.
Mike Upton

Software Engineer
Red Gate Software Ltd.
Mike Upton
 
Posts: 157
Joined: Wed May 11, 2011 8:04 am
Location: Red Gate

Re:

Postby sagar » Mon Apr 07, 2014 8:00 am

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
sagar
 
Posts: 7
Joined: Tue Feb 18, 2014 1:08 pm

Postby Mike Upton » Mon Apr 07, 2014 8:51 am

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
Mike Upton

Software Engineer
Red Gate Software Ltd.
Mike Upton
 
Posts: 157
Joined: Wed May 11, 2011 8:04 am
Location: Red Gate

Re:

Postby sagar » Mon Apr 07, 2014 9:31 am

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
sagar
 
Posts: 7
Joined: Tue Feb 18, 2014 1:08 pm

Postby Mike Upton » Mon Apr 07, 2014 9:46 am

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
Code: Select all
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 ****.
Mike Upton

Software Engineer
Red Gate Software Ltd.
Mike Upton
 
Posts: 157
Joined: Wed May 11, 2011 8:04 am
Location: Red Gate

Re:

Postby sagar » Mon Apr 07, 2014 10:10 am

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
Code: Select all
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â€
sagar
 
Posts: 7
Joined: Tue Feb 18, 2014 1:08 pm

Postby Mike Upton » Mon Apr 07, 2014 10:36 am

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 Upton

Software Engineer
Red Gate Software Ltd.
Mike Upton
 
Posts: 157
Joined: Wed May 11, 2011 8:04 am
Location: Red Gate

Postby Mike Upton » Mon Apr 07, 2014 1:48 pm

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.
Mike Upton

Software Engineer
Red Gate Software Ltd.
Mike Upton
 
Posts: 157
Joined: Wed May 11, 2011 8:04 am
Location: Red Gate

Re:

Postby sagar » Mon Apr 07, 2014 2:45 pm

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
 
Posts: 7
Joined: Tue Feb 18, 2014 1:08 pm

Thanks a lot Mike.It worked.

Postby sagar » Mon Apr 14, 2014 6:48 am

Thanks a lot Mike.
We had problem using "Invoke-Sqlcmd" in SQL 2005 versions, eventually figured it out and its working fine :-)
sagar
 
Posts: 7
Joined: Tue Feb 18, 2014 1:08 pm

Stop Deployment manager executing on sql custom script fail

Postby akhilgarg81 » Mon Aug 04, 2014 12:10 pm

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
}
akhilgarg81
 
Posts: 6
Joined: Thu Jul 24, 2014 9:52 am


Return to Deployment Manager PowerShell Scripts

Who is online

Users browsing this forum: No registered users and 0 guests

cron