Friday, March 30, 2012

OPENQUERY on AS 2005 produces different results than AS 2000

I have an Analysis Server set up as a linked server I want to pass a query to from SQL. However, the number of columns returned differs. My query is as follows:

With Member Measures.PeerGroup As 'Model2CustomerRiskClass.currentmember.parent.parent.uniquename '

select { Measures.[PeerGroup], Measures.[baseamt], Measures.[Count]} on
columns,
{nonEmptyCrossjoin(Model2CustomerRiskClass.[Id].members ,[RecvPay].[RecvPay].members)}
Dimension PROPERTIES [Id].Name, [RecvPay].[recvpay].Name on rows
from Model2 where ([bookdate].&[2007].&[1].&[1])

Executing this directly against the AS (on both AS 2000 and 2005) produces the same results, five columns. The first two are unnamed, but contain the Id Name and RecvPay Name. The other three are PeerGroup, BaseAmt. and Count.

Now, if I execute this statement from Query Analyzer using Select * from OpenQuery (SQL 2000 and AS 2000), I get the same five columns named as follows:
[Model2CustomerRiskClass].[Name]
[RecvPay].[Name]
[Measures].[PeerGroup]
[Measures].[BaseAmt]
[Measures].[Count]

This is fine as my insert statement accepts five columns in that order. However, here's the same result from the same query in Management Studio (SQL 2005 and AS 2005 both SP2 CTP): The columns are:
[Model2CustomerRiskClass].[RiskClass].[Name]
[Model2CustomerRiskClass].[GroupId].[Name]
[Model2CustomerRiskClass].[Id].[Name]
[RecvPay].[RecvPay].[Name]
[Measures].[PeerGroup]
[Measures].[BaseAmt]
[Measures].[Count]

As you see, there are two extra columns. I do not want RiskClass and GroupId levels to show up. Can I get rid of them somehow? I cannot specify my SQL select by column names since, as you see, some column names are also different between the two. I need a query which returns the same columns in both 2000 and 2005. Is this possible?

Thanks,
Boris Zakharin, MCAD
Metavante Risk and Compliance

Any ideas at all? I am still having this issue and it needs to be resolved.

Thanks

No comments:

Post a Comment