Red Gate forums :: View topic - Include custom SQL script output in DM's deployment logs
Return to www.red-gate.com RSS Feed Available

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

Deployment Manager PowerShell Scripts forum

Include custom SQL script output in DM's deployment logs

Search in Deployment Manager PowerShell Scripts forum
Post new topic   Reply to topic
Jump to:  
Author Message
swinghouse



Joined: 12 Sep 2011
Posts: 99

PostPosted: Fri May 31, 2013 3:57 pm    Post subject: Include custom SQL script output in DM's deployment logs Reply with quote

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

Code:
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:

Quote:
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
Back to top
View user's profile Send private message
chirayu



Joined: 17 Sep 2012
Posts: 65

PostPosted: Mon Jun 03, 2013 2:03 pm    Post subject: Reply with quote

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:
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/magazine/gg213852.aspx

Would you please run this command
Code:
sqlcmd.exe <arguments> | Out-Default

and check that it actually outputs something?

Thanks!

Chirayu
_________________
Chirayu Shishodiya
Software Engineer - Deployment Manager
Red Gate
Back to top
View user's profile Send private message
swinghouse



Joined: 12 Sep 2011
Posts: 99

PostPosted: Mon Jun 03, 2013 11:04 pm    Post subject: Reply with quote

Hi Chirayu,

Your suggestion worked beautifully!

After adding

Code:
| Out-Default


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

Many thanks!

/Mattias
Back to top
View user's profile Send private message
chirayu



Joined: 17 Sep 2012
Posts: 65

PostPosted: Tue Jun 04, 2013 8:56 am    Post subject: Reply with quote

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

Thanks!

Chirayu
_________________
Chirayu Shishodiya
Software Engineer - Deployment Manager
Red Gate
Back to top
View user's profile Send private message
DatabaseJase



Joined: 20 Sep 2013
Posts: 7
Location: Northamptonshire

PostPosted: Fri Sep 20, 2013 11:03 am    Post subject: Re: Include custom SQL script output in DM's deployment logs Reply with quote

swinghouse wrote:
In one of our Deployment Manager (DM) projects we let PostDeploy.ps1 run a customized SQL script like so:
Code:
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:
Invoke-Sqlcmd -InputFile "RunMigrationScript.sql" -ServerInstance $databaseServer -Database $databaseName -Username $databaseUserName -Password $databasePassword  | Out-Default


You would need to add SQL PowerShell Commandlets:

Code:
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.
Back to top
View user's profile Send private message
DatabaseJase



Joined: 20 Sep 2013
Posts: 7
Location: Northamptonshire

PostPosted: Fri Oct 04, 2013 9:42 am    Post subject: Error Capture? Reply with quote

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:
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.
Back to top
View user's profile Send private message
Mike Upton



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

PostPosted: Wed Nov 06, 2013 11:35 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
DatabaseJase



Joined: 20 Sep 2013
Posts: 7
Location: Northamptonshire

PostPosted: Fri Nov 08, 2013 11:39 am    Post subject: Re: Reply with quote

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...
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