I have been getting below error when I try to execute procedure after concatenated ID parameter in XMLA cube partition code.
OLE DB provider "MSOLAP" for linked server "SSAS" returned message "The JSON DDL request failed with the following error: Cannot de-serialize Partition. The JSON input is not properly formed. Check path 'createOrReplace.partition.source.query', line 12, position 126.;After parsing a value an unexpected character was encountered: 2. Path 'createOrReplace.partition.source.query', line 12, position 126..".
--Procedure
CREATE PROCEDURE dynamic_partitions(@ID AS VARCHAR(10)) AS
declare @xmla varchar(max) = '
{
"createOrReplace": {
"object": {
"database": "TabularProject59",
"table": "FactInternetSales",
"partition": "FactInternetSales_2015"
},
"partition": {
"name": "FactInternetSales_2015",
"source": {
"query": "SELECT [dbo].[FactInternetSales].* FROM [dbo].[FactInternetSales] where CAST(left(orderdatekey,4) AS VARCHAR(10))="'+@ID+',
"dataSource": "SqlServer H-SAIKRISHNAJSQLSERVER AdventureWorksDW2017"
},
"annotations": [
{
"name": "QueryEditorSerialization",
"value": "<?xml version=\"1.0\" encoding=\"UTF-16\"?><Gemini xmlns=\"QueryEditorSerialization\"><AnnotationContent><![CDATA[<RSQueryCommandText>SELECT [dbo].[FactInternetSales].* FROM [dbo].[FactInternetSales] where left(orderdatekey,4)=2010</RSQueryCommandText><RSQueryCommandType>Text</RSQueryCommandType><RSQueryDesignState></RSQueryDesignState>]]></AnnotationContent></Gemini>"
}
]
}
}
}'
EXEC (@xmla) AT SSAS;
----Procedure Execution
EXEC [dynamic_partitions] @ID='2015';