March 24, 2016 at 2:53 pm
Hi-we've had some problems with switching the analysis server connection (by connection I mean the datawarehouse it processes from) and then not switching it back. Basically, we wind up processing from our QA datawarehouse.
I'd like to determine what connection the database is using and send an alert if it isn't the right one. Is there a way to determine the connection using a DMV? I went though the article below and other links but couldn't find any metadata/property that would give me what I want... any ideas? Thanks in advance
March 25, 2016 at 3:20 pm
I don't believe that there is...there is only a DMV that returns the names of all the data sources in a database, but not specifics about the connection strings of those data sources.
There may be a way to interrogate the XML source code of an SSAS database through the SMO in .NET and then parse the XML to determine what the data source details are...but I have never attempted to do that and therefore not 100% if it's possible or not.
March 31, 2016 at 8:01 am
You can get to it if you are able to install the ASSP assembly on your instance (https://asstoredprocedures.codeplex.com/). Then the following (or a variation of it) will return your connection string:
WITH MEMBER ConnectionString AS
ASSP.discoverXmlMetaDataSingleValue("ConnectionString", "\DataSource")
MEMBER ConnectionName as
ASSP.discoverXmlMetaDataSingleValue("Name", "\DataSource")
SELECT {ConnectionName,
ConnectionString}
ON 0
FROM [Your Cube]
March 31, 2016 at 8:05 am
Thank you to you both for the suggestions. I haven't made time to investigate these proposed solutions, but I'll let you know the results when I do.
Thanks,
Joe
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply