How to Query Analysis Service Properties

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Analysis Services » How to Query Analysis Service Properties

How to Query Analysis Service Properties

Rate Topic

Display Mode

Topic Options

Author

Message

yzhang

Posted Friday, February 01, 2013 2:10 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, May 17, 2013 11:35 AM
Points: 4, Visits: 153

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!

Post #1414848

yzhang

Posted Monday, February 04, 2013 7:47 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, May 17, 2013 11:35 AM
Points: 4, Visits: 153

I found the answer. This works: BigGrin

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

Post #1415306

« Prev Topic | Next Topic »

Permissions