Querying [$SYSTEM].DBSCHEMA_TABLES through SQL Linked Server?

• Eric Mamet

  SSChampion

  Points: 11728

  More actions

  January 16, 2014 at 4:24 am

  #303242

  I am trying to query the SSAS DMVs through a linked server and am not having much luck with the query syntax...

  The Linked Server works fine (as I can browse the catalog) but I get an error when trying the following query

  SELECT *

  FROM SSAS.[Self Service Reporting].[b]$SYSTEM[/b].DBSCHEMA_TABLES

  The error is An invalid schema or catalog was specified for the provider "MSOLAP" for linked server "SSAS".

  If I change the Schema name, the error changes...

  SELECT *

  FROM SSAS.[Self Service Reporting].[b]SYSTEM[/b].DBSCHEMA_TABLES

  The new error is An invalid schema or catalog was specified for the provider "MSOLAP" for linked server "SSAS"

  Any idea?

  Thanks

  Eric

• PB_BI

  SSCoach

  Points: 17463

  More actions

  January 16, 2014 at 4:45 am

  #1680590

  Have you tried using OPENQUERY?

  SELECT *

  FROM

  OPENQUERY (SSAS, 'Select * from [$SYSTEM].DBSCHEMA_TABLES')

  You shouldn't need to specify the catalog (which I'm assuming is [Self Service Reporting]) as you define that in the linked server.

  ---

  I'm on LinkedIn

• Eric Mamet

  SSChampion

  Points: 11728

  More actions

  January 16, 2014 at 5:26 am

  #1680605

  Wonderful!

  Thanks