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

 

 

 

 

 

 

 

 

 

Parents
No Data
Reply
  • 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))"
Children
No Data