Search code examples
synchronizationssascube

Cube Sync issue in SSAS

Can I sync cube between two servers (Server B and Server C) by running the XMLA query on Server A?.... Currently I am getting an error :( . Please find below XMLA script and error output

XMLA Query:

<Synchronize xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Source>
<ConnectionString>Provider=MSOLAP.5;Data Source='+@ServerName+';Integrated Security=SSPI;Initial Catalog='+@DatabaseID+'</ConnectionString>
<Object>
    <DatabaseID>'+@DatabaseID+'</DatabaseID>
</Object>
</Source>
<SynchronizeSecurity>CopyAll</SynchronizeSecurity>
<ApplyCompression>true</ApplyCompression>
</Synchronize>

Error Message:

OLE DB provider "MSOLAP" for linked server "Server A" returned message "Errors in the OLE DB provider. The data source or location is not valid. Provide a valid server to which to connect.
Solution

  • Yes, this is possible. I got the issue.

    We can run our XMLA script on Server B from Server A by using remote query execution.

    Put whole XMLA script into variable @XMLA and @LinkedSyncServerName = Server B Before running this query test your linked server connections

    EXEC (''' + @XMLA + N''') AT [' + @LinkedSyncServerName + N']'