Code d'erreur 7321 lors de l'exécution d'une requête MDX à l'aide d'openquery

Salut J'ai écrit la requête MDX suivante qui récupère datatables d'un cube qui se trouve sur un server lié,

USE [BMS_Extracts] GO /****** Object: StoredProcedure [dbo].[usp_CQ_SnP_Quotes] Script Date: 12/17/2015 11:09:23 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER proc [dbo].[usp_CQ_SnP_Quotes] --'2016-Q4' ( @FisQuar varchar(20) ) As Declare @Mdx nvarchar(max) Declare @FisMonthparam varchar(20) Declare @FisMonthloop int select (FISC_MTH_ID) as Monthloop,concat(left(min(FISC_MTH_ID),4) ,'-M',right(min(FISC_MTH_ID),2))as Monthparam into tbl_CQ_snp_quotes from calendar where cast(FISC_QTR_ID as varchar(10)) = concat(left(@FisQuar,4),right(@FisQuar,1)) group by FISC_MTH_ID set @FisMonthloop = (select min(Monthloop) from tbl_CQ_snp_quotes) print @FisMonthloop while @FisMonthloop <= (select max(Monthloop) from tbl_CQ_snp_quotes) begin set @FisMonthparam = (select Monthparam from tbl_CQ_snp_quotes where Monthloop = @FisMonthloop ) print @FisMonthparam set @mdx= ' insert into CQ_SnP_Quotes select cast(cast([[Dimension - Account]].[Account ID]].[Account ID]].[MEMBER_CAPTION]]] as varchar(100)) as bigint) as [Account ID], cast([[Dimension - Account]].[Account Name]].[Account Name]].[MEMBER_CAPTION]]] as varchar(200)) as [Account Name], cast([[Dimension - Channel]].[Segment]].[Segment]].[MEMBER_CAPTION]]] as varchar(30)) as [Segment], cast(cast([[Dimension - Account Team]].[SR Badge]].[SR Badge]].[MEMBER_CAPTION]]] as varchar(100)) as bigint) as [SR Badge], cast(cast([[Dimension - Account Team]].[SR Mgr Badge]].[SR Mgr Badge]].[MEMBER_CAPTION]]] as varchar(100)) as bigint) as [SR Mgr Badge], cast([[Dimension - Account Team]].[SR Mgr Name]].[SR Mgr Name]].[MEMBER_CAPTION]]] as varchar(30)) as [SR Mgr Name], cast([[Dimension - Account Team]].[SR Name]].[SR Name]].[MEMBER_CAPTION]]] as varchar(30)) as [SR Name], cast(cast([[Dimension - Fiscal Calendar]].[Date]].[Date]].[MEMBER_CAPTION]]] as varchar(100)) as datetime) as [Date], cast([[Dimension - Fiscal Calendar]].[Fiscal Month]].[Fiscal Month]].[MEMBER_CAPTION]]] as varchar(30)) as [Fiscal Month], cast([[Dimension - Fiscal Calendar]].[Fiscal Quarter]].[Fiscal Quarter]].[MEMBER_CAPTION]]] as varchar(30)) as [Fiscal Quarter], cast([[Dimension - Fiscal Calendar]].[Fiscal Week]].[Fiscal Week]].[MEMBER_CAPTION]]] as varchar(30)) as [Fiscal Week], cast([[Dimension - Product]].[Product LOB]].[Product LOB]].[MEMBER_CAPTION]]] as varchar(50)) as [Product LOB], cast([[Dimension - Product]].[Product Brand]].[Product Brand]].[MEMBER_CAPTION]]] as varchar(50)) as [Product Brand], cast(cast([[Base - Quote Offering]].[Quote Num]].[Quote Num]].[MEMBER_CAPTION]]] as varchar(100)) as bigint) as [Quote Num], cast([[Base - Quote Offering]].[Converted Flag]].[Converted Flag]].[MEMBER_CAPTION]]] as varchar(30)) as [Converted Flag], cast([[Custom - Product SnP]].[SnP LOB Level 1]].[SnP LOB Level 1]].[MEMBER_CAPTION]]] as varchar(100)) as [SnP LOB Level 1], cast([[Custom - Product SnP]].[SnP LOB Level 2]].[SnP LOB Level 2]].[MEMBER_CAPTION]]] as varchar(100)) as [SnP LOB Level 2], cast([[Custom - Product SnP]].[SnP LOB Level 3]].[SnP LOB Level 3]].[MEMBER_CAPTION]]] as varchar(100)) as [SnP LOB Level 3], cast([[Custom - Product SnP]].[SnP LOB Level 4]].[SnP LOB Level 4]].[MEMBER_CAPTION]]] as varchar(100)) as [SnP LOB Level 4], cast([[Custom - Product SnP]].[SnP LOB Level 5]].[SnP LOB Level 5]].[MEMBER_CAPTION]]] as varchar(100)) as [SnP LOB Level 5], cast([[Custom - Product SnP]].[SnP LOB Level 6]].[SnP LOB Level 6]].[MEMBER_CAPTION]]] as varchar(100)) as [SnP LOB Level 6], cast(cast([[Measures]].[Margin Quoted SnP]]] as varchar(100)) as float) as [Margin Quoted SnP], cast(cast([[Measures]].[Revenue Converted SnP]]] as varchar(100)) as float) as [Revenue Converted SnP], cast(cast([[Measures]].[Revenue Quoted SnP]]] as varchar(100)) as float) as [Revenue Quoted SnP], cast(cast([[Measures]].[Units Converted SnP]]] as varchar(100)) as float) as [Units Converted SnP], cast(cast([[Measures]].[Units Quoted SnP]]] as varchar(100)) as float) as [Units Quoted SnP], cast(cast([[Measures]].[Margin Converted SnP]]] as varchar(100)) as float) as [Margin Converted SnP], cast(cast([[Measures]].[Margin Quoted SnP Displays]]] as varchar(100)) as float) as [Margin Quoted SnP Displays], cast(cast([[Measures]].[Units Quoted SnP Displays]]] as varchar(100)) as float) as [Units Quoted SnP Displays], cast(cast([[Measures]].[Revenue Converted SnP Displays]]] as varchar(100)) as float) as [Revenue Converted SnP Displays], cast(cast([[Measures]].[Revenue Quoted SnP Displays]]] as varchar(100)) as float) as [Revenue Quoted SnP Displays] from openquery([AUSDWGSPOLAP1.AUS.AMER.DELL.COM],'' SELECT NON EMPTY { [Measures].[Margin Quoted SnP], [Measures].[Revenue Converted SnP], [Measures].[Revenue Quoted SnP], [Measures].[Units Converted SnP], [Measures].[Units Quoted SnP], [Measures].[Margin Converted SnP], [Measures].[Margin Quoted SnP Displays], [Measures].[Units Quoted SnP Displays], [Measures].[Revenue Converted SnP Displays], [Measures].[Revenue Quoted SnP Displays] } ON COLUMNS, NON EMPTY { ([Dimension - Account].[Account ID].[Account ID].ALLMEMBERS * [Dimension - Account].[Account Name].[Account Name].ALLMEMBERS * [Dimension - Channel].[Segment].[Segment].ALLMEMBERS * [Dimension - Account Team].[SR Badge].[SR Badge].ALLMEMBERS * [Dimension - Account Team].[SR Mgr Badge].[SR Mgr Badge].ALLMEMBERS * [Dimension - Account Team].[SR Mgr Name].[SR Mgr Name].ALLMEMBERS * [Dimension - Account Team].[SR Name].[SR Name].ALLMEMBERS * [Dimension - Fiscal Calendar].[Date].[Date].ALLMEMBERS * [Dimension - Fiscal Calendar].[Fiscal Month].[Fiscal Month].ALLMEMBERS * [Dimension - Fiscal Calendar].[Fiscal Quarter].[Fiscal Quarter].ALLMEMBERS * [Dimension - Fiscal Calendar].[Fiscal Week].[Fiscal Week].ALLMEMBERS * [Dimension - Product].[Product LOB].[Product LOB].ALLMEMBERS * [Dimension - Product].[Product Brand].[Product Brand].ALLMEMBERS * [Base - Quote Offering].[Quote Num].[Quote Num].ALLMEMBERS * [Base - Quote Offering].[Converted Flag].[Converted Flag].ALLMEMBERS * [Custom - Product SnP].[SnP LOB Level 1].[SnP LOB Level 1].ALLMEMBERS * [Custom - Product SnP].[SnP LOB Level 2].[SnP LOB Level 2].ALLMEMBERS * [Custom - Product SnP].[SnP LOB Level 3].[SnP LOB Level 3].ALLMEMBERS * [Custom - Product SnP].[SnP LOB Level 4].[SnP LOB Level 4].ALLMEMBERS * [Custom - Product SnP].[SnP LOB Level 5].[SnP LOB Level 5].ALLMEMBERS * [Custom - Product SnP].[SnP LOB Level 6].[SnP LOB Level 6].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION ON ROWS FROM ( SELECT ( { [Dimension - Channel].[Segment].&[CBO LB Public], [Dimension - Channel].[Segment].&[CBO LB PvtEnt], [Dimension - Channel].[Segment].&[CBO MB Public], [Dimension - Channel].[Segment].&[CBO MB PvtEnt] } ) ON COLUMNS FROM ( SELECT ( { [Dimension - Geography].[Country].&[United States] } ) ON COLUMNS FROM ( SELECT ( { [Dimension - Fiscal Calendar].[Fiscal Month].&['+@FisMonthparam+'] } ) ON COLUMNS FROM [Global SnP Quotations]))) WHERE ( [Dimension - Geography].[Country].&[United States] ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS'')' Exec sp_executesql @mdx set @FisMonthloop = @FisMonthloop + 1 end drop table tbl_CQ_snp_quotes 

Cette requête a été exécutée avec succès avant mais maintenant elle a commencé à donner l'erreur suivante,

 OLE DB provider "MSOLAP" for linked server "AUSDWGSPOLAP1.AUS.AMER.DELL.COM" returned message "The XML for Analysis request timed out before it was completed.". Msg 7321, Level 16, State 2, Line 1 An error occurred while preparing the query " SELECT NON EMPTY { [Measures].[Margin Quoted SnP], [Measures].[Revenue Converted SnP], [Measures].[Revenue Quoted SnP], [Measures].[Units Converted SnP], [Measures].[Units Quoted SnP], [Measures].[Margin Converted SnP], [Measures].[Margin Quoted SnP Displays], [Measures].[Units Quoted SnP Displays], [Measures].[Revenue Converted SnP Displays], [Measures].[Revenue Quoted SnP Displays] } ON COLUMNS, NON EMPTY { ([Dimension - Account].[Account ID].[Account ID].ALLMEMBERS * [Dimension - Account].[Account Name].[Account Name].ALLMEMBERS * [Dimension - Channel].[Segment].[Segment].ALLMEMBERS * [Dimension - Account Team].[SR Badge].[SR Badge].ALLMEMBERS * [Dimension - Account Team].[SR Mgr Badge].[SR Mgr Badge].ALLMEMBERS * [Dimension - Account Team].[SR Mgr Name].[SR Mgr Name].ALLMEMBERS * [Dimension - Account Team].[SR Name].[SR Name].ALLMEMBERS * [Dimension - Fiscal Calendar].[Date].[Date].ALLMEMBERS * [Dimension - Fiscal Calendar].[Fiscal Month].[Fiscal Month].ALLMEMBERS * [Dimension - Fiscal Calendar].[Fiscal Quarter].[Fiscal Quarter].ALLMEMBERS * [Dimension - Fiscal Calendar].[Fiscal Week].[Fiscal Week].ALLMEMBERS * [Dimension - Product].[Product LOB].[Product LOB].ALLMEMBERS * [Dimension - Product].[Product Brand].[Product Brand].ALLMEMBERS * [Base - Quote Offering].[Quote Num].[Quote Num].ALLMEMBERS * [Base - Quote Offering].[Converted Flag].[Converted Flag].ALLMEMBERS * [Custom - Product SnP].[SnP LOB Level 1].[SnP LOB Level 1].ALLMEMBERS * [Custom - Product SnP].[SnP LOB Level 2].[SnP LOB Level 2].ALLMEMBERS * [Custom - Product SnP].[SnP LOB Level 3].[SnP LOB Level 3].ALLMEMBERS * [Custom - Product SnP].[SnP LOB Level 4].[SnP LOB Level 4].ALLMEMBERS * [Custom - Product SnP].[SnP LOB Level 5].[SnP LOB Level 5].ALLMEMBERS * [Custom - Product SnP].[SnP LOB Level 6].[SnP LOB Level 6].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION ON ROWS FROM ( SELECT ( { [Dimension - Channel].[Segment].&[CBO LB Public], [Dimension - Channel].[Segment].&[CBO LB PvtEnt], [Dimens... 

Je suis nouveau dans les requêtes MDX et je ne suis donc pas capable de savoir ce que signifie l'erreur. S'il vous plaît laissez-moi savoir comment résoudre l'erreur suivante.

On dirait un timeout pour moi. Pouvez-vous augmenter le timeout d'expiration de la requête pour les servers liés (et openquery) mentionnés ici? https://support.microsoft.com/en-us/kb/314530

configure 'remote query timeout', 0
go
reconfigure with override
go

C'est une alternative plutôt qu'une réponse directe à votre question.

Au lieu d'utiliser OPENQUERY essayez la solution CLR suivante: https://olapextensions.codeplex.com/

Nous avions beaucoup de OPENQUERY basé sur mdx et avons commencé à tout déplacer sur cette procédure stockée CLR. Les avantages sont:

  • Aucun server lié n'est nécessaire
  • Aucun problème avec les longueurs de string
  • Beaucoup plus facile de conversions de type de données.