How to find Analysis Services Server Version

  • Article

 The below was put together by a colleague at Microsoft - Olga Liakhovich, and this information can be useful to you folks out there who may be looking for ways to find out the version of Analysis Services you are running in your environment.

 

Solutions

Sql Server Management Studio

Management Studio Object Explorer will show the server name, the build number, and the user name after you connect.

For SQL Server 2005:

  • 1. 9.00.1399 is RTM
  • 2. 9.00.2047 is SP1
  • 3. 9.00.3042 is SP2
  • 4. anything in between is a hotfix.

The latest post SP2 hotfix should be 32xx.

 

 PowerShell script

Enclosed below is a PowerShell script that will retrieve AS server version. To run it, first save in ps1 file (for example C:\scripts\ASVer.ps1) and then run a command:

Powershell.exe  C:\scripts\ASVer.ps1

 

## Add the AMO namespace

$loadInfo = [Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")

 

## Connect and get the edition of the local server

$connection = "localhost"

$server = New-Object Microsoft.AnalysisServices.Server

$server.connect($connection)

Write-Output ("`n`nServer: {0}`nEdition: {1}`nBuild: {2}`n`n" -f

    $server.Name, $server.Edition, $server.Version)

 

Result:

Server: WIN2K3R2EE

Edition: Developer

Build: 10.0.1075.23

XMLA discover command

Using ASCmd command line utility or SQL Server Management Studio you can send a XMLA discover command and filter for the DBMSVersion.

XMLA Commad:

<Discover xmlns='urn:schemas-microsoft-com:xml-analysis'>

  <RequestType>DISCOVER_PROPERTIES</RequestType>

  <Restrictions/>

  <Properties/>

</Discover>

 

Result:

<return xmlns="urn:schemas-microsoft-com:xml-analysis">

...

   <row>

      <PropertyName>DBMSVersion</PropertyName>

      <PropertyDescription>DBMSVersion</PropertyDescription>

      <PropertyType>string</PropertyType>

      <PropertyAccessType>Read</PropertyAccessType>

      <IsRequired>false</IsRequired>

      <Value>9.00.3207.00</Value>

    </row>

...

</return>

Or more short version:

<Discover xmlns='urn:schemas-microsoft-com:xml-analysis'>

  <RequestType>DISCOVER_PROPERTIES</RequestType>

  <Restrictions>

    <RestrictionList>

      <PropertyName>DBMSVersion</PropertyName>

    </RestrictionList>

  </Restrictions>

  <Properties>

    <PropertyList>

      <Content>Data</Content>

    </PropertyList>

  </Properties>

</Discover>

 

Result:

<return xmlns="urn:schemas-microsoft-com:xml-analysis">

  <root xmlns="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="https://www.w3.org/2001/XMLSchema">

    <row>

      <PropertyName>DBMSVersion</PropertyName>

      <PropertyDescription>DBMSVersion</PropertyDescription>

      <PropertyType>string</PropertyType>

      <PropertyAccessType>Read</PropertyAccessType>

      <IsRequired>false</IsRequired>

      <Value>9.00.3228.00</Value>

    </row>

  </root>

</return>

 

AMO stored procedures

Enclosed below is an AMO sproc that will retrieve the AS server version. Once you have an assembly you can deploy this assembly on to your server and use the Call statement

public static DataTable GetASVersion()

        {

                AMO.Server asServer = new                    Microsoft.AnalysisServices.Server();

                asServer.Connect(".");

 

                

                DataTable dtResult = new DataTable("dtResult");

                dtResult.Columns.Add("Server", typeof(String));

                dtResult.Columns.Add("AS Version", typeof(String));

                dtResult.Columns.Add("Build", typeof(String));

 

 

                DataRow rowResults = dtResult.NewRow();

                Object[] items = new Object[3];

                items[0] = asServer.Name;

                items[1] = asServer.Edition;

                items[2] = asServer.Version;

                rowResults.ItemArray = items;

                dtResult.Rows.Add(rowResults);

                rowResults.AcceptChanges();

                dtResult.AcceptChanges();

                return dtResult;          

        }

Result (in SQL Server Management Studio):