sql script execution through powershell always shows success

sagarsagar Posts: 9
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â€

Comments

  • 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.
    Development Lead
    Redgate Software
  • 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
  • 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
    Development Lead
    Redgate Software
  • 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
  • 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 ****.
    Development Lead
    Redgate Software
  • 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â€
  • 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.
    Development Lead
    Redgate Software
  • 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.
    Development Lead
    Redgate Software
  • 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.
  • Thanks a lot Mike.
    We had problem using "Invoke-Sqlcmd" in SQL 2005 versions, eventually figured it out and its working fine :-)
  • 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
    }
Sign In or Register to comment.