Include custom SQL script output in DM's deployment logs

Share your PowerShell scripts for Deployment Manager

Moderators: Mike Upton, justin.caldicott, csmith, chirayu, DavidSimner

Include custom SQL script output in DM's deployment logs

Postby swinghouse » Fri May 31, 2013 3:57 pm

In one of our Deployment Manager (DM) projects we let PostDeploy.ps1 run a customized SQL script like so:

Code: Select all
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
swinghouse
 
Posts: 99
Joined: Mon Sep 12, 2011 10:51 am

Postby chirayu » Mon Jun 03, 2013 2:03 pm

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
Code: Select all
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
Code: Select all
sqlcmd.exe <arguments> | Out-Default

and check that it actually outputs something?

Thanks!

Chirayu
Chirayu Shishodiya
Software Engineer - Deployment Manager
Red Gate
chirayu
 
Posts: 65
Joined: Mon Sep 17, 2012 5:48 pm

Postby swinghouse » Mon Jun 03, 2013 11:04 pm

Hi Chirayu,

Your suggestion worked beautifully!

After adding

Code: Select all
| Out-Default


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

Many thanks!

/Mattias
swinghouse
 
Posts: 99
Joined: Mon Sep 12, 2011 10:51 am

Postby chirayu » Tue Jun 04, 2013 8:56 am

Glad it works! I have moved this topic to Powershell scripts forum.

Thanks!

Chirayu
Chirayu Shishodiya
Software Engineer - Deployment Manager
Red Gate
chirayu
 
Posts: 65
Joined: Mon Sep 17, 2012 5:48 pm

Re: Include custom SQL script output in DM's deployment logs

Postby DatabaseJase » Fri Sep 20, 2013 11:03 am

swinghouse wrote:In one of our Deployment Manager (DM) projects we let PostDeploy.ps1 run a customized SQL script like so:
Code: Select all
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:

Code: Select all
Invoke-Sqlcmd -InputFile "RunMigrationScript.sql" -ServerInstance $databaseServer -Database $databaseName -Username $databaseUserName -Password $databasePassword  | Out-Default


You would need to add SQL PowerShell Commandlets:

Code: Select all
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.
DatabaseJase
 
Posts: 7
Joined: Fri Sep 20, 2013 10:53 am
Location: Northamptonshire

Error Capture?

Postby DatabaseJase » Fri Oct 04, 2013 9:42 am

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:

Code: Select all
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.
DatabaseJase
 
Posts: 7
Joined: Fri Sep 20, 2013 10:53 am
Location: Northamptonshire

Postby Mike Upton » Wed Nov 06, 2013 11:35 am

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.
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 DatabaseJase » Fri Nov 08, 2013 11:39 am

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...
DatabaseJase
 
Posts: 7
Joined: Fri Sep 20, 2013 10:53 am
Location: Northamptonshire


Return to Deployment Manager PowerShell Scripts

Who is online

Users browsing this forum: No registered users and 0 guests