SHARE
TWEET

Untitled

a guest Jun 27th, 2019 66 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. function Invoke-DbUpdateScript {
  2.     [CmdletBinding()]
  3.     param(
  4.         [Parameter(Mandatory = $true, ValueFromPipelineByPropertyName = $true)]
  5.         [string] $Database,
  6.         [Parameter(ValueFromPipelineByPropertyName = $true)]
  7.         [string] $Port = "1433",
  8.         [Parameter(Mandatory = $true, ValueFromPipelineByPropertyName = $true, ParameterSetName = "Script")]
  9.         [string] $Script,
  10.         [Parameter(Mandatory = $true, ValueFromPipelineByPropertyName = $true, ParameterSetName = "Sql")]
  11.         [string] $Sql,
  12.         [Parameter(Mandatory = $true, ValueFromPipelineByPropertyName = $true)]
  13.         [ValidateSet("Dev","Test","Prod")]
  14.         [string] $Environment,
  15.         [Parameter(ValueFromPipelineByPropertyName = $true)]
  16.         [string] $ComputerName = $env:COMPUTERNAME,
  17.         [Parameter(ValueFromPipelineByPropertyName = $true)]
  18.         [System.Management.Automation.PSCredential] $Credential,
  19.         [Parameter(ValueFromPipelineByPropertyName = $true)]
  20.         [System.Management.Automation.Runspaces.AuthenticationMechanism] $Authentication,
  21.         [Parameter(ValueFromPipelineByPropertyName = $true)]
  22.         [switch] $Throw,
  23.         [switch] $SurpressWriteHost
  24.     )
  25.  
  26.     if($PSCmdlet.ParameterSetName -eq "Script") {
  27.         $path = Get-DbUpdateScriptPath -Database $Database -Script $Script -Environment Staging
  28.  
  29.         if((Test-Path -Path $path) -eq $false) {
  30.             throw (
  31.                 "Invoke-DbUpdateScript: Script '{0}' not found at '{1}'. Please ensure it exists and try again." -f `
  32.                 $Script, $path
  33.             )
  34.         }
  35.  
  36.         $Sql = Get-Content -Path $path -Raw
  37.     }
  38.    
  39.     $dbhost = Get-DbConnectionString -Database $Database -Port $Port -Environment $Environment
  40.  
  41.     $scriptBlock = {
  42.         param([string] $dbhost, [string] $database, [string] $sql, [bool] $SurpressWriteHost)
  43.  
  44.         $tempfile = New-TemporaryFile
  45.         $sql | Set-Content -Path $tempfile -Force
  46.  
  47.         $errorfile = New-TemporaryFile
  48.  
  49.         if(-not $SurpressWriteHost) {
  50.             Write-Host @"
  51.  
  52. ----------------
  53. dbhost: $dbhost
  54. database: $database
  55. tempfile: $tempfile
  56. errorfile: $errorfile
  57. sql:
  58. $sql
  59. ----------------
  60.  
  61. "@
  62.         }
  63.  
  64.         $cmd = 'sqlcmd -E -I -S "{0}" -d "{1}" -i "{2}"' -f $dbhost, $database, $tempfile
  65.         #$cmd = 'sqlcmd -E -I -S "{0}" -d "{1}" -Q "{2}"' -f $dbhost, $database, $sql
  66.  
  67.         $result = invoke-expression "$cmd 2> $errorfile"
  68.  
  69.         $errorc = Get-Content -Path $errorfile -Raw
  70.         if($errorc.length -gt 0) {
  71.             if($result.length -gt 0) { $result += "`r`n`r`n" }
  72.             $result += $errorc
  73.         }
  74.  
  75.         Remove-Item -Path $tempfile | Out-Null
  76.         Remove-Item -Path $errorfile | Out-Null
  77.  
  78.         return $result
  79.     }
  80.  
  81.     if($Credential -eq $null) {
  82.         if(-not $SurpressWriteHost) {
  83.             Write-Host ("Executing script on {0}. (script path = '{1}')" -f $dbhost, $path)
  84.         }
  85.  
  86.         $output = Invoke-Command `
  87.                     -ScriptBlock $scriptBlock `
  88.                     -ArgumentList @($dbhost, $Database, $Sql, $SurpressWriteHost)
  89.  
  90.     } else {
  91.         if(-not $SurpressWriteHost) {
  92.             Write-Host (
  93.                 "Executing script on {0} with credentials for '{1}'. (script path = '{2}')" -f `
  94.                 $dbhost, $Credential.UserName, $path
  95.             )
  96.         }
  97.        
  98.         $output = Invoke-Command `
  99.                     -ComputerName $ComputerName -ScriptBlock $scriptBlock `
  100.                     -Credential $Credential -Authentication $Authentication `
  101.                     -ArgumentList @($dbhost, $Database, $Sql, $SurpressWriteHost)
  102.     }
  103.    
  104.  
  105.     ## no error or output to report
  106.    
  107.     if([string]::IsNullOrWhiteSpace($output)) {
  108.         return $null
  109.     }
  110.  
  111.  
  112.     ## Error processing
  113.  
  114.     $errors = Convert-DbUpdateOutputToErrors -Output $output
  115.  
  116.     if(@($errors).Count -gt 0) {
  117.         # This is used in debugging locally
  118.         if(-not $SurpressWriteHost) {
  119.             Write-Host @"
  120.  
  121. ------------- ERRORS --------------
  122. "@
  123.  
  124.             if($output -match "sqlcmd \:") {
  125.                 Write-Host @"
  126. $output
  127. -----------------------------------
  128.  
  129. "@
  130.             }
  131.         }
  132.  
  133.         if($Throw) {
  134.             $message = [System.Text.StringBuilder]::new(100 * $errors.Count)
  135.             foreach($er in $errors) {
  136.                 if($message.Length -gt 0) { $message.AppendLine() }
  137.                 $message.Append($er.OriginalErrorMessage)
  138.             }
  139.             throw $message.ToString()
  140.         }
  141.     }
  142.  
  143.     return $output
  144. }
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top