21

How do find out which edition is installed without having the management studio installed? I have a server that functions as a license manager for another software. Upon investigation of a high RAM usage alert, I found that the sqlservr.exe process is taking up almost 2 GB of RAM.

I looked through the program menu and found that the configuration manager was installed, otherwise, it is pretty bare bones. I clicked on properties of the EXE file and found 10.50.1600.1, but there is no place that I've found that states whether it is Express, Dev, STN, ENT, etc.

If I had to guess, this is an express edition, but I wanted to know if there is an obvious tell-tale sign.

Update: @Bob - The file tells me what I know, not the edition.

@valo - I get the following error when I run that command and I did verify named pipes was enabled:

HResult 0x35, Level 16, State 1 Named Pipes Provider: Could not open a connection to SQL Server [53]. Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A network-related or instance->specific error has occurred while establishing a connection to SQL Server. Server is not >found or not accessible. Check if instance name is correct and if SQL Server is configured >to allow remote connections. For more information see SQL Server Books Online.. Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired.

@thomas - I noticed the Stock Keeping Unit Name before I asked the question, but that seemed too easy, I guess my initial suspicion was correct.

Improve this question

4 Answers 4

Reset to default
19

This can be done through WMI (accessed through PowerShell in my below example). All I'm doing is looking at a property ("SKUNAME") of the SQL Server service, via the SqlServiceAdvancedProperty class. Note, there are a few environment-specific variables that would need to be set accordingly at the top of the code.

$ComputerName = "YourComputerName"
$ServiceName = 'YourEngineServiceName'
$PropertyName = "SKUNAME"

# retrieve the most current version of the ComputerManagement namespace
#
$ComputerManagementNamespace =
    (Get-WmiObject -ComputerName $ComputerName -Namespace "root\microsoft\sqlserver" -Class "__NAMESPACE" |
        Where-Object {$_.Name -like "ComputerManagement*"} |
        Select-Object Name |
        Sort-Object Name -Descending |
        Select-Object -First 1).Name
 
if ($ComputerManagementNamespace -eq $null) {
    Write-Error "ComputerManagement namespace not found"
}
else {
    $ComputerManagementNamespace = "root\microsoft\sqlserver\" + $ComputerManagementNamespace
} 

# get the property and its value
#
Get-WmiObject -ComputerName $ComputerName -Namespace $ComputerManagementNamespace -Class "SqlServiceAdvancedProperty" |
    Where-Object {
        $_.ServiceName -eq $ServiceName -and
        $_.PropertyName -eq $PropertyName
    } |
    Select-Object @{Name = "ComputerName"; Expression = { $ComputerName }},
        ServiceName,
        @{Name = "PropertyValue"; Expression = {
            if ($_.PropertyValueType -eq 0) {
                $_.PropertyStrValue
            }
            else {
                $_.PropertyNumValue
            }
        }}

Likewise, this same information can be found directly in the SQL Server Configuration Manager tool. Once you open it up, right-click on your SQL Server service and go into Properties. Then click on the Advanced tab and look at the Stock Keeping Unit Name key. There will you find what edition you are using.

Improve this answer
1
  • Using the SQL Server Configuration Manager tool did the trick for me. Instead of using the Stock Keeping Unit Name (which just said "Express Edition"), I cross-referenced the File Version with this table: sqlserverbuilds.blogspot.com
    – velkoon
    Commented Apr 15, 2021 at 20:21
23

You can try the command line tool:

C:\>sqlcmd -S [SERVER] -d [DB_NAME] -E -Q "SELECT @@VERSION"
Improve this answer
19

If the SQL Server service is running (which based on your description, it is) check the SQL Server error log. You don't need SSMS installed to do this as it is just a text file. File location may vary depending on how it was installed, but Books Online shows the default location here.

The first entry in the log should contain the version/edition information.

Edit: Despite the comment in the question above, this most certainly does tell you the edition:

2014-06-08 00:05:00.050 spid158      Microsoft SQL Server 2012 (SP1) - 11.0.3381.0 (X64) 

Aug 23 2013 20:08:13 

Copyright (c) Microsoft Corporation

Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
Improve this answer
13

Quick Answer

Just run the following:

{InstanceHome}\MSSQL\Binn\sqlservr.exe -v [-s InstanceName]

NOTE ABOUT SQL Server Express LocalDB: LocalDB can only be determined by the
existence of the following folder:

C:\Program Files\Microsoft SQL Server\{SQL Server Version: 110, 120, etc}\LocalDB\Binn

Running "sqlserver.exe -v" doesn't help as it reports as being "Developer Edition", and
searching the Registry it reports as being "Express Edition".

This method always works, unlike the other solutions which won't work when:

  • you don't want to write a program (for example, PowerShell)?
  • the Client Tools haven't been installed (required to use SQLCMD.EXE)?
  • There is either no SQL Server log file, or there is no log file that contains any start/restart events?
  • SQL Server is not running (hence SELECT @@VERSION; is not an option)?

Example 1
If you only have a single Instance installed, you do not need to use the -s switch:

C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn>sqlservr.exe -v

Returns:

2015-08-01 11:40:11.63 Server      Logging to event log is disabled. Startup option '-v' is supplied, either from the registry or the command prompt.
2015-08-01 11:40:11.63 Server      Microsoft SQL Server 2012 - 11.0.5343.0 (X64)
        May  4 2015 19:11:32
        Copyright (c) Microsoft Corporation
        Developer Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )

2015-08-01 11:40:11.63 Server      (c) Microsoft Corporation.
2015-08-01 11:40:11.63 Server      All rights reserved.
2015-08-01 11:40:11.63 Server      Server process ID is 3824.
2015-08-01 11:40:11.63 Server      System Manufacturer: '{CompanyName}', System Model: '{ModelNumber}'.
2015-08-01 11:40:11.63 Server      Authentication mode is MIXED.
2015-08-01 11:40:11.63 Server      The service account is '{AccountName}'. This is an informational message; no user action is required.
2015-08-01 11:40:11.63 Server      SQL Server shutdown has been initiated

Example 2
If you have multiple Instances installed on the machine, you need to also use the -s switch to specify which Instance you are interested in, else it can intermix version info:

C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS2014\MSSQL\Binn>sqlservr.exe -v
       -s SQLEXPRESS2014

Returns:

2015-08-01 12:01:13.13 Server      Logging to event log is disabled. Startup option '-v' is supplied, either from the registry or the command prompt.
2015-08-01 12:01:13.13 Server      Microsoft SQL Server 2014 - 12.0.4213.0 (X64)
        Jun  9 2015 12:06:16
        Copyright (c) Microsoft Corporation
        Express Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )

2015-08-01 12:01:13.13 Server      UTC adjustment: -4:00
2015-08-01 12:01:13.13 Server      (c) Microsoft Corporation.
2015-08-01 12:01:13.13 Server      All rights reserved.
2015-08-01 12:01:13.13 Server      Server process ID is 1712.
2015-08-01 12:01:13.13 Server      System Manufacturer: '{CompanyName}', System Model: '{ModelNumber}'.
2015-08-01 12:01:13.13 Server      Authentication mode is WINDOWS-ONLY.
2015-08-01 12:01:13.13 Server      The service account is '{AccountName}'. This is an informational message; no user action is required.
2015-08-01 12:01:13.13 Server      SQL Server shutdown has been initiated

Additional Information

The quickest, easiest, and most reliable way to determine version information of a program is simply to request that information from the program. Most programs (*.COM and *.EXE) have a command-line switch / flag / parameter / option / dealy-ma-bob that will display help and/or version information.

Depending on the program itself, the command-line switch will be prefixed with one of the following:

  • [nothing / nada]
  • -
  • --
  • /

And, again depending on the program itself, the command-line switch will be one of the following:

  • Most common:
    • v
    • version
  • Sometimes included in the general help info:
    • ?
    • h
    • help

{ SQL Server examples shown above }

The command-line utility SQLCMD.EXE, on the other hand, uses the -v switch to pass variable values into the SQL script, so it only displays version info in its general info display:

C:\>sqlcmd /?
Microsoft (R) SQL Server Command Line Tool
Version 11.0.2100.60 NT x64
Copyright (c) 2012 Microsoft. All rights reserved.

usage: Sqlcmd            [-U login id]          [-P password]
  [-S server]            [-H hostname]          [-E trusted connection]
  ...
  [-v var = "value"...]  ...
  ...
  [-? show syntax summary]

NotePad, however, only has a very limited set of command-line switches, and none of them display version info.

Alternative, no-fuss methods:

  • Check the registry:

    Key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\{VersionNumber}\Tools\Setup
    Name: Edition

    • If you know the version number (e.g. SQL Server 2012 = 110, SQL Server 2014 = 120, etc), you can the Edition for it specifically by running the following at a command prompt:

      REG QUERY "HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\{VersionNumber}\Tools\Setup" /v /e /f Edition

      Returns one entry similar to the following:

      HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\110\Tools\Setup
    Edition    REG_SZ    Developer Edition

    • If you what to see the Edition for whatever happens to be installed, run the following at a command prompt:

      REG QUERY "HKLM\SOFTWARE\Microsoft\Microsoft SQL Server" /s /v /e /f Edition

      Returns one or more entries similar to the one shown above and the following:

      HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11E.LOCALDB\Setup
    Edition    REG_SZ    Express Edition

  • Check the installation log:

    C:\Program Files\Microsoft SQL Server\{SQL Server Version: 110, 120, etc}\Setup Bootstrap\Log\{date_time}\Detail.txt

    For the string "IsExpressSku":

    (03) 2014-09-05 16:53:44 SQLEngine: --EffectiveProperties: IsExpressSku = True

Improve this answer
0

Not the answer you're looking for? Browse other questions tagged or ask your own question.