Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- function Invoke-DbUpdateScript {
- [CmdletBinding()]
- param(
- [Parameter(Mandatory = $true, ValueFromPipelineByPropertyName = $true)]
- [string] $Database,
- [Parameter(ValueFromPipelineByPropertyName = $true)]
- [string] $Port = "1433",
- [Parameter(Mandatory = $true, ValueFromPipelineByPropertyName = $true, ParameterSetName = "Script")]
- [string] $Script,
- [Parameter(Mandatory = $true, ValueFromPipelineByPropertyName = $true, ParameterSetName = "Sql")]
- [string] $Sql,
- [Parameter(Mandatory = $true, ValueFromPipelineByPropertyName = $true)]
- [ValidateSet("Dev","Test","Prod")]
- [string] $Environment,
- [Parameter(ValueFromPipelineByPropertyName = $true)]
- [string] $ComputerName = $env:COMPUTERNAME,
- [Parameter(ValueFromPipelineByPropertyName = $true)]
- [System.Management.Automation.PSCredential] $Credential,
- [Parameter(ValueFromPipelineByPropertyName = $true)]
- [System.Management.Automation.Runspaces.AuthenticationMechanism] $Authentication,
- [Parameter(ValueFromPipelineByPropertyName = $true)]
- [switch] $Throw,
- [switch] $SurpressWriteHost
- )
- if($PSCmdlet.ParameterSetName -eq "Script") {
- $path = Get-DbUpdateScriptPath -Database $Database -Script $Script -Environment Staging
- if((Test-Path -Path $path) -eq $false) {
- throw (
- "Invoke-DbUpdateScript: Script '{0}' not found at '{1}'. Please ensure it exists and try again." -f `
- $Script, $path
- )
- }
- $Sql = Get-Content -Path $path -Raw
- }
- $dbhost = Get-DbConnectionString -Database $Database -Port $Port -Environment $Environment
- $scriptBlock = {
- param([string] $dbhost, [string] $database, [string] $sql, [bool] $SurpressWriteHost)
- $tempfile = New-TemporaryFile
- $sql | Set-Content -Path $tempfile -Force
- $errorfile = New-TemporaryFile
- if(-not $SurpressWriteHost) {
- Write-Host @"
- ----------------
- dbhost: $dbhost
- database: $database
- tempfile: $tempfile
- errorfile: $errorfile
- sql:
- $sql
- ----------------
- "@
- }
- $cmd = 'sqlcmd -E -I -S "{0}" -d "{1}" -i "{2}"' -f $dbhost, $database, $tempfile
- #$cmd = 'sqlcmd -E -I -S "{0}" -d "{1}" -Q "{2}"' -f $dbhost, $database, $sql
- $result = invoke-expression "$cmd 2> $errorfile"
- $errorc = Get-Content -Path $errorfile -Raw
- if($errorc.length -gt 0) {
- if($result.length -gt 0) { $result += "`r`n`r`n" }
- $result += $errorc
- }
- Remove-Item -Path $tempfile | Out-Null
- Remove-Item -Path $errorfile | Out-Null
- return $result
- }
- if($Credential -eq $null) {
- if(-not $SurpressWriteHost) {
- Write-Host ("Executing script on {0}. (script path = '{1}')" -f $dbhost, $path)
- }
- $output = Invoke-Command `
- -ScriptBlock $scriptBlock `
- -ArgumentList @($dbhost, $Database, $Sql, $SurpressWriteHost)
- } else {
- if(-not $SurpressWriteHost) {
- Write-Host (
- "Executing script on {0} with credentials for '{1}'. (script path = '{2}')" -f `
- $dbhost, $Credential.UserName, $path
- )
- }
- $output = Invoke-Command `
- -ComputerName $ComputerName -ScriptBlock $scriptBlock `
- -Credential $Credential -Authentication $Authentication `
- -ArgumentList @($dbhost, $Database, $Sql, $SurpressWriteHost)
- }
- ## no error or output to report
- if([string]::IsNullOrWhiteSpace($output)) {
- return $null
- }
- ## Error processing
- $errors = Convert-DbUpdateOutputToErrors -Output $output
- if(@($errors).Count -gt 0) {
- # This is used in debugging locally
- if(-not $SurpressWriteHost) {
- Write-Host @"
- ------------- ERRORS --------------
- "@
- if($output -match "sqlcmd \:") {
- Write-Host @"
- $output
- -----------------------------------
- "@
- }
- }
- if($Throw) {
- $message = [System.Text.StringBuilder]::new(100 * $errors.Count)
- foreach($er in $errors) {
- if($message.Length -gt 0) { $message.AppendLine() }
- $message.Append($er.OriginalErrorMessage)
- }
- throw $message.ToString()
- }
- }
- return $output
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement