3

I have a new SSAS 2016 tabular model (1200 compatibility level). I am trying to set up a SQL Agent job to process the model daily. However, when the job runs, it is throwing the following error:

XmlaException. The { text node at line 7, column 17 cannot appear inside the Command element (namespace urn:schemas-microsoft-com:xmla-analysis) under Envelope/Body/Execute. This element can only have text nodes containing white-space characters.

The command is:

{
  "refresh": {
     "type": "full",
     "objects": [
       {
         "database": "Finance"
       }
      ]
   }
}

This works ok when I execute it in SSMS, but fails when run via a SQL agent job.

Tony
  • 9,672
  • 3
  • 47
  • 75
cmn
  • 93
  • 2
  • 8
  • What step type are you using? Is it a "SQL Server Analysis Services Command". In my experience that needs to be XML, not JSON which is what you have above. How did you come up with that command? – Nick.Mc Nov 25 '16 at 01:26

3 Answers3

2

I have discovered what the problem was. It turned out that the SQL Server engine was version 2014, while SSAS was 2016. This is what was causing the problem. I was trying to process the 2016 SSAS tabular model using SQL 2014 SQL Agent. Once I realised this, I used the 2016 agent instead and the job is now working.

cmn
  • 93
  • 2
  • 8
0

It looks like the command you are attempting to execute is in TMSL (Tabular Model Scripting Languge) but SQL Agent is expecting XMLA.

This site describes how to get the equivalent command in XMLA http://biinsight.com/how-to-automate-ssas-tabular-model-processing/

mendosi
  • 2,001
  • 1
  • 12
  • 18
  • If I do that, I get the error "This command cannot be executed on database 'Finance' because it has been defined with StorageEngineUsed set to TabularMetadata. For databases in this mode, you must use Tabular APIs to administer the database" – cmn Nov 24 '16 at 20:37
  • Okay, sorry for the misinformation. I don't know how to do TMSL with SQL Server Agent. – mendosi Nov 24 '16 at 21:16
0

I was also facing the same problem because of conflict between Compatibility levels.... The best solution I found is to use proxy agent having the credentials of SSAS tabular mode and run as Type Sql Server Analysis Services Query and using proxy agent to run as

Asjad sheikh
  • 1