Functions/Invoke-SdtTsqlScript.ps1
|
function Invoke-SdtTsqlScript { #[OutputType([System.Data.DataSet])] [CmdletBinding( DefaultParameterSetName='ByScriptFilePath')] Param ( [Parameter(Mandatory = $true)] [string]$ServerInstance , [Parameter(Mandatory = $false)] [string]$Database = 'master' , [Parameter(Mandatory = $true, ParameterSetName = 'ByScriptFilePath')] [string]$InputFile , [Parameter(Mandatory = $true, ParameterSetName = 'ByTSQLString')] [string]$Query , [Parameter(Mandatory = $false)] [Switch]$GetPipelineOutput ) [void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO'); switch($PSCmdlet.ParameterSetName) { 'ByScriptFilePath' { $script_contents = Get-Content -Path "$InputFile" | Out-String; $OutputFile = "$$SdtLogsPath\Invoke-SdtTsqlScript\$((Get-ItemProperty $InputFile).BaseName)__$(($ServerInstance -split '\\')[0]).txt"; } 'ByTSQLString' { $script_contents = $Query; $OutputFile = "$$SdtLogsPath\Invoke-SdtTsqlScript\$ServerInstance.txt"; } } Write-Verbose "`$OutputFile = '$OutputFile'"; $OutputFinal = @(); # Create Output File if (!(Test-Path "$$SdtLogsPath\Invoke-SdtTsqlScript")) { Write-Verbose "Path "+"$$SdtLogsPath\Invoke-SdtTsqlScript does not exist. Creating it."; New-Item -ItemType "directory" -Path "$$SdtLogsPath\Invoke-SdtTsqlScript"; } # Separate script into different groups by 'GO' $createScriptList = [regex]::Split($script_contents, '\bGO'); foreach ($cSL in $createScriptList) { #$cSl; $Output = $null; Try { $Output = Execute-SqlQuery -ServerInstance $ServerInstance -Database $Database -Query "$cSL" -ErrorAction SilentlyContinue; } Catch { $ErrorMessage = $_.Exception.Message; $FailedItem = $_.Exception.ItemName; $Output = @" ======================================================================== ======================================================================== Error occurred while executing script. Below is the Error Message:- $ErrorMessage "@; } if([String]::IsNullOrEmpty($Output) -eq $false ) { $OutputFinal += $Output; } } #$dtable = $Result | Out-SdtDataTable; $OutputFinal | Out-File -FilePath "$OutputFile" -Append; } <# Invoke-SdtTsqlScript -ServerInstance $env:COMPUTERNAME ` -InputFile 'C:\Users\adwivedi\Documents\WindowsPowerShell\Modules\SQLDBATools\SQLQueries\__06_Setup_DBA_db.sql' ` -Verbose #> |