Posted On October 7, 2020

Invoke T-SQL on Multiple Servers

kimconnect 0 comments
blog.KimConnect.com >> Codes , Database >> Invoke T-SQL on Multiple Servers
$computersAndCredentials=@{
    'SQL01'=@{'intranet\sql01Admin'='PASSWORD'}
    'SQL02'=@{'intranet\sql02Admin'='PASSWORD'}
    'SQL03'=@{'intranet\sql03Admin'='PASSWORD'}
    'SQL04'=@{'intranet\sql04Admin'='PASSWORD'}
}
$tSql="
    --Update the Setting with a new value:
    USE MSCRM_CONFIG
    GO
    UPDATE ServerSettingsProperties
    SET IntColumn='600' where ColumnName='LongQueryThresholdInSeconds'
    GO
    --View the current Setting:
    select IntColumn from MSCRM_CONFIG..ServerSettingsProperties
    where ColumnName='LongQueryThresholdInSeconds'
    GO
    "

function invokeSql($computersAndCredentials,$tSql){
    
    foreach ($item in $computersAndCredentials.GetEnumerator()){
        $computerName=$item.Name
        $item.Value.getEnumerator()|%{$userName=$_.Name;$plainTextPassword=$_.Value}
        $password=ConvertTo-securestring $plainTextPassword -AsPlainText -Force
        $credentials=New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $userName,$password
        #write-host "$($item.Name) = $username / $plainTextPassword"
        $scriptBlock={
            param($tSql)
            function confirmation($content,$testValue="I confirm",$maxAttempts=3){
                $confirmed=$false;
                $attempts=0;        
                $content|write-host
                write-host "Please review this content for accuracy.`r`n"
                while ($attempts -le $maxAttempts){
                    if($attempts++ -ge $maxAttempts){
                        write-host "A maximum number of attempts have reached. No confirmations received!`r`n"
                        break;
                        }
                    $userInput = Read-Host -Prompt "Please type in this value => $testValue <= to confirm. Input CANCEL to skip this item";
                    if ($userInput.ToLower() -eq $testValue.ToLower()){
                        $confirmed=$true;
                        write-host "Confirmed!`r`n";
                        break;                
                    }elseif($userInput -like 'cancel'){
                        write-host 'Cancel command received.'
                        $confirmed=$false
                        break
                    }else{
                        Clear-Host;
                        write-host $($content|out-string).Trim()
                        write-host "Attempt number $attempts of $maxAttempts`: $userInput does not match $testValue. Try again or Input CANCEL to skip this item`r`n"
                        }
                    }
                return $confirmed;
            }
            write-host "Executing function on $env:computername"
            import-module sqlps
            $confirmed=confirmation -content $tSql
            if($confirmed){
                try{
                    invoke-sqlcmd -Query $tSql -QueryTimeout $([int]::MaxValue) -ConnectionTimeout 0 -Verbose
                    return $true
                }catch{
                    write-warning $_
                    return $false
                }
            }else{
                write-host 'No confirmations received. Changes have NOT been made.'
                return $false
            }
        }
        $result=invoke-command -ComputerName $computerName -Credential $credentials -ScriptBlock $scriptBlock -Args $tSql
        write-host $result
    }

}

invokeSql $computersAndCredentials $tSql

Leave a Reply

Your email address will not be published. Required fields are marked *

Related Post

PowerShell: Get ‘Size on Disk’ of Files in Windows

There are many methods of attempting to obtain "size on disk" values of files in…

CSS Snippet to Target Phones, Tablets, and Desktops Separately

@media (min-width:20em) { /* iPhones, Androids portrait 480x320 phones */ } @media (min-width:30em) { /*…

How to Call Functions or Pass Function as Argument

cls   function listClusters{     # List all Clusters in this Domain     $items=get-cluster -domain (get-addomain)     $global:clusters=$items…
Other project: DragonCoin.com