Red Gate forums :: View topic - Powershell error details
Return to www.red-gate.com RSS Feed Available

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

Powershell error details

Search in Deployment Manager forum
Post new topic   Reply to topic
Jump to:  
Author Message
lee5i3



Joined: 16 Jul 2010
Posts: 38

PostPosted: Tue Aug 27, 2013 5:29 pm    Post subject: Powershell error details Reply with quote

How can you get more details from a failed powershell script?

My script looks like this...

Code:

$path = Split-Path -Parent $MyInvocation.MyCommand.Path

& "C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe" /SourceFile:$path"\bin\Release\Project.dacpac" /Action:Publish /TargetConnectionString:$ConnectionString /p:CreateNewDatabase=$CreateNewDatabase /p:DeployDatabaseInSingleUserMode=True /p:GenerateSmartDefaults=True | Write-Host


My database is failing to install.. but it is only showing this...

Code:
2013-08-27 14:30:00 DEBUG  Looking for PowerShell scripts named Deploy.ps1
2013-08-27 14:30:00 INFO   Calling PowerShell script: 'C:\ProgramData\Red Gate\DeploymentAgent\Applications\Development\Database\13.08.27.72574\Deploy.ps1'
2013-08-27 14:31:06 DEBUG  Script 'C:\ProgramData\Red Gate\DeploymentAgent\Applications\Development\Database\13.08.27.72574\Deploy.ps1' completed.
2013-08-27 14:31:06 DEBUG  Script output:
2013-08-27 14:31:06 DEBUG  Publishing to database 'Project' on server '(local)\SQLEXPRESS'.
Initializing deployment (Start)
Initializing deployment (Complete)
Analyzing deployment plan (Start)
Analyzing deployment plan (Complete)
Updating database (Start)
Creating Project...
Nonqualified transactions are being rolled back. Estimated rollback completion: 0%.
Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.
Creating [dbo].[Table1]...
Creating [dbo].[Table1].[IX_Date]...
Creating [dbo].[Table2]...
.............
.............
.............
.............
.............
Creating [dbo].[Proc1]...
Creating [dbo].[Proc2]...
Creating [dbo].[Proc3]...
The statement has been terminated.
The statement has been terminated.
The statement has been terminated.
The statement has been terminated.
The statement has been terminated.
The statement has been terminated.
The statement has been terminated.
An error occurred while the batch was being executed.
Updating database (Failed)

2013-08-27 14:31:06 DEBUG  Return code of PowerShell script: 1
2013-08-27 14:31:06 ERROR  PowerShell script 'C:\ProgramData\Red Gate\DeploymentAgent\Applications\Development\Database\13.08.27.72574\Deploy.ps1' returned non-zero exit code: 1. Deployment terminated.


This is forcing me log into the server and manually run the script to see what the actual error is, is there a way to show the error in the log rather than just an error code
Back to top
View user's profile Send private message
james.billings



Joined: 16 Jun 2010
Posts: 1116
Location: My desk.

PostPosted: Wed Aug 28, 2013 12:34 pm    Post subject: Reply with quote

I don't think this is a powershell limitation as such- the output from the SQLPackage command is being displayed as you'd expect, so really it's that which is not supplying the output you need to troubleshoot. If you run the SQLPackage command manually, does it give better feedback? If so, it should be possible to capture that.

The only option affecting output I could see (checking [url=http://msdn.microsoft.com/en-us/library/hh550080(v=vs.103).aspx]here[/url] was a /quiet switch which can be true or false to suppress output. The default is off, which is what you have, and there doesn't seem to be an option to increase the detail that is output.
Back to top
View user's profile Send private message
lee5i3



Joined: 16 Jul 2010
Posts: 38

PostPosted: Wed Aug 28, 2013 5:14 pm    Post subject: Reply with quote

I did not run the ps1 manually but if I ran the command inside the ps1 manually, I am getting a lot more information about the error, ended up being a foreign key constraint that prevented it from running the scripts
Back to top
View user's profile Send private message
lee5i3



Joined: 16 Jul 2010
Posts: 38

PostPosted: Wed Aug 28, 2013 5:27 pm    Post subject: Reply with quote

I just ran it manually to see what it would show... this is how it looks

http://postimg.org/image/xxv4qgral/


However, Deployment Manager logs just say deployment terminated, I get no indication of what went wrong
Back to top
View user's profile Send private message
james.billings



Joined: 16 Jun 2010
Posts: 1116
Location: My desk.

PostPosted: Wed Aug 28, 2013 5:32 pm    Post subject: Reply with quote

My guess would be the full output is still going to stderr and not picked up by the write-host or similar, but I'll need to have a play around to see if I can figure that out (i'm no Powershell genuis!)
Back to top
View user's profile Send private message
Mike Upton



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

PostPosted: Wed Aug 28, 2013 5:38 pm    Post subject: Reply with quote

That's certainly a possibility. One thing to try would be redirecting stderr to stdout by adding 2>&1 to the command line:

Code:
$path = Split-Path -Parent $MyInvocation.MyCommand.Path

& "C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe" /SourceFile:$path"\bin\Release\Project.dacpac" /Action:Publish /TargetConnectionString:$ConnectionString /p:CreateNewDatabase=$CreateNewDatabase /p:DeployDatabaseInSingleUserMode=True /p:GenerateSmartDefaults=True 2>&1 | Write-Host


I don't know if this will work, but it's worth a try.
_________________
Mike Upton

Software Engineer
Red Gate Software Ltd.
Back to top
View user's profile Send private message
lee5i3



Joined: 16 Jul 2010
Posts: 38

PostPosted: Wed Aug 28, 2013 8:31 pm    Post subject: Re: Reply with quote

That actually works.. PERFECT!

Shows the actual SQL exception that prevented problem.. THANKS


Mike Upton wrote:
That's certainly a possibility. One thing to try would be redirecting stderr to stdout by adding 2>&1 to the command line:

Code:
$path = Split-Path -Parent $MyInvocation.MyCommand.Path

& "C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe" /SourceFile:$path"\bin\Release\Project.dacpac" /Action:Publish /TargetConnectionString:$ConnectionString /p:CreateNewDatabase=$CreateNewDatabase /p:DeployDatabaseInSingleUserMode=True /p:GenerateSmartDefaults=True 2>&1 | Write-Host


I don't know if this will work, but it's worth a try.
Back to top
View user's profile Send private message
james.billings



Joined: 16 Jun 2010
Posts: 1116
Location: My desk.

PostPosted: Wed Aug 28, 2013 8:32 pm    Post subject: Reply with quote

Nice, thanks for letting us know that helped Smile
Back to top
View user's profile Send private message
lee5i3



Joined: 16 Jul 2010
Posts: 38

PostPosted: Wed Aug 28, 2013 8:49 pm    Post subject: Reply with quote

One little tip as well for those using SQL Data Tools..

I've also added an Extended Property to my database called Version..

and in the argument list for SQLPackager.exe, I've added..

Code:
/variables:Version=$RedGateReleaseNumber


This way, I can look at the database to determine the version that was installed.
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