Overcome varchar(8000) or nvarchar(4000) limit in OpenQuery to SSAS
OpenQuery has query string length limit to varchar(8000) or nvarchar(4000)
To overcome this limit you can use the below query:
DECLARE @mdx AS VARCHAR(MAX) SET @mdx=' <Your MDX longer than varchar(8000)> ' SELECT len(@mdx) AS LENGTH_OF_MDX Exec (@mdx) At <Your Linked Server>;
to use dynamic SQL, you can use the below tempalte:
BEGIN TRY DECLARE @SQL nvarchar(max) SET @SQL = 'Exec (''' + @MDX + ''') At ' + @CubeLocation EXEC sp_executesql @SQL END TRY BEGIN CATCH SET @ErrorMessage = 'Query execution failed!' RAISERROR(@ErrorMessage , 16, 1) WITH NOWAIT END CATCH
However, although the above can bypass the varchar(8000) limit, it brings another side issue. If you MDX returns a empty set, the SQL Server connection will be dropped. And even worse, this error cannot be captured by Try Catch block. Hence, if you need to implement this logic in your SQL stored procedure, you need to implemented MDX Count function to avoid returning no result set. For instance, count the result-set first and if count = 0, there will be no need to go ahead with the query. Here is the code sample for a function to return the row count of your MDX result set:
</pre> WITH SET myset AS <Your SET Definition> Member [Measures].[cnt] AS myset.count SELECT [Measures].[cnt] ON columns FROM <Your Cube> WHERE ( <Your Slicers> ) <pre>
You can then wrap the above query in dynamic SQL which uses OpenQuery and linked server.
Hello,
When using” exec at” how to store result in temporary file?
Thank you.
There’s a viable alternative to this on codeplex, the OLAPExtensions CLR. It’s really helped simplify some of our more complicated requirements. No linked servers, no 8000 mdx character limit, no double querying to check for result, and values in result are strongly typed. https://olapextensions.codeplex.com/
Yes, this is a good one!
Another possibility is to utilise the new SSIS 2012 Data Feed components, docs can be found at: http://office.microsoft.com/en-us/publish-ssis-packages-as-odata-feed-sources-HA104079177.aspx. It is harder to set up but provides lots of potentials
Very interesting George. I think that would be very useful as a report source. I’ve seen several report source procedures that were essentially an etl which returns a result.
At first glance, I don’t believe the feed would be nearly as flexible as the OLAP linked server or the OLAPExtensions CLR. Typically, the package saves the destination and source column metadata. I’d think that would mean 1 package per query. Depending on the environment, that may or may not be feasible.
INSERT INTO
Exec (@mdx) At
– doesn’t work (unlike open query)
do you know how to achieve this ?