Hi Experts,
I made an XDS policy which is used to filter the custom dimensions in D365,
The query used for the policy uses a table which joins to a Custom tempdb table. An XDS method was created on the temp table with refresh frequency set as PerInvocation.
Issue: The policy works as expected in D365 DEV boxes and not in TEST instance? does any one had similar issue ? or have any suggestions pls on the possible cause?
The setup in DEV and test is same
The code or objects are same in dev and test
the only difference i found was on the query creation, in dev the query uses tempdb and in TEST it does not. below is the example
Query in DEV:
SELECT T1.RECID FROM DIMENSIONFINANCIALTAG T1
WHERE (T1.PARTITION=5637144576)
AND EXISTS (SELECT 'x' FROM tempdb."DBO".t20085_B9185767C2C7463E8B51EF747CF631E4 T2 WHERE ((T2.PARTITION=5637144576) AND (((T1.RECID=T2.DIMFINANCIALTAGRECID) AND (T1.FINANCIALTAGCATEGORY=T2.FINANCIALTAGCATEGORY))
AND (T1.VALUE=T2.VALUE)))) ORDER BY T1.FINANCIALTAGCATEGORY,T1.VALUE
Query in TEST:
SELECT T1.RECID FROM DIMENSIONFINANCIALTAG T1
WHERE (T1.PARTITION=5637144576)
AND EXISTS (SELECT 'x' FROM "DBO".t18113_C08B372B81D74728A61218434319219B T2 WHERE ((T2.PARTITION=5637144576) AND (((T1.RECID=T2.DIMFINANCIALTAGRECID)
AND (T1.FINANCIALTAGCATEGORY=T2.FINANCIALTAGCATEGORY))
AND (T1.VALUE=T2.VALUE)))) ORDER BY T1.FINANCIALTAGCATEGORY,T1.VALUE
Please note that when i set the refreshfrequency to Persession, it worked earlier in both DEV and TEST. but the business requirement was to have it done for perinvocation.
