Theo thanks for the script!
i got it to work on a regular basis on most of my servers that have SSAS, but I've got an exception on one SSAS database: the database was renamed at one point, so the ID is different from the name; the linked server can give us the name of the Database, which we assume is the same as the ID that the xmla script requires.
i end up getting this error:
Date4/16/2015 8:54:02 AM
LogJob History (Maint_SSAS Backup All SSAS Databases)
Step ID2
ServerDOM-SQL-PROD
Job NameMaint_SSAS Backup All SSAS Databases
Step NameDynamically Build XMLA commands Per Database
Duration00:04:16
Sql Severity17
Sql Message ID7412
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
Executed as user:MyDomain\dwhc. Could not execute statement on remote server 'SSASLocal'. [SQLSTATE 42000] (Error 7215) OLE DB provider "MSOLAP" for linked server "SSASLocal" returned message "Errors in the metadata manager. Either the database with the ID of 'ClaimTracking' does not exist in the server with the ID of 'DOM-SQL-PROD', or the user does not have permissions to access the object.". [SQLSTATE 01000] (Error 7412). The step failed.
so the script gets created for ClaimTracking in my case, but the ID the XMLA script should have is technically a different value than the name.
have you encountered this since you wrote your article? how did you tackle it?
the data from $SYSTEM.DBSCHEMA_CATALOGS doesn't have the id, would you know if a different system catalog has the id tied to the name, so the script could be modified?
Edit: i did my due diligence, and ran exec sp_tables_ex [SSASLocal] on the linked server, and then queries every $SYSTEM table, and visually poked around for the ID, but i had no luck:
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM TABLE_SCHEM.TABLE_NAME') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DBSCHEMA_CATALOGS') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DBSCHEMA_COLUMNS') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DBSCHEMA_PROVIDER_TYPES') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DBSCHEMA_TABLES') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_CALC_DEPENDENCY') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_COMMAND_OBJECTS') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_COMMANDS') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_CONNECTIONS') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_CSDL_METADATA') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_DB_CONNECTIONS') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_DIMENSION_STAT') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_ENUMERATORS') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_INSTANCES') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_JOBS') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_KEYWORDS') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_LITERALS') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_LOCKS') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_MASTER_KEY') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_MEMORYGRANT') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_MEMORYUSAGE') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_OBJECT_ACTIVITY') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_OBJECT_MEMORY_USAGE') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_PARTITION_DIMENSION_STAT') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_PARTITION_STAT') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_PERFORMANCE_COUNTERS') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_PROPERTIES') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_RESOURCE_POOLS') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_SCHEMA_ROWSETS') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_SESSIONS') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_STORAGE_TABLE_COLUMN_SEGMENTS') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_STORAGE_TABLE_COLUMNS') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_STORAGE_TABLES') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_TRACE_COLUMNS') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_TRACE_DEFINITION_PROVIDERINFO') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_TRACE_EVENT_CATEGORIES') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_TRACES') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_TRANSACTIONS') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DISCOVER_XEVENT_TRACE_DEFINITION') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DMSCHEMA_MINING_COLUMNS') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DMSCHEMA_MINING_FUNCTIONS') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DMSCHEMA_MINING_MODEL_CONTENT') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DMSCHEMA_MINING_MODEL_CONTENT_PMML') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DMSCHEMA_MINING_MODEL_XML') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DMSCHEMA_MINING_MODELS') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DMSCHEMA_MINING_SERVICE_PARAMETERS') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DMSCHEMA_MINING_SERVICES') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DMSCHEMA_MINING_STRUCTURE_COLUMNS') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.DMSCHEMA_MINING_STRUCTURES') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.MDSCHEMA_CUBES') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.MDSCHEMA_DIMENSIONS') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.MDSCHEMA_FUNCTIONS') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.MDSCHEMA_HIERARCHIES') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.MDSCHEMA_INPUT_DATASOURCES') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.MDSCHEMA_KPIS') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.MDSCHEMA_LEVELS') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.MDSCHEMA_MEASUREGROUP_DIMENSIONS') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.MDSCHEMA_MEASUREGROUPS') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.MDSCHEMA_MEASURES') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.MDSCHEMA_MEMBERS') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.MDSCHEMA_PROPERTIES') AS a
SELECT * FROM Openquery([SSASLocal], 'SELECT * FROM $SYSTEM.MDSCHEMA_SETS') AS a
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!