ForEach-DB.psm1
|
<#
.Synopsis This script executes all commands in a .sql script file in all databases present in the specified SQL instance. .Description If any error occurs the script reports the database that failed and the process continues. If -dbErrorsFileOutput or -dbWarningsFileOutput are provided, the database names of the databases that caused the errors or warnings are written the files specified in these params. If the parameter -dbExceptionFileInput is provided file specified in this parameter should contains a list of database names to be skipped when processing. This is used to avoid processing databases known not to be incompatible with the script. Authentication method used current user in Active Directory. Default level of parallelism if -parallelLevel is not provided is 4 meaning there will be 4 concurrent jobs executing the script. In case Format is not specified while a result set file output is specified, the script will attempt to derive the format type from the file extension. .Parameter Server Name of the MSSQL server to connect to. .Parameter File Filename with a .SQL script to execute against the matched databases. .Parameter dbExceptionFileInput File name containing databases to ignore when processing the command provided in -Query or -File parameter. .Parameter ParallelLevel Level of parallelism used to execute the parameters. Bu default the value of this parameter is 4, meaning 4 parallel processes are going to be spawned and the outout of this jobs is going to be collected and reported in a single pipeline. .Parameter dbWarningsFileOutput Filename where to output database names of databases where the provided script errored out with special RAISERROR used to denote a warning. The type of exception raised is as follows: RAISERROR('Not a compatible database', 20, -1) with log .Parameter dbErrorsFileOutput Filename where to output database names of databases where the provided script errored out when executing the provided statements. .Parameter DetailedErrorLogFileOutput If provided all errors resulting from SQL commands execution will be logged to this file in JSON format. The objects will contain the database name where the error was captured and the resulting error message. .Parameter Query Query/command to execute against matching databases .Parameter ResultSetFormat Format to use when query returns data (SELECT statement). Valid formats are: CSV - Comma delimited output TAB - TAB character delimited output JSON - JSON array of objects .Parameter ResultSetFileOutput File name of target file where to output result sets returned from SELECT calls. Result set will also be shown on the console. .Parameter OpenExcel If specified and if output format type is CSV or XLSX Excel will be opened with the target file specified in ResultSetFileOutput .Parameter ReRunDBFromErrorsFile If this switch is specified the script will re-run all databases contained in the file provided in DbErrorsFileOutput parameter .Parameter BindVariables Pass here an array of key value pairs of SQLCMD variables and their corresponding values. The format for each element in the array is as follows: VARIABLE=VALUE. See examples for more details .Parameter DBDriverQueryFile File name of file containing a driver query returning the server names, database names and buckets to split the list of target databases to execute the query or script. The query must return the following fields: * BUCKET * DBNAME * SERVERNAME This is the query used by default pulling databases from MSSQL metadata: SELECT (ROW_NUMBER() OVER (ORDER BY NAME) - 1) % {parallelLevel} BUCKET, NAME AS DBNAME, 'vm-pc-sql02' SERVERNAME FROM sys.databases When using a custom query make sure to use the variable $parallelLevel in order to create matching buckets for the list of databases. .Parameter ConsoleDuplex When this switch is specified together with ResultSetFileOutput the result set generated will be also output to console. This will slow down processing when using parallel level > 1. .Parameter ShowEachExecSummary This switch controls if showing summary after every database job is processed. .Parameter ShowParams This switch enables showing the values of all parameters passed to this cmdlet .Parameter ConsoleSilence Controls if displaying error messages, final summary per job and total time elapsed. If ConsoleDuplex us enabled result sets will be shown in the console ignoring ConsoleSilence. If not ResultSetFileOutput is specified result sets will also be output to console. .Example # Executes the default query SELECT DB_NAME() DBNAME in each database of server vm-pc-sql02 ForEach-DB -Server vm-pc-sql02 .Example # When using a SELECT statement it's possible to output directly to an Excel compatible .csv file. # The recommended way to do it by using Out-File in order to control the encoding. ForEach-DB -server vm-pc-sql02 -query "select db_name(), getdate()" -ResultSetFormat CSV | Out-File -FilePath .\Output.csv -Encoding UTF8 .Example # The following example runs query SELECT DB_NAME() DBNAME, GETDATE() DATE in all databases, outputs warnings, errors and detailed error messages # to specified files. After completing the generation of CSV output file will call Excel and open the target file ForEach-DB -server vm-pc-sql02 -out output.csv -format CSV -OpenExcel -Warn Warnings.log -Err Errors.log -Query "SELECT DB_NAME() DBNAME, GETDATE() DATE" -Deterror DetError.json .Example # This command binds a variable in the query body passed using the BindVariables parameter ForEach-DB -server vm-pc-sql02 -out output.csv -format CSV -Query "SELECT DB_NAME() DBNAME, `$(A) AS A" -BindVar @("A='1'") #> function IsEmptyString ($str) { $isNull = [string]::IsNullOrEmpty($str) if ($isNull) { return $true } else { return $false } } function resultSetToDelimited ($result, $columnsFile, $delimiter) { if (!(Test-Path -Path $columnsFile)) { try { Out-File -FilePath $columnsFile -InputObject ($result | ConvertTo-Csv -NoTypeInformation -Delimiter $delimiter | Select-Object -First 1 | ForEach-Object {$_ -replace '"', ""}) -Encoding utf8 } catch { # It's possible for two or more threads to try to output the header portion of a delimited file at the same time # we will ignore this errors and assume the file that has the file locked will output the headers successfully if (!$_.Exception.Message.Contains("The process cannot access the file")) { throw } } } return ($result | ConvertTo-Csv -NoTypeInformation -Delimiter $delimiter | Select-Object -Skip 1 | ForEach-Object {$_ -replace '"', ""}) } function elapsedTime($startTime) { $localElapsedTime = $startTime.Elapsed $localTotalTime = $([string]::Format("{0:d2}:{1:d2}:{2:d2}.{3:d2}", $localElapsedTime.hours, $localElapsedTime.minutes, $localElapsedTime.seconds, $localElapsedTime.milliseconds)) return $localTotalTime } function output($content, $consoleDuplex, $consoleSilence, $resultSetFileOutput) { if (($consoleDuplex -and $resultSetFileOutput) -or (!$consoleSilence -and !$resultSetFileOutput)) { Write-Output $content } if ($resultSetFileOutput) { Out-File -FilePath $resultSetFileOutput -InputObject $content -Append -Encoding utf8 } } function ForEach-DB { [CmdletBinding(PositionalBinding = $false)] param( [Parameter(Mandatory = $true, Position = 0, HelpMessage="Enter the MSSQL server name")][Alias("Srv")][string]$server, [Parameter(Position = 1)][Alias("Q")][string]$query = 'SELECT DB_NAME() DBNAME, GETDATE() DATE', [Parameter(Position = 2)][Alias("Out")][string]$resultSetFileOutput, [Alias("Script")][string]$file, [Alias("Except", "Ignore")][string]$dbExceptionFileInput, [Alias("Para")][ValidateRange(1, 16)][int]$parallelLevel = 4, [Alias("Warn")][string]$dbWarningsFileOutput, [Alias("Err")][string]$dbErrorsFileOutput, [Alias("DetError")][string]$detailedErrorLogFileOutput, [Alias("Format")][string][ValidateSet('TAB', 'CSV', 'JSON', 'XLSX', 'PIPE')]$resultSetFormat, [Alias("OpEx")][switch]$openExcel, [Alias("ReRun")][switch]$reRunDBFromErrorsFile, [Alias("Vars", "BindVars")][string[]]$bindVariables, [Alias("Duplex", "Con")][switch]$consoleDuplex, [Alias("DBDriver")][string]$dbDriverQueryFile, [Alias("EachJobSumm", "EachJobSummary")][switch]$showEachExecSummary, [Alias("ShowParameters")][switch]$showParams, [Alias("Silent", "Silence")][switch]$consoleSilence ) function CoalesceFile($fileName) { if ((IsEmptyString $fileName) -eq $false) { try { Get-Content "${fileName}.Job-*" | Out-File "${fileName}" -Append -Encoding utf8 Remove-Item "${fileName}.Job-*" } catch { # If there's no warnings or errors other than source file not existing we will silence the exception if (!$_.Exception.Message.Contains('does not exist, or has been filtered')) { throw } # No sense creating a target blank file. Let's remove the zero bytes file created by Out-File above if ((Test-Path -Path $fileName) -and ((Get-Item $fileName).Length -le 0)) { Remove-Item $fileName } } } } # If Excel has kept XLSX files open, we will add a numeric suffix to the filename right before the extension $global:fileNameSuffix = 0 function PrepareOutputFile($fileName, [ref]$returnFileContents) { if ((IsEmptyString $fileName) -eq $false) { $fileName = [System.IO.Path]::GetFullPath($fileName) if ($global:fileNameSuffix -gt 0) { $filename = $fileName -replace '(\.)([a-zA-Z0-9]*)$', "_${global:fileNameSuffix}.`$2" } $pathExists = Test-Path -Path $fileName if ($pathExists) { if ($returnFileContents) { $returnFileContents.Value = Get-Content -Path $fileName } try { Remove-Item $fileName } catch { # If we have already increased fileNameSuffix to a value > 0 the slot MUST be open # for all ancillary files such as error log, warning log, etc. # In summary, logic to increase fileNameSuffix should only execute for the file file # which happens to be the output file. Other calls simply inherit the pre-set suffix if ($global:fileNameSuffix -gt 0) { throw } while ($true) { try { $global:fileNameSuffix++ $tmpFileName = $filename -replace '(\.)([a-zA-Z0-9]*)$', "_${global:fileNameSuffix}.`$2" if (Test-Path -Path $tmpFileName) { Remove-Item $tmpFileName } $filename = $tmpFileName break } catch { # we will stop trying new filenames at 100. Who will have 100 open Excel windows? if ($global:fileNameSuffix -gt 100) { throw } } } } } } return $fileName } function PrintParameterValues() { if($showParams) { Write-Color -Text "Starting SQL jobs execution with params:" Write-Host "" Write-color -Text "Server: ", "${server}" -Color White, Cyan Write-Color -Text "File: ", "${file}" -Color White, Cyan Write-Color -Text "Query: ", "${query}" -Color White, Cyan Write-Color -Text "ResultSetFileOutput: ", "${resultSetFileOutput}" -Color White, Cyan Write-Color -Text "DB exceptions file input: ", "$dbExceptionFileInput" -Color White, Cyan Write-Color -Text "ParallelLevel: ", "${parallelLevel}" -Color White, Cyan Write-Color -Text "WarningsFileOutput: ", "${dbWarningsFileOutput}" -Color White, Cyan Write-Color -Text "ErrorsFileOutput: ", "${dbErrorsFileOutput}" -Color White, Cyan Write-Color -Text "DetailedErrorLogFileOutput: ", "${detailedErrorLogFileOutput}" -Color White, Cyan Write-Color -Text "ResultSetFormat: ", "${resultSetFormat}" -Color White, Cyan Write-Color -Text "OpenExcel: ", "${openExcel}" -Color White, Cyan Write-Color -Text "ReRunDBFromErrorsFile: ", "${reRunDBFromErrorsFile}" -Color White, Cyan Write-Color -Text "DBDriverQueryFile: ", "${dbDriverQueryFile}" -Color White, Cyan Write-Color -Text "BindVariables: ", "${bindVariables}" -Color White, Cyan Write-Color -Text "ConsoleDuplex: ", "${consoleDuplex}" -Color White, Cyan Write-Color -Text "ShowEachExecSummary: ", "${showEachExecSummary}" -Color White, Cyan Write-Color -Text "ConsoleSilence: ", "${consoleSilence}" -Color White, Cyan Write-Color -Text "ShowParams: ", "${showParams}" -Color White, Cyan Write-Host "" } } function InitializeFileOutput() { # So far only file type requiring initialization is JSON type # In order to build a correct JSON file we enclose all returned objects in an array if ($resultSetFormat -eq "JSON") { output "[" $consoleDuplex $consoleSilence $resultSetFileOutput } } function ReencodeCSVFileAsUTF8() { # Default encoding for CSV files not liked by Excel # After completing our output to CSV we need to re-encode the file # $out = Get-Content $resultSetFileOutput # Set-Content -Value $out -Path $resultSetFileOutput -Encoding UTF8 } function FinalizeFileOutput() { switch ($resultSetFormat) { 'JSON' { # We will close the JSON array of objects and we will add an empty object at the end # given the fact workers add a comma after every object returned from the result sets output "{}]" $consoleDuplex $consoleSilence $resultSetFileOutput } 'CSV' { if (!(Test-Path -Path $resultSetFileOutput)) { return; } ReencodeCSVFileAsUTF8 if ($openExcel) { $excel = New-Object -comobject Excel.Application $workbook = $excel.Workbooks.Open($resultSetFileOutput) $excel.Visible = $true } } 'XLSX' { if (!(Test-Path -Path $resultSetFileOutput)) { return; } ReencodeCSVFileAsUTF8 $xlWorkbookDefault = 51 $xlNoChange = 1 $xlLocalSessionChanges = 2 $excel = New-Object -comobject Excel.Application if (Test-Path -Path "${resultSetFileOutput}.csv") { Remove-Item "${resultSetFileOutput}.csv" } Rename-Item -Path $resultSetFileOutput -NewName "${resultSetFileOutput}.csv" $workbook = $excel.Workbooks.Open($resultSetFileOutput + '.csv') try { $workbook.Worksheets[1].Columns["A:Z"].AutoFit() > $null $workbook.SaveAs($resultSetFileOutput, $xlWorkbookDefault, [Type]::Missing, [Type]::Missing, $false, $false, $xlNoChange, $xlLocalSessionChanges) Remove-Item "${resultSetFileOutput}.csv" } finally { if ($openExcel) { $excel.Visible = $true } else { $workbook.Close() } } } } } # The following command set is going to be executed in parallel using Start-Job $cmd = { param( $jobID ) $file = $using:file $query = $using:query $resultSetFileOutput = $using:resultSetFileOutput $dbWarningsFileOutput = $using:dbWarningsFileOutput $dbErrorsFileOutput = $using:dbErrorsFileOutput $detailedErrorLogFileOutput = $using:detailedErrorLogFileOutput $resultSetFormat = $using:resultSetFormat $dbs = $using:dbs $dbExceptionDBs = $using:dbExceptionDBs $bindVariables = $using:bindVariables $showEachExecSummary = $using:showEachExecSummary $columnsFile = $using:columnsFile $consoleSilence = $using:consoleSilence $consoleDuplex = $using:consoleDuplex $global:dbProcessedCount = 0 $global:dbSkipped = 0 $global:dbWarnings = 0 $global:dbErrors = 0 $delimitedExtensions = @{'CSV' = ','; 'XLSX' = ','; 'TAB' = '`t'; 'PIPE' = '|'} function processScript($dbs) { Write-Progress -Id $jobID -Activity "Job-${jobID}" -Status "Starting..." -PercentComplete 0 $lastPercentComplete = 0 foreach($item in $dbs) { $localStartTime = [System.Diagnostics.Stopwatch]::StartNew() $db = $item | Select-Object -exp DBNAME $server = $item | Select-Object -exp SERVERNAME $percentComplete = (100 * ($global:dbProcessedCount + $global:dbSkipped) / $dbs.Count) if ($percentComplete -ge $lastPercentComplete + 5) { $lastPercentComplete = $percentComplete Write-Progress -Id $jobID -Activity "Job-${jobID}" -Status "Processing ${db}..." -PercentComplete $percentComplete } if ($dbExceptionDBs.Contains($db)) { $global:dbSkipped++ continue } $global:dbProcessedCount++ $ErrorActionPreference = 'SilentlyContinue' try { if($query) { $result = Invoke-SQLCmd -ServerInstance $server -Database $db -Query $query -AbortOnError -MaxCharLength 65535 -Variable $bindVariables } else { $result = Invoke-SQLCmd -ServerInstance $server -Database $db -InputFile $file -AbortOnError -MaxCharLength 65535 -Variable $bindVariables } if($result) { if ($delimitedExtensions[$resultSetFormat]) { $resultTable = resultSetToDelimited $result $columnsFile $delimitedExtensions[$resultSetFormat] } elseif ($resultSetFormat -eq "JSON") { $resultTable = $result | Select-Object * -ExcludeProperty ItemArray, Table, RowError, RowState, HasErrors | ConvertTo-Json $resultTable = $resultTable -replace '(^\[[ \r\n]*)|([ \r\n]*\]$)', '' $resultTable += "," } output $resultTable $consoleDuplex $consoleSilence "${resultSetFileOutput}.Job-${jobID}" } $localTotalTime = elapsedTime $localStartTime if ($showEachExecSummary) { if (!$result) { Write-Color -Text "Job-${jobID}: ", "${db}", "... OK. Elapsed: ${localTotalTime}" -Color White, Cyan, White } else { $recCount = ($result | Measure).Count Write-Color -Text "Job-${jobID}: ", "${db}", "... Retrieved ", "${recCount}", " records. Elapsed: ${localTotalTime}" -Color White, Cyan, White, Green, White } } } catch { $localTotalTime = elapsedTime $localStartTime if ($_.Exception.Message.Contains('user error 50000, severity 20')) { $global:dbWarnings++ if ($showEachExecSummary) { Write-Color -Text "Job-${jobID}: ", "${db}...", " WARNING", ": Database incompatible. Elapsed: ${localTotalTime}" -Color White, Cyan, Yellow, White if ((IsEmptyString $dbWarningsFileOutput) -eq $false) { Out-File -FilePath "${dbWarningsFileOutput}.Job-${jobID}" -InputObject "${db}" -Append -Encoding utf8 } } } else { $global:dbErrors++ if (!$consoleSilence) { Write-Color -Text "Job-${jobID}: ", "${db}...", " ERROR", ": script failed. Elapsed: ${localTotalTime}" -Color White, Cyan, Red, White } if ((IsEmptyString $dbErrorsFileOutput) -eq $false) { Out-File -FilePath "${dbErrorsFileOutput}.Job-${JobID}" -InputObject "${db}" -Append -Encoding utf8 } if ((IsEmptyString $detailedErrorLogFileOutput) -eq $false) { $props = @{ Database = $db ErrorMessage = $_.Exception.Message } $ErrorMsgObj = New-Object psobject -Property $props $errorAsJson = ConvertTo-Json -InputObject $ErrorMsgObj Out-File -FilePath "${detailedErrorLogFileOutput}.Job-${JobID}" -InputObject "${errorAsJson}," -Append -Encoding utf8 } } } finally { $ErrorActionPreference = 'Stop' } } } processScript $dbs Write-Progress -Id $jobID -Activity "Job-${jobID}" -Status "Completed" -PercentComplete 100 -Completed if (!$consoleSilence) { Write-Color -Text "Job-${jobID} finished. ", "Summary: ", "processed ", "${dbProcessedCount}", ", Skipped ", "${dbSkipped}", ", Warnings ", "${dbWarnings}", ", Errrors ", "${dbErrors}", "." -Color White, Green, White, Green, White, Yellow, White, Yellow, White, Red, White } } # Environment initialization $ErrorActionPreference = "Stop" # Set the current directory on the .NET layer so we can normalize file names when calling PrepareOutputFile [System.IO.Directory]::SetCurrentDirectory(((Get-Location -PSProvider FileSystem).ProviderPath)) # Paramaters setup and verification if ($consoleSilence -and ($consoleDuplex -or $showEachExecSummary)) { throw "-ConsoleSilence can't be specified together with -ConsoleDuplex or -ShowEachExecSummary parameters" } if ((IsEmptyString $query) -eq $false -and (IsEmptyString $file) -eq $false) { throw "-Query and -File parameters can be specified at the same time" } if ((IsEmptyString $file) -eq $false) { $file = [System.IO.Path]::GetFullPath($file) } $resultSetFileOutput = PrepareOutputFile $resultSetFileOutput $dbWarningsFileOutput = PrepareOutputFile $dbWarningsFileOutput [string[]]$erroredOutDBs = @() $dbErrorsFileOutput = PrepareOutputFile $dbErrorsFileOutput ([ref]$erroredOutDBs) if (((IsEmptyString $dbErrorsFileOutput -eq $false) -or !(Test-Path -Path $dbErrorsFileOutput)) -and $reRunDBFromErrorsFile) { throw "-DBErrorsFileOutput not provided or error file not found. -ReRunDBFromErrorsFile was set but no error file was loaded. Can't run." } $detailedErrorLogFileOutput = PrepareOutputFile $detailedErrorLogFileOutput $columnsFile = PrepareOutputFile "output_columns.csv" if ((IsEmptyString $resultSetFormat) -eq $true) { if ((IsEmptyString $resultSetFileOutput) -eq $false) { if (($resultSetFileOutput -match '\.([a-zA-Z0-9]*)$') -and (@('TAB', 'CSV', 'XLSX', 'JSON', 'PIPE')) -contains $Matches[1]) { $resultSetFormat = $Matches[1] } else { $resultSetFormat = 'CSV' } } else { $resultSetFormat = 'TAB' } } if (!(@('XLSX', 'CSV') -contains $resultSetFormat) -and $openExcel) { throw "-OpenExcel option can be used only when -ResultSetFormat is equal to XLSX or CSV" } # Finished parameters setup and verification # Detailed error file will be an array of JSON objects. Let's open the array if ((IsEmptyString $detailedErrorLogFileOutput) -eq $false) { Out-File -FilePath $detailedErrorLogFileOutput -InputObject "[" -Encoding utf8 } if ((IsEmptyString $dbExceptionFileInput) -eq $false) { $dbExceptionFileInput = [System.IO.Path]::GetFullPath($dbExceptionFileInput) [string[]]$dbExceptionDBs = Get-Content -Path $dbExceptionFileInput } else { $dbExceptionDBs = @() } # Let's retrieve the list of databases in the server if ((IsEmptyString $dbDriverQueryFile) -eq $false) { if (Test-Path -Path $dbDriverQueryFile) { $dbsQuery = Get-Content -Path $dbDriverQueryFile -Raw # User should use the token {parallelLevel} to create buckets matching the number of parallelism $dbsQuery = $dbsQuery -replace '{parallelLevel}', "${parallelLevel}" } else { throw "Could not find driver file ${dbDriverQueryFile}" } } else { $dbsQuery = "SELECT (ROW_NUMBER() OVER (ORDER BY NAME) - 1) % ${parallelLevel} BUCKET, NAME AS DBNAME, '${server}' SERVERNAME FROM sys.databases" } $databases = Invoke-Sqlcmd -ServerInstance ${server} -Database master -query ${dbsQuery} if ($reRunDBFromErrorsFile -and $erroredOutDBs.Count -gt 0) { $databases = $databases.Where({($erroredOutDBs -contains ($_ | Select-Object -exp DBNAME)) -eq $true}) } $db_splits = @(0..($parallelLevel - 1)) $db_splits[0] = $databases for ($i = 0; $i -lt $parallelLevel - 1; $i++) { $db_splits[$i], $db_splits[$i + 1] = $db_splits[$i].Where({($_ | Select-Object -exp BUCKET) -eq $i}, 'Split') } $startTime = [System.Diagnostics.Stopwatch]::StartNew() PrintParameterValues InitializeFileOutput $consoleDuplex $consoleSilence $resultSetFileOutput # Let's start and process the parallel jobs output [console]::TreatControlCAsInput = $true try { # Let's stop abandoned jobs that never properly started Get-Job -State NotStarted | Stop-Job Get-Job | Where({($_ | Select-Object -exp State) -ne 'Running'}) | Remove-Job $jobID = 1 # env:\MODULE_PATH environment variable is used in order to be able to import functions # needed within the codeblock used below when callint Start-Job. Essentially this trick # allows the parallel workers to use specific functions from the main module (this module) if (Test-Path -Path env:\MODULE_PATH) { Remove-Item -Path env:\MODULE_PATH } New-Item -Name MODULE_PATH -Value $MyInvocation.MyCommand.Module.Path -Path env:\ > $null try { foreach($dbs in $db_splits) { Start-Job -ScriptBlock $cmd -ArgumentList ($jobID++) -InitializationScript { Import-Module -Name $env:MODULE_PATH -DisableNameChecking } > $null } $runningJobs = $true while ($runningJobs) { $runningJobs = Get-Job -State Running # Randomize the list so screen updates is not always from top to bottom $runningJobs = $runningJobs | Sort-Object {Get-Random} if($runningJobs) { if ($resultSetFileOutput -and !$consoleDuplex) { Receive-Job $runningJobs > $null } else { Receive-Job $runningJobs } Wait-Job $runningJobs -Timeout 0 > $null # if Ctrl-C is pressed we will cancel all of our child jobs if ([console]::KeyAvailable) { $key = [system.console]::readkey($true) if (($key.modifiers -band [consolemodifiers]"control") -and ($key.key -eq "C")) { Write-Color -Text "Terminating..." -Color Red $runningJobs | Stop-Job break } } } } } finally { Remove-Item -Path env:\MODULE_PATH } } finally { [console]::TreatControlCAsInput = $false } # Even though our execution run has completed we may not have fetched all output from the child jobs $jobs = Get-Job -HasMoreData $true if ($jobs) { if ($consoleDuplex) { $jobs | Receive-Job } else { $jobs | Receive-Job > $null } } if ($resultSetFileOutput -and (Test-Path -Path $columnsFile)) { Get-Content -Path $columnsFile | Out-File -FilePath $resultSetFileOutput -Encoding utf8 Remove-Item $columnsFile } CoalesceFile $resultSetFileOutput CoalesceFile $dbWarningsFileOutput CoalesceFile $dbErrorsFileOutput CoalesceFile $detailedErrorLogFileOutput if ((IsEmptyString $detailedErrorLogFileOutput) -eq $false) { # We will close our JSON array of detailed errors. Note the need for {} # due to the extra comma after the last error object logged Out-File -FilePath $detailedErrorLogFileOutput -InputObject "{}]" -Append -Encoding utf8 } FinalizeFileOutput # Final step: display total elapsed time $elapsedTime = $startTime.Elapsed $totalTime = $([string]::Format("{0:d2}:{1:d2}:{2:d2}.{3:d2}", $elapsedTime.hours, $elapsedTime.minutes, $elapsedTime.seconds, $elapsedTime.milliseconds)) if (!$consoleSilence) { Write-Host "" Write-Color -Text "Total elapsed ", "${totalTime}" -Color White, Cyan } } Export-ModuleMember -Function ForEach-DB # The following functions are used from within the codeblock used when calling Start-Job Export-ModuleMember -Function IsEmptyString Export-ModuleMember -Function resultSetToDelimited Export-ModuleMember -Function elapsedTime Export-ModuleMember -Function output |