$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
Categories: