Unable to run the PS query.

  • How to check if I have a list of SQL Servers in a .TXT file and in the list, what are the SQL Servers are not accessible/stopped/not working?

    $MachineList = get-content "C:\PS\list_server.txt";

    $MachineList.Count

    foreach ($Machine in $MachineList)

    {

    #Run ping test

    $MachineName = $Machine

    $result = Get-WMIObject -query “select StatusCode from Win32_PingStatus where Address = ‘$MachineName’”

    }

    The above query is throwing error....

    Thanks.

  • Try this one instead

    $servers = get-content "C:\PS\list_server.txt"

    $collection = $()

    foreach ($server in $servers)

    {

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

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

    {

    $status["Results"] = "Up"

    }

    else

    {

    $status["Results"] = "Down"

    }

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

    $collection += $serverStatus

    }

    $collection | Export-Csv -LiteralPath .\ServerStatus.csv -NoTypeInformation

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Thnx Jason..

    Thanks.

  • 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

    Thanks.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply