Need product information

I am looking for a way to find production information for sql server. I am looking for @@version but I don't want to run @@version and parse this. Would prefer to get this from serverproperty ('correctpropertyname') 

I want everything before the - (dash) 

 

Microsoft SQL Server 2008 (SP3) - 

 

 

 

 

 

  • Hi 4Wheels,

    Here's something you should be able to modify

    function Get-SQLVersion

    {
    [CmdletBinding()]
    [OutputType([psobject])]
    param
    (
    [Parameter(Mandatory = $false, ValueFromPipelineByPropertyName = $true, HelpMessage = 'Computer name')] [string] $Computername = $env:computername,
    [Parameter(Mandatory = $true, ValueFromPipelineByPropertyName = $true, HelpMessage = 'Instance')] [string] $SQLServer,
    [Parameter(Mandatory = $true, ValueFromPipelineByPropertyName = $true, HelpMessage = 'Credential')] [PSCredential] $Credential
    )


    Process
    {
    Invoke-Command -ComputerName $Computername -Credential $Credential -ScriptBlock {


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

    $SqlConnection = New-Object -TypeName System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = "Server=$using: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'
    }

    }

    $hash = @{
    DisplayName = "$versionName $($Tables.Column2) $($Tables.Column3) $($Tables.Column1)"
    Name = $versionName
    Version = $Tables.Column3
    ServicePack = $Tables.Column2
    VersionNumber = $Tables.Column1
    }

    New-Object -TypeName PSobject -Property $hash
    }
    }
    }
  • This is what I did. I ended up using @@version, but was able to use a substring so that I get the product information that I was looking for. I want this information from the server instead of something from a hard coded case statement. 

    $SqlQuery = "SELECT

    SERVERPROPERTY('ServerName') AS ServerName,

    SUBSTRING(@@VERSION, 0,CHARINDEX('-', @@VERSION) - 1) as Product,

    SERVERPROPERTY('ProductVersion') AS ProductVersion,

    SERVERPROPERTY('ProductLevel') AS ProductLevel,

    SERVERPROPERTY('Edition') AS Edition,

    SERVERPROPERTY('EngineEdition') AS EngineEdition

     

  • For those interested in the SMO version of the above, here is the approximate code:

    [void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
    $sqldetails = @()
    $sqlservers = "sqlserver","sql2005express\sqlexpress"

    foreach ($sqlserver in $sqlservers) {
    $server = New-Object Microsoft.SqlServer.Management.Smo.Server $sqlserver

    switch ($server.VersionMajor) {
    9 { $version = 2005 }
    10 {
    $version = 2008
    if ($server.VersionMinor -eq 50) { $version = "2008 R2" }
    }
    11 { $version = 2012 }
    12 { $version = 2014 }
    13 { $version = 2016 }
    }

    $sqldetails += [PSCustomObject]@{
    ServerName = $server.DomainInstanceName
    Product = "SQL Server $version"
    ProductVersion = $server.ResourceVersion
    ProductLevel = $server.ProductLevel
    Edition = $server.Edition
    EngineEdition = $server.EngineEdition
    }

    }

    $sqldetails

    The output looks like this