Include custom SQL script output in DM's deployment logs

swinghouseswinghouse Posts: 120 Bronze 2
edited November 8, 2013 5:39AM in Deployment Manager PowerShell Scripts
In one of our Deployment Manager (DM) projects we let PostDeploy.ps1 run a customized SQL script like so:
sqlcmd.exe -b -V 1 -s $databaseServer -U $databaseUserName -P $databasePassword -i "RunMigrationScript.sql" -d "$databaseName"

This works, but it would be nice to have DM include the output (SQL Print statements in this case) from the SQL script file in the deployment log. Currently we get:
2013-05-30 15:43:53 DEBUG Looking for PowerShell scripts named PostDeploy.ps1
2013-05-30 15:43:53 INFO Calling PowerShell script: 'C:\ProgramData\Red Gate\DeploymentAgent\Applications\Production.Data\BagheraDataMigration\0.116\PostDeploy.ps1'
2013-05-30 15:49:08 DEBUG Script 'C:\ProgramData\Red Gate\DeploymentAgent\Applications\Production.Data\BagheraDataMigration\0.116\PostDeploy.ps1' completed.
2013-05-30 15:49:08 DEBUG Script output:
2013-05-30 15:49:08 DEBUG
2013-05-30 15:49:08 DEBUG Return code of PowerShell script: 0

No script output in there!

Is it possible to get the script output in the DM deployment log? If so, what do we need to change?

/Mattias
· Share on Twitter

Comments

  • chirayuchirayu Posts: 79
    Hi Mattias,

    Deployment Manager should be capturing the std-out output from powershell scripts.

    Windows powershell puts 'Out-Default' at the end of a pipeline, so what actually gets run is 
    sqlcmd.exe <arguments> | Out-Default

    Out-Default cannot process some objects. It can only handle a special type of formatting object produced by the shell’s formatting subsystem, according to this article:
    http://technet.microsoft.com/en-us/maga ... 13852.aspx

    Would you please run this command 
    sqlcmd.exe <arguments> | Out-Default
    and check that it actually outputs something?

    Thanks!

    Chirayu
    Chirayu Shishodiya
    Software Engineer - Deployment Manager
    Red Gate
    · Share on Twitter
  • swinghouseswinghouse Posts: 120 Bronze 2
    Hi Chirayu,

    Your suggestion worked beautifully!

    After adding
    | Out-Default

    to the sqlmcd call, the script output is indeed captured by Deployment Manager.

    Many thanks!

    /Mattias
    · Share on Twitter
  • chirayuchirayu Posts: 79
    Glad it works! I have moved this topic to Powershell scripts forum.

    Thanks!

    Chirayu
    Chirayu Shishodiya
    Software Engineer - Deployment Manager
    Red Gate
    · Share on Twitter
  • DatabaseJaseDatabaseJase Posts: 9 Bronze 2
    swinghouse wrote:
    In one of our Deployment Manager (DM) projects we let PostDeploy.ps1 run a customized SQL script like so: 
    sqlcmd.exe -b -V 1 -s $databaseServer -U $databaseUserName -P $databasePassword -i "RunMigrationScript.sql" -d "$databaseName"
    Hello,

    Firstly thank you for this topic as it has helped me get starting with creating a custom SQL deployment script using PowerShell. I'm just curious why you wouldn't use the more native command:
    Invoke-Sqlcmd -InputFile "RunMigrationScript.sql" -ServerInstance $databaseServer -Database $databaseName -Username $databaseUserName -Password $databasePassword  | Out-Default

    You would need to add SQL PowerShell Commandlets:
    Try {Add-PSSnapin SqlServerCmdletSnapin100} Catch {"SqlServerCmdletSnapin100 already loaded"}
Try {Add-PSSnapin SqlServerProviderSnapin100} Catch {"SqlServerProviderSnapin100 already loaded"}
    I've found the native Invoke-Sqlcmd to be much more adaptable as you can return result sets which can processed through ForEach if required.

    Just curious and also thought this might help someone else reading this forum with an alternative option.
    · Share on Twitter
  • DatabaseJaseDatabaseJase Posts: 9 Bronze 2
    I just ran into an issue that I'd known about but forgotten. There is an issue with the Invoke-Sqlcmd in that the documentation states that unless a specific query timeout is specified then the command does not timeout. In fact it uses the default of 30 seconds so remember to always include the -QueryTimeout option.

    This brings me onto the fact that this error:
    Invoke-Sqlcmd : Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
At C:\ProgramData\Red Gate\DeploymentAgent\Applications\Test\2.7.20131001.1\Deploy.ps1:94 char:30
+                 Invoke-Sqlcmd <<<<  -InputFile $UpgradeScriptPath -ServerInstance $RedGateDatabaseServer -Database $RedGateDatabaseName -Username $RedGateDatabaseUserName -Password $RedGateDatabasePassword  | Out-Default
    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
    + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
    Did not cause the deployment to fail. Should I be doing something specific to capture errors? It is in a Try/Catch block so I'd have expected it to fail completely. It was only because I was checking the logs I picked it up.
    · Share on Twitter
  • Mike UMike U Posts: 316 Gold 1
    The deployment actually would fail if you didn't have the call inside a try-catch block; any unhandled exception will cause a deployment failure. You're catching it in your PowerShell script, and therefore stopping the Deployment Manager runner seeing the exception at all.
    Development Lead
    Redgate Software
    · Share on Twitter
  • DatabaseJaseDatabaseJase Posts: 9 Bronze 2
    Mike Upton wrote:
    The deployment actually would fail if you didn't have the call inside a try-catch block
    So the irony here is that I'm trying to be too clever and in fact should just trust Deployment Manager to essentially do the try-catch to ensure that any errors cause the deployment to fail?

    Thank you for the information. I'll go and remove those try-catch blocks...
    · Share on Twitter
Sign In or Register to comment.