Referencing MDX Query results in SQL Server

• Rip Murdock

  SSC Enthusiast

  Points: 146

  More actions

  August 31, 2005 at 7:02 pm

  #88943

  I’ve utilized Openrowset with Select…Into to dump the results of an MDX queries into a new table created on the fly.  I’ve attempted to reference the results using the INFORMATION_SCHEMA.COLUMNS table to build a dynamic T-SQL screen, T-SQL doesn’t recognize the column names that are automatically assigned to these columns because they contain “.”  The first four column names are [Date].[Year].[MEMBER_CAPTION],[Date].[Quarter].[MEMBER_CAPTION],[Date].[Month].[MEMBER_CAPTION],[Date].[Day].[MEMBER_CAPTION]

  When selecting the first of these, I get this error message.

   

  The column prefix 'Date.Year' does not match with a table name or alias name used in the query.

   

  If I enclose the column name in brackets, selecting [[Date].[Year].[MEMBER_CAPTION]]

  I get this error:

   

  Unclosed quotation mark before the character string 'MEMBER_CAPTION]

   

  I could also select these columns by ordinal position, but I do not believe that is possible in T-SQL.

   

  Any other suggestions?

• Vasc

  SSCarpal Tunnel

  Points: 4643

  More actions

  September 1, 2005 at 10:10 am

  #586919

  set quoted_identifier on

  declare @t table("[Date].[Year].[MEMBER_CAPTION]" varchar(1))

  insert into @t select '1'

  select "[Date].[Year].[MEMBER_CAPTION]" from @t

  ---

  Kindest Regards,

  Vasc

• Rip Murdock

  SSC Enthusiast

  Points: 146

  More actions

  September 2, 2005 at 3:02 pm

  #587416

  Thanks

• Raja Reddy-375473

  Newbie

  Points: 7

  More actions

  June 10, 2008 at 8:24 am

  #826690

  Thank You.