Powershell error details

Automated deployment for web applications and databases

Moderators: Mike Upton, justin.caldicott, Sean.newham, csmith, chirayu, DavidSimner, david.conlin

Powershell error details

Postby lee5i3 » Tue Aug 27, 2013 5:29 pm

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

My script looks like this...

Code: Select all
$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: Select all
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
lee5i3
 
Posts: 38
Joined: Fri Jul 16, 2010 9:26 pm

Postby james.billings » Wed Aug 28, 2013 12:34 pm

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 here 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.
james.billings
 
Posts: 1144
Joined: Wed Jun 16, 2010 11:10 am
Location: My desk.

Postby lee5i3 » Wed Aug 28, 2013 5:14 pm

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
lee5i3
 
Posts: 38
Joined: Fri Jul 16, 2010 9:26 pm

Postby lee5i3 » Wed Aug 28, 2013 5:27 pm

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
lee5i3
 
Posts: 38
Joined: Fri Jul 16, 2010 9:26 pm

Postby james.billings » Wed Aug 28, 2013 5:32 pm

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!)
james.billings
 
Posts: 1144
Joined: Wed Jun 16, 2010 11:10 am
Location: My desk.

Postby Mike Upton » Wed Aug 28, 2013 5:38 pm

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

Code: Select all
$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.
Mike Upton
 
Posts: 157
Joined: Wed May 11, 2011 8:04 am
Location: Red Gate

Re:

Postby lee5i3 » Wed Aug 28, 2013 8:31 pm

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: Select all
$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.
lee5i3
 
Posts: 38
Joined: Fri Jul 16, 2010 9:26 pm

Postby james.billings » Wed Aug 28, 2013 8:32 pm

Nice, thanks for letting us know that helped :)
james.billings
 
Posts: 1144
Joined: Wed Jun 16, 2010 11:10 am
Location: My desk.

Postby lee5i3 » Wed Aug 28, 2013 8:49 pm

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: Select all
/variables:Version=$RedGateReleaseNumber


This way, I can look at the database to determine the version that was installed.
lee5i3
 
Posts: 38
Joined: Fri Jul 16, 2010 9:26 pm


Return to Deployment Manager

Who is online

Users browsing this forum: No registered users and 0 guests