Powershell error details

lee5i3lee5i3 Posts: 48
edited August 28, 2013 3:49PM in Deployment Manager
How can you get more details from a failed powershell script?

My script looks like this...
$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...
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
· Share on Twitter

Comments

  • James BJames B Posts: 1,124 Silver 4
    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.
    Systems Software Engineer

    Redgate Software

    · Share on Twitter
  • lee5i3lee5i3 Posts: 48
    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
    · Share on Twitter
  • lee5i3lee5i3 Posts: 48
    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
    · Share on Twitter
  • James BJames B Posts: 1,124 Silver 4
    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!)
    Systems Software Engineer

    Redgate Software

    · Share on Twitter
  • Mike UMike U Posts: 316 Gold 1
    That's certainly a possibility. One thing to try would be redirecting stderr to stdout by adding 2>&1 to the command line:
    $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.
    Development Lead
    Redgate Software
    · Share on Twitter
  • lee5i3lee5i3 Posts: 48
    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:
    $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.
    · Share on Twitter
  • James BJames B Posts: 1,124 Silver 4
    Nice, thanks for letting us know that helped :)
    Systems Software Engineer

    Redgate Software

    · Share on Twitter
  • lee5i3lee5i3 Posts: 48
    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..
    /variables:Version=$RedGateReleaseNumber

    This way, I can look at the database to determine the version that was installed.
    · Share on Twitter
Sign In or Register to comment.