SQL Server 2017 - Check The Advanced Analytics Status

Check the Advanced Analytics status using SERVERPROPERTY In SQL Server 2017/2016

In this blog, we will see how to check the Advanced Analytics status using "SERVERPROPERTY".


"SERVERPROPERTY" is a System-defined function used to return the SQL Server Instance Information.


  1. SERVERPROPERTY ('propertyname')


Use ISADVANCEDANALYTICSINSTALLED property to check the Advanced Analytics status.
 0  Advanced Analytics was not installed
 1  Advanced Analytics was installed


  1. declare @IsAdvancedAnalyticsInstalled as sql_variant  
  2. set @IsAdvancedAnalyticsInstalled = (select SERVERPROPERTY('IsAdvancedAnalyticsInstalled'))  
  3. select @IsAdvancedAnalyticsInstalled as IsAdvancedAnalyticsInstalled ,  
  4. case @IsAdvancedAnalyticsInstalled  
  5. when 0 then 'Advanced Analytics was not installed'  
  6. when 1 then 'Advanced Analytics was installed'  
  7. else 'Invalid Input'  
  8. end as 'AdvancedAnalyticsInstalled Status'  



Check the Advanced Analytics status using PowerShell

You can use Windows PowerShell to invoke SQL command on a reachable server within the network using Invoke-Sqlcmd cmdlet, as the following.
  • Open Windows PowerShell as Administrator.
  • Type the Invoke-Sqlcmd with the below parameters.

    • -query: the SQL query that you need to run on the remote server.
    • -ServerInstance: the SQL server instance name.
    • -Username: the username that has sufficient permission to access and execute SQL query on the remote server.
    • -Password: the password of the elevated user.

      PS SQLSERVER:\> Invoke-Sqlcmd -query "select SERVERPROPERTY('IsAdvancedAnalyticsInstalled') as 'IsAdvancedAnalyticsInstalled'" -ServerInstance "epm\epmdb" -Username sa -Password *****

Applies to

  • SQL Server 2016.
  • SQL Server 2017.


See Also