How to Query Analysis Service Properties

• yzhang

  SSC Enthusiast

  Points: 154

  More actions

  February 1, 2013 at 2:10 pm

  #288784

  Hi,

  Anyone knows a way to query the properties of an Analysis Service instance? I am trying to return the configured location of the backup files in SSAS. I tried to use the following query, but it keeps returning the values from the database engine. The "OLAPServer" was a guess, and I tried many different guessed names but couldn't make it work. Is there a way to find the return this by reading the registry or using MDX or T-SQL?

  declare @HkeyLocal nvarchar(18)

  declare @MSSqlServerRegPath nvarchar(31)

  declare @InstanceRegPath sysname

  declare @SetupRegPath sysname

  declare @RegPathParams sysname

  select @HkeyLocal=N'HKEY_LOCAL_MACHINE'

  -- Instance-based paths

  select @MSSqlServerRegPath=N'SOFTWARE\Microsoft\OLAPServer'

  select @InstanceRegPath=@MSSqlServerRegPath + N'\MSSQLServer'

  select @SetupRegPath=@MSSqlServerRegPath + N'\Setup'

  select @RegPathParams=@InstanceRegPath+'\Parameters'

  declare @SmoDefaultLog nvarchar(512)

  --exec master.dbo.xp_instance_regread @HkeyLocal, @InstanceRegPath, N'DefaultLog', @SmoDefaultLog OUTPUT

  exec master.dbo.xp_instance_regread @HkeyLocal,@SetupRegPath, N'SQLPath', @SmoDefaultLog OUTPUT

  PRINT @SmoDefaultLog

  Thanks you!

• yzhang

  SSC Enthusiast

  Points: 154

  More actions

  February 4, 2013 at 7:47 am

  #1583957

  I found the answer. This works::-D

  EXEC xp_regread 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\Microsoft SQL Server\MSAS10_50.MSSQLSERVER\SETUP','SQLPath'

  GO