Sql versions for multiple servers

1

I would like a script that checks the sql server versions of all 50 servers, what's the best way to do this instead of logging into sech server select @@version . is there a script I could use

2

It should be "simple" using SQLCMD mode in SQL Server Management Studio.

Change the query type to SQLCMD Mode (query menu, SQLCMD Mode) and then list your servers one-by-one with the @@VERSION command. It is one script that will log on to all 50 servers at once.

Very simple.

:CONNECT ServerA

SELECT @@VERSION

:CONNECT ServerB

SELECT @@VERSION

3

when I run multiple servers it returns the same versions when they are different versions
:CONNECT REDSGETSTSTST
SELECT @@VERSION
:CONNECT FREDSAST
SELECT @@VERSION

4

My apologies. I neglected to include the GO statement.

:CONNECT ServerA
SELECT @@VERSION
GO

:CONNECT ServerB
SELECT @@VERSION
GO

5

hi

hope this helps

PowerShell with T-SQL

  # SQL Server Version Checker Script
    # This script checks the SQL Server version for multiple servers and logs the results
    
    # Read server list from file (one server name per line)
    $serverListPath = "C:\Servers.txt"
    
    # Create log file paths
    $logPath = "C:\SQLVersions_$(Get-Date -Format yyyyMMdd_HHmmss).log"
    $errorLogPath = "C:\Errors_$(Get-Date -Format yyyyMMdd_HHmmss).log"

    # Load SQL Server PowerShell module
    Import-Module SqlServer

    # Get list of servers
    $servers = Get-Content -Path $serverListPath

    # Loop through each server
    foreach ($server in $servers) {
        Write-Progress -Activity "Checking SQL Server versions" -Status $server -PercentComplete ($servers.IndexOf($server) / $servers.Count * 100)
        
        try {
            # Get server version information
            $versionInfo = Invoke-SqlCmd -ServerInstance $server -Query "SELECT @@VERSION"
            
            # Extract version information
            $versionString = $versionInfo.[0].Column1
            
            # Log the version information
            "$server - $versionString" | Add-Content -Path $logPath
        }
        catch {
            # Log any errors
            "Error connecting to $server: $($Error[0].Message)" | Add-Content -Path $errorLogPath
            Write-Warning "Failed to connect to $server: $($Error[0].Message)"
        }
    }

    Write-Host "Version checking completed!"
    Write-Host "Results saved to: $logPath"
    Write-Host "Error log: $errorLogPath"
1 Like
6

If those are real server names, I strongly recommend you take that post down.

7

Can you try using registered servers?

8

Using dbatools:

Get-DbaInstanceProperty -SqlInstance 'instance_name\DEV','instance_name\PROD','other_instance' | 
Where-Object -Property Name -In ('Edition', 'VersionString') | 
Format-Table -Property SqlInstance, Name, Value -AutoSize

You can replace the hard coded list of instances with a variable, or a CSV, or depending on your situation, with Find-DbaInstance.

SqlInstance         Name          Value
-----------         ----          -----
some_instance\DEV   Edition       Developer Edition (64-bit)
some_instance\DEV   VersionString 16.0.1135.2
another_instance    Edition       Standard Edition (64-bit)
another_instance    VersionString 16.0.4175.1