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".

What's the "SERVERPROPERTY"?

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

"SERVERPROPERTY" Syntax

1. SERVERPROPERTY ('propertyname')

ISADVANCEDANALYTICSINSTALLED

Use ISADVANCEDANALYTICSINSTALLED property to check the Advanced Analytics status.

 

0	Advanced Analytics was not installed
1	Advanced Analytics was installed

Example

declare @IsAdvancedAnalyticsInstalled as sql_variant
set @IsAdvancedAnalyticsInstalled = (select SERVERPROPERTY('IsAdvancedAnalyticsInstalled'))
select @IsAdvancedAnalyticsInstalled as IsAdvancedAnalyticsInstalled ,
case @IsAdvancedAnalyticsInstalled
when 0 then 'Advanced Analytics was not installed'
when 1 then 'Advanced Analytics was installed'
else 'Invalid Input'
end as 'AdvancedAnalyticsInstalled Status'

 

Output



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.

Reference

• How to get the SQL Server Instance Information using “SERVERPROPERTY”.

See Also

Download the "Get The Detailed SQL Server Information" script.