Home > BI, SQL Analysis Services, SQL Script > Overcome varchar(8000) or nvarchar(4000) limit in OpenQuery to SSAS

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.

Advertisements
  1. amine
    April 19, 2013 at 1:53 am

    Hello,
    When using” exec at” how to store result in temporary file?

    Thank you.

  2. kevin
    October 25, 2013 at 1:01 pm

    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/

    • October 29, 2013 at 10:36 am

      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

      • kevin
        November 2, 2013 at 12:40 pm

        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.

  3. sourav
    January 7, 2014 at 9:52 pm

    INSERT INTO
    Exec (@mdx) At

    – doesn’t work (unlike open query)

    do you know how to achieve this ?

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: