Parsing @@Version output question

Hello,

I am using Powershell 2.0 with SQL snapin loaded and trying to get the SQL server Version, version number and Edition.

$ver = Invoke-Sqlcmd  -ServerInstance 'ServerName\InstanceName' -Query 'select @@version'
$ver

Result:
--------------------------------
Column1 : Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
            Apr  2 2010 15:48:46
            Copyright (c) Microsoft Corporation
            Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
---------------------------------- 

My question is, how do I parse this single string output of four lines into

$SQLVersion = Microsoft SQL Server 2008 R2
$SQLVerNum = 10.50.1600.1
$SQLEdittion = Standard Edition (64-bit)
$Buil = Build 9200

I am assuming that i should be using RegEx, but not really getting anywhere (especially because I cant figure out how to look at a separate line

 

 

 

 

 

 

 

 

 

  • Instead of SELECT @@Version you can run this:

    SELECT
    SERVERPROPERTY('ProductVersion') AS ProductVersion,
    SERVERPROPERTY('ProductLevel') AS ProductLevel,
    SERVERPROPERTY('Edition') AS Edition,
    SERVERPROPERTY('EngineEdition') AS EngineEdition;
    GO
    The output from Invoke-SQLCmd with this command will be an object with ProductVersion,ProductLevel,Edition and EngineEdition as properties.
    If you wish to parse that exact output, the below is possible, however probably not useful for other versions...
    $result = Invoke-SQLCmd -Query "SELECT @@VERSION" | Select-Object -ExpandProperty column1
    $result = $result -split "`n"
    $SQLVersion = $($result[0] -split '-')[0].trim()
    $SQLVerNum = $($($result[0] -split '-')[1].trim() -split " ")[0]
    $SQLEdittion = $($result[3] -split "\son\s")[0].trim()
    $Build = $($($($result[3] -split "\son\s") -split "\(")[3] -split "\:")[0].trim()
  • Try this variant:

     001 
     002 
     003 
     004 
     005 
     006 
     007 
     008 
     009 
     010 
     011 
     012 
     013 
     014 
     015 
     016 
     017 
     018 
     019 
    
    $ver = 
    @"
    --------------------------------
    Column1 : Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
                Apr  2 2010 15:48:46
                Copyright (c) Microsoft Corporation
                Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
    ----------------------------------
    "@
     
    $SQLEdittion = [regex]::match($ver,'\n\s+(.+)\son').groups[1].value
    $SQLVersion = [regex]::match($ver,':\s([^(-]+)').groups[1].value
    $SQLVerNum = [regex]::match($ver,'\d+(\.\d+){3}').value
    $Build = [regex]::match($ver,'Build \d+').value
     
    $SQLEdittion
    $SQLVersion
    $SQLVerNum
    $Build
    

     

  • I tend to write my scripts from a remoting point of view, so usually use the .NET approach

    $sqlServer = "$env:computername"

    $SQLquery = @'
    SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')
    '@

    $SqlConnection = New-Object -TypeName System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = "Server=$sqlServer;Database=Master;Integrated Security=SSPI;"
    $SqlCmd = New-Object -TypeName System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = $SQLquery
    $SqlCmd.Connection = $SqlConnection
    $SqlAdapter = New-Object -TypeName System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd

    $DataSet = New-Object -TypeName System.Data.DataSet
    $nSet = $SqlAdapter.Fill($DataSet)
    $SqlConnection.Close()
    $Tables = $DataSet.Tables
    $arrayVersion = ($($Tables.Column1).Split('.'))
    [string] $SQLVersionNumber = "$($arrayVersion[0]).$($arrayVersion[1])"

    Switch -Wildcard ($SQLVersionNumber) {
    '12.0*'
    {
    $versionName = 'SQL Server 2014'
    }
    '11.0*'
    {
    $versionName = 'SQL Server 2012'
    }
    '10.50*'
    {
    $versionName = 'SQL Server 2008 R2'
    }
    '10.0*'
    {
    $versionName = 'SQL Server 2008'
    }
    '9.0*'
    {
    $versionName = 'SQL Server 2005'
    }
    '8.0*'
    {
    $versionName = 'SQL Server 2000'
    }
    '7.0*'
    {
    $versionName = 'SQL Server'
    }

    }

    Write-Output -InputObject "$versionName $($Tables.Column2) $($Tables.Column3) ($($Tables.Column1))"
  • This looks exactly like what I am looking for, however when I get the actual data, 

    $ver=Invoke-SQLCmd  -ServerInstance Server\Instance -Query "Select @@version"

    and run the matches,

    $SQLEdittion = [regex]::match($ver,'\n\s+(.+)\son').groups[1].value
    $SQLVersion = [regex]::match($ver,':\s([^(-]+)').groups[1].value
    $SQLVerNum = [regex]::match($ver,'\d+(\.\d+){3}').value
    $Build = [regex]::match($ver,'Build \d+').value
     
    
    The values are empty

     

  • I would be glad to help but I have no SQL (no need).

  • This code turned out to be exactly what I was looking for. Thank you!

    Just needed to change $ver to $ver.column1 and it worked perfectly for all versions of SQl Servers that I was able to connect to.

    $SQLEdittion = [regex]::match($ver.column1,'\n\s+(.+)\son').groups[1].value
    $SQLVersion = [regex]::match($ver.column1,':\s([^(-]+)').groups[1].value
    $SQLVerNum = [regex]::match($ver.column1,'\d+(\.\d+){3}').value
    $Build = [regex]::match($ver.column1,'Build \d+').value