George's BI Blog

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.