Red Gate forums :: View topic - sql script execution through powershell always shows success
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation

Deployment Manager PowerShell Scripts forum

sql script execution through powershell always shows success

Search in Deployment Manager PowerShell Scripts forum
Post new topic   Reply to topic
Jump to:  
Author Message
sagar



Joined: 18 Feb 2014
Posts: 7

PostPosted: Wed Apr 02, 2014 9:25 am    Post subject: sql script execution through powershell always shows success Reply with quote

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” is not existing in the database.(We did this intentionally to see how powershell with deployment manager will handle errors)

SQL Code :
Code:

insert into test values ('test')


Powershell:
Code:
sqlcmd.exe -S $databaseServer -U $databaseUserName -P $databasePassword -i $sqlFilePath -d $databaseName | Out-Default


But the deployment manager shows as successfull deployment.

Deployment manager log :

Executing powershell script C:\ProgramData\Red Gate\DeploymentAgent\Applications\Development\testy\1.0.0.11\Deploy.ps1...
Msg 208, Level 16, State 1, Server SSRS, Line 1
Invalid object name 'test'.
2014-04-02 13:21:32 +05:30 DEBUG Return code of PowerShell script: 0



We did try with Invoke-Sqlcmd as well but no luck .
Poweshell :
Code:
Invoke-Sqlcmd -InputFile $sqlFilePath -ServerInstance $databaseServer -Database $databaseName -Username $databaseUserName -Password $databasePassword  | Out-Default


Deployment manager log :
Invoke-Sqlcmd : Invalid object name 'test'.
At C:\ProgramData\Red Gate\DeploymentAgent\Applications\Development\testy\1.0.0.9\Deploy.ps1:16 char:15
+ Invoke-Sqlcmd <<<< "insert into test values ('test')" -ServerInstance $databaseServer -Database $databaseName -Username $databaseUserName -Password $databasePassword | Out-Default
+ CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
+ FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand


Is there a way that we can catch this error so that deployment manger does not show this as successful deployment as the execution of the sql script resulted in error ?

Thanks,
Sagar
Back to top
View user's profile Send private message
Mike Upton



Joined: 11 May 2011
Posts: 135
Location: Red Gate

PostPosted: Mon Apr 07, 2014 7:49 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
sagar



Joined: 18 Feb 2014
Posts: 7

PostPosted: Mon Apr 07, 2014 8:00 am    Post subject: Re: Reply with quote

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
Back to top
View user's profile Send private message
Mike Upton



Joined: 11 May 2011
Posts: 135
Location: Red Gate

PostPosted: Mon Apr 07, 2014 8:51 am    Post subject: Reply with quote

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:

Quote:
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.
Back to top
View user's profile Send private message
sagar



Joined: 18 Feb 2014
Posts: 7

PostPosted: Mon Apr 07, 2014 9:31 am    Post subject: Re: Reply with quote

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:

Quote:
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
Back to top
View user's profile Send private message
Mike Upton



Joined: 11 May 2011
Posts: 135
Location: Red Gate

PostPosted: Mon Apr 07, 2014 9:46 am    Post subject: Reply with quote

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:
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.
Back to top
View user's profile Send private message
sagar



Joined: 18 Feb 2014
Posts: 7

PostPosted: Mon Apr 07, 2014 10:10 am    Post subject: Re: Reply with quote

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:
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”

Import-Module “sqlps” -DisableNameChecking

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

echo "Completed executing $sqlFilePath"


Thanks,
Sagar
Back to top
View user's profile Send private message
Mike Upton



Joined: 11 May 2011
Posts: 135
Location: Red Gate

PostPosted: Mon Apr 07, 2014 10:36 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Mike Upton



Joined: 11 May 2011
Posts: 135
Location: Red Gate

PostPosted: Mon Apr 07, 2014 1:48 pm    Post subject: Reply with quote

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
Quote:
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.
Back to top
View user's profile Send private message
sagar



Joined: 18 Feb 2014
Posts: 7

PostPosted: Mon Apr 07, 2014 2:45 pm    Post subject: Re: Reply with quote

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
Quote:
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.
Back to top
View user's profile Send private message
sagar



Joined: 18 Feb 2014
Posts: 7

PostPosted: Mon Apr 14, 2014 6:48 am    Post subject: Thanks a lot Mike.It worked. Reply with quote

Thanks a lot Mike.
We had problem using "Invoke-Sqlcmd" in SQL 2005 versions, eventually figured it out and its working fine Smile
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group