There could be many reasons for this but in most cases, this misleads us, specifically if the MDX is running with Management Studio. You see that there is enough memory, and you are confident of the MDX, but why the Management Studio throws this?
Reason is simple. This Knowledge base article explains it;
“SSMS is a 32-bit process. Therefore, it is limited to 2 GB of memory. SSMS imposes an artificial limit on how much text that can be displayed per database field in the results window. This limit is 64 KB in "Grid" mode and 8 KB in "Text" mode. If the result set is too large, the memory that is required to display the query results may surpass the 2 GB limit of the SSMS process. Therefore, a large result set can cause the error that is mentioned in the "Symptoms" section.”
How do we make sure that MS throws the exception because of its limitations, not because of anything else. Simplest way is, get the code executed using another technique. It can be a .NET code using a provider that supports executing MDX against SSAS or could be a SSIS package. I used a simple SSIS package that has a connection to source and data reader destination. Here is the way;
- Create a new SSIS project and add an OLE DB Connection to SSAS.
- Make sure you add “Format=Tabular” for Extended Properties in Connection Manager.
- Add a data flow task and configure an OLE DB Source.
- Set the SSAS connection made to OLDE DB Source and add the MDX statement as SQL Command.
- Add a Data Reader destination and connect with the source.
- Execute and see.
If the code gets executed without any issue, it means you are hit by SSMS limitation not because of anything else.
No comments:
Post a Comment