MDX Query above 8000 characters

Hi,

I have a MDX query which is of an aprox length of 10000 characters. I
have to execute the query from within the stored procedure in sql. To
run this query I use the openrowset method.

If the length of my query is less than 8000 characters my query
executes perfectly, but the moment it exceeds 8000 characters it stop
working. Please suggest a solution for the same.

Sample Code:

declare @.mdxqry varchar(8000)
declare @.SearchCond varchar(8000)

set @.SearchCond = @.SearchCond + '
[ProductsAccounts].CurrentMember.properties("AS Date") <= "' + @.TDate
+ '" '

set @.mdxqry = '''WITH ' +
'MEMBER [Measures].[Difference] as ''''[Measures].[Expected Interest
Amount] - [Measures].[Adjusted Interest]'''' ' +
'MEMBER [Measures].[Loan Closed within Report Period] as ' +
''iif(cdate([ProductsAccounts].CurrentMember.properties("Closed
Date")) < cdate("' + @.ToDate + '"), "Yes", "No")'' +
'MEMBER [Measures].[ClosedBeforeLastInstallment] as
''''iif([Measures].[Loan Closed Before Last Instal]=1, "Yes", "No")''''
' +
'SELECT ' +
'{[Measures].[Expected Interest Amount], [Measures].[Adjusted
Interest], [Measures].[Difference], ' +
'[Measures].[Zero Interest Transactions],
[Measures].[ClosedBeforeLastInstallment], ' +
'[Measures].[Loan Closed within Report Period]} ON 0, '

set @.mdxqry = @.mdxqry +
'{Filter([ProductsAccounts].[Account Id].Members, (' + @.SearchCond +
'))} on 2, ' +
@.BranchFilter +
'FROM InterestAnalysis'''

set @.mdxqry = 'SELECT a.* FROM
OpenRowset(''MSOLAP'',''DATASOURCE="SERVERNAME"; Initial
Catalog="DATABASENAME";'',' + @.mdxqry + ') as a'

exec(@.mdxqry)

I have already tried splitting my query into smalled chunks and
executing it, but still I face the same problem.

This is how I have Done it:

declare @.mdxqry1 varchar(8000)
declare @.mdxqry2 varchar(8000)
declare @.SearchCond varchar(8000)

set @.SearchCond = @.SearchCond + '
[ProductsAccounts].CurrentMember.properties("AS Date") <= "' + @.TDate
+ '" '

set @.mdxqry1 = '''WITH ' +
'MEMBER [Measures].[Difference] as ''''[Measures].[Expected Interest
Amount] - [Measures].[Adjusted Interest]'''' ' +
'MEMBER [Measures].[Loan Closed within Report Period] as ' +
''iif(cdate([ProductsAccounts].CurrentMember.properties("Closed
Date")) < cdate("' + @.ToDate + '"), "Yes", "No")'' +
'MEMBER [Measures].[ClosedBeforeLastInstallment] as
''''iif([Measures].[Loan Closed Before Last Instal]=1, "Yes", "No")''''
'

set @.mdxqry2 = 'SELECT ' +
'{[Measures].[Expected Interest Amount], [Measures].[Adjusted
Interest], [Measures].[Difference], ' +
'[Measures].[Zero Interest Transactions],
[Measures].[ClosedBeforeLastInstallment], ' +
'[Measures].[Loan Closed within Report Period]} ON 0, '

set @.mdxqry2 = @.mdxqry2 +
'{Filter([ProductsAccounts].[Account Id].Members, (' + @.SearchCond +
'))} on 2, ' +
@.BranchFilter +
'FROM InterestAnalysis'''

set @.mdxqry2 = 'SELECT a.* FROM
OpenRowset(''MSOLAP'',''DATASOURCE="SERVERNAME"; Initial
Catalog="DATABASENAME";'',' + @.mdxqry + ') as a'

exec(@.mdxqry1 + @.mdxqry2)

Thanks in Advance

Charuvarchar is limited to 8000 characters, but EXEC can take several varchar parameters concatenated together.

Try breaking @.mdxqry into several strings that you know will be below the 8k limit (@.mdxqry1, @.mdxqry2, ...@.mdxqryN). Then call it like this:

exec (@.mdxqry1 + @.mdxqry2 + ... @.mdxqryN)|||I have already tried breaking up the varchar into multiple variables but the same problem exists...Please see if there is any other solution...

Thanks
Charu|||I have already tried breaking up the varchar into multiple variables but the same problem exists...How? Not according to the code you posted.