Activity

1. andrecrms commented on Mar 5, 2025

   

   André Rodrigues (andrecrms)

   on Mar 5, 2025

   Author

   More actions

   Full script:

   $VMs = @("server1", "server2")
   $Connection = @()
   $Jobs = @()
   $LogFile = "$(Get-Location)\execution_log.txt"

   Function to log messages to a file

   function Log-Message {
   param ([string]$Message)
   $timestamp = Get-Date -Format "yyyy-MM-dd HH:mm:ss"
   "$timestamp - $Message" | Out-File -FilePath $LogFile -Append
   }

   Log script start

   Log-Message "Script execution started."
   Write-Host "Execution started. Logs will be saved to: $LogFile"

   Establish the sessions and execute the SQL command on each server in parallel

   foreach ($VM in $VMs) {
   Try {
   # Attempt to create a session to the remote machine
   $Session = New-PSSession -ComputerName $VM -ErrorAction Stop
   Write-Host "Successfully connected to $VM"
   Log-Message "Successfully connected to $VM"

       # Start a background job to execute SQL commands
       $Job = Invoke-Command -Session $Session -ScriptBlock {
           Try {
               Import-Module sqlps -ErrorAction SilentlyContinue
   
               # Get the SQL Server instance name
               $serverName = $env:COMPUTERNAME.ToLower()
               $instanceNames = (Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server').InstalledInstances
               $queryResult = ""
   
               foreach ($instanceName in $instanceNames) {
                   $sqlInstance = if ($instanceName -eq "MSSQLSERVER") { 
                       $serverName 
                   }
                   else { 
                       "$serverName\$instanceName" 
                   }
   
                   # Define the SQL query
                   $query = "
                   
                   USE [master]
   

   GO

                   "
   
                   Try {
                       # Attempt to execute the query
                       $result = Invoke-Sqlcmd -ServerInstance $sqlInstance -Query $query -QueryTimeout 65535 -ErrorAction Stop
                       $queryResult = "SQL executed successfully on $sqlInstance."
                       $status = "Success!"
                   }
                   Catch {
                       # Capture any errors from the SQL query execution
                       $lineNumber = $_.InvocationInfo.ScriptLineNumber
                       $queryResult = "Error executing SQL at line $($lineNumber): $($_.Exception.Message)"
                       $status = "Fail"
                   }
               }
   
               # Return the results
               [PSCustomObject]@{
                   ComputerName = $serverName
                   SQLResult    = $queryResult
                   Status       = $status
               }
           }
           Catch {
               # Capture any critical errors within the script block
               $lineNumber = $_.InvocationInfo.ScriptLineNumber
               [PSCustomObject]@{
                   ComputerName = $env:COMPUTERNAME
                   SQLResult    = "N/A"
                   Status       = "Fail - Critical error at line $($lineNumber): $($_.Exception.Message)"
               }
               Log-Message "Critical error on $env:COMPUTERNAME at line $($lineNumber): $($_.Exception.Message)"
           }
       } -AsJob
   
       # Add the job to the jobs list
       $Jobs += $Job
   }
   Catch {
       # Capture errors when trying to connect
       $lineNumber = $_.InvocationInfo.ScriptLineNumber
       $Connection += [PSCustomObject]@{
           ComputerName = $VM
           Status       = "Fail"
           ErrorMessage = "Connection error at line $($lineNumber): $($_.Exception.Message)"
       }
       Write-Host "Failed to connect to $VM. Error: $($_.Exception.Message)"
       Log-Message "Failed to connect to $VM at line $lineNumber. Error: $($_.Exception.Message)"
   }
   

   }

   Wait for all jobs to finish

   $Jobs | ForEach-Object {
   # Wait for the job to complete
   Wait-Job -Job $_

   # Fetch the result of the job
   $JobResult = Receive-Job -Job $_
   
   # Output the result for each server
   Write-Host "Result for $($JobResult.ComputerName):"
   Write-Host "SQL Execution Result: $($JobResult.SQLResult)"
   Write-Host "Status: $($JobResult.Status)"
   Write-Host "----------------------------------------------------"
   Log-Message "Result for $($JobResult.ComputerName): SQL Execution Result: $($JobResult.SQLResult) - Status: $($JobResult.Status)"
   
   # Check the job status before removing it
   if ($_.State -eq 'Completed') {
       # Clean up the job
       Remove-Job -Job $_
   }
   

   }

   If there were any connection errors, output them and log

   if ($Connection.Count -gt 0) {
   Write-Host "Connection Issues:"
   Log-Message "Connection Issues:"
   $Connection | ForEach-Object {
   Write-Host "Computer: $($.ComputerName), Error: $($.ErrorMessage)"
   Log-Message "Computer: $($.ComputerName), Error: $($.ErrorMessage)"
   }
   }

   Log script completion

   Log-Message "Script execution completed."
   Write-Host "Execution completed. Log file saved at: $LogFile"
2. Christoph Bergmeister (bergmeister)

   added

   Issue - Bug

   on Mar 12, 2025