Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

5 Answers 5

up vote 9 down vote accepted

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.

share|improve this answer
    
I updated my OP based on everyone's suggestions, I thank all of you who took the time to respond. –  Sean Perkins Jun 10 '14 at 19:58

You can try the command line tool:

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

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: )
share|improve this answer

Quick Answer

What do you do if:

  • 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)?

Just run the following:

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

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: 'TOSHIBA', System Model: 'Satellite L855'.
2015-08-01 11:40:11.63 Server      Authentication mode is MIXED.
2015-08-01 11:40:11.63 Server      The service account is 'Dali\Solomon'. 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: 'TOSHIBA', System Model: 'Satellite L855'.
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 'Dali\Solomon'. 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
      
    • 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
      
  • Check the installation log:

    C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\Log\20140905_164316\Detail.txt

    For the string "IsExpressSku":

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

share|improve this answer

Just Execute the query

select serverproperty('Edition')

to find out your SQL Server Edition

share|improve this answer
1  
I suspect your answer is attracting down votes because it only addresses a very small part of the question. –  Paul White Aug 1 at 11:42

protected by Paul White Aug 1 at 11:37

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?

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