Hello,

I wanted to use Multi-thread, as I need to run some scripts to multiple instances at the same time. Single time execution takes longer, so threading is the best choice. Can you please review the below code and suggest to make any changes to run it successfully..?

import-module "SQLPS" -DisableNameChecking

set-location "C:\PS\check\"

$servers = get-content "C:\PS\check\list_servertest.txt";

#$servers.Count

$collection = $()

$newset = $servers.Replace("\XYZ","")

foreach ($server in $newset)

{

$status = @{ "ServerName" = $server; "TimeStamp" = (Get-Date -f s) }

if (!(Test-Connection $server -Count 1 -ea 0 -Quiet))

{

#Server is DOWN

$status["Results"] = "DOWN"

}

else

{

#Server is UP

$status["Results"] = "UP"

$sqlservices = Get-Service -ComputerName $server | Where-Object {$_.ServiceName -like "MSSQL`$XYZ"}

#Write-Host "SQL Services on Server [$($server)]"

$status += $sqlservices | Select-Object Name, DisplayName, Status

}

New-Object -TypeName PSObject -Property $status -OutVariable serverStatus

$collection += $serverStatus

}

$collection| Out-File -filepath "C:\PS\check\good_servers.txt" -append

$servers = Get-Content "C:\PS\check\good_servers.txt"

$filePathOutput = "C:\PS\check\execution_result.txt"

<#

[ScriptBlock]$scriptblock = {

function Execute-SQLCommand {param( [string]$Server, #the host name of the SQL server

[string]$Database, #the name of the database

[System.Data.SqlClient.SqlCommand]$Command) #the command to execute (name of stored procedure)

$sqlConnection = New-Object System.Data.SqlClient.SqlConnection

$sqlConnection.ConnectionString = "Integrated Security=SSPI;Persist Security Info=False;User ID=user;Initial Catalog=$Database;Data Source=$Server"

$Command.CommandType = [System.Data.CommandType]::Text

$Command.Connection = $sqlConnection

$sqlConnection.Open()

$Result = $Command.ExecuteNonQuery()

$sqlConnection.Close()

if ($Result -gt 0)

{

{

Invoke-Sqlcmd -ServerInstance $instance -Connectiontimeout 10 -Database "master"

-InputFile "C:\PS\check\permissions_check.sql" -QueryTimeout 10 | Out-File -filePath "C:\PS\check\permissions_check_output.txt" -Append

Write-Host "Processing completed on Server: $($instance)" -Fore Green

}

# permissions_check.sql is to check the permission of an account.

return $TRUE

}

else

{

return $FALSE

}

}

#>

$MaxThreads = 20

$SleepTimer = 1000

foreach($server in $servers) {

Start-Job -ScriptBlock {"C:\PS\check\main_call.Ps1" }

While($(Get-Job -State 'Running').Count -ge $MaxThreads) {

Start-Sleep -Milliseconds $SleepTimer #Need this to wait until a job is complete and kick off a new one.

Write-Host "Processing completed on Server: $($instance)" -Fore Green

}

}

Get-Job | Wait-Job | Receive-Job