question

SteenSchlterPersson-5935 avatar image
0 Votes"
SteenSchlterPersson-5935 asked LukasYu-msft answered

How to run multiple XMLA statements in the same script

Hi,

I'm working on a script to create around 50 roles for a Tabular Model, but have to troubles figuring out how I can run them all at once.

The script looks like below, just only with 2 roles as an example -

 {
 "createOrReplace": {
 "object": {
 "database": "Test",
 "role": "Group A"
 },
 "role": {
 "name": "Group A",
 "modelPermission": "read",
 "members": [
 {
 "memberName": "Domain\\xxx",
 "memberId": "S-......-73405"
 }
 ],
 "tablePermissions": [
 {
 "name": "Tablename",
 "filterExpression": "'TableName'[HomeDepartment] IN ({\"XX\",\"YY\"}) && 'TableName'[HomeUnit] IN ({\"ZZ\",\"WW\"})"
 }
 ]
 }
 }
 }


// Group B


"createOrReplace": {
"object": {
"database": "Test",
"role": "Group B"
},
"role": {
"name": "Group B",
"modelPermission": "read",
"members": [
{
"memberName": "Domain\\GGG",
"memberId": "S.......-53912"
},
{
"memberName": "Domain\\AAA",
"memberId": "S......-26483"
}
],
"tablePermissions": [
{
"name": "TableName",
"filterExpression": "'TableName'[HomeDepartment] IN ({\"HH\",\"KK\",\"TT\",\"FF\",\"MM\"}) && 'TableName'[HomeUnit] IN ({\"EE\"})"
}
]
}
}

I can run each "createOrReplace" section individually and it works fine and create the roles with permissions with out any problems but if I try to run the whole script (hit F5 in SSMS) I get the error -
Executing the query ...
The JSON DDL request failed with the following error: Additional text encountered after finished reading JSON content: {. Path '', line 30, position 1..
Run complete

I have then tried to add <Batch Transaction="true" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">; ..... </Batch> around the script, but then I get the error - The { text node at line 9, column 3 cannot appear inside the Batch element (namespace http://schemas.microsoft.com/analysisservices/2003/engine) under Envelope/Body/Execute/Command. This element can only have text nodes containing white-space characters.

I have also tried other combinations/suggestions, but none of them works.

My experience running XMLA scripts are fairly limited, so I hope some one can help on how I can fix this? I can of course always just run each individual step to get the roles created/updated but it would be nice just to be able to execute the whole script at once.

Regards
Steen

sql-server-analysis-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

DarrenGosbell-0123 avatar image
0 Votes"
DarrenGosbell-0123 answered SteenSchlterPersson-5935 commented

I think you might need to use the sequence command to run multiple operations in the one batch


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Darren,

Thanks for the feedback - it seems like it was the Sequence command I was after :-).

Thanks

/Steen

0 Votes 0 ·
LukasYu-msft avatar image
0 Votes"
LukasYu-msft answered

Glad you have resolve the issue, if you have further issue, feel free to post in the forum.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.