SQL Server 2005 OPENQUERY bug with linked Analysis Services server

It appears that there is a bug in the RTM release of SQL Server when dealing with a linked server to an Analysis Services server. When you issue a query that has a “WITH MEMBER“ or “WITH SET“ clause like the following simple query:

USE master
GO

/* Add new linked server */
EXEC sp_addlinkedserver
@server
='LINKED_OLAP', -- local SQL name given to the linked server
@srvproduct='', -- not used
@provider='MSOLAP.3', -- OLE DB provider (the .2 means the SQL2K version)
@datasrc='localhost', -- analysis server name (machine name)
@catalog='Adventure Works DW' -- default catalog/database

GO

SELECT * 
FROM OPENQUERY
(LINKED_OLAP,'WITH MEMBER Measures.Test AS ''1'' SELECT {Measures.Members,Measures.Test} ON COLUMNS FROM [Adventure Works]')

/* Remove any previous references to the linked server */
EXEC sp_dropserver 'LINKED_OLAP'

It returns the following error:

Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "MSOLAP.3" for linked server "LINKED_OLAP" reported an error. The provider did not give any information about the error.
Msg 7321, Level 16, State 2, Line 2
An error occurred while preparing the query "WITH MEMBER MEASURES.X AS '1' select {measures.memberS,MEASURES.X} on columns from [adventure works]" for execution against OLE DB provider "MSOLAP.3" for linked server "LINKED_OLAP".

Apparently this is fixed in SP1, but this may cause anyone that is using OPENQUERY some pain until SP1 comes out. In my experience, most people that are using OPENQUERY are doing so because they are trying to run non-trivial MDX statements. They are usually deriving some context from a relational database which often requires using WITH SET or WITH MEMBER clauses. So the fact that OPENQUERY does not currently work in SQL Server 2005 when running such a query would be a significant issue for some people.

So what are the options for people using a WITH clause inside an OPENQUERY function?

  1. Wait for SP1 (no word yet as to when that is due)
  2. Re-write your queries to make use of some of the new features in Analysis Services 2005

Option 2, while it will take more effort, is worth considering. The use of OPENQUERY for non-trivial queries, inherently requires the use of dynamic SQL as OPENQUERY will not take a variable for the query parameter. These things can be a bit of a maintenance nightmare. Quotes all have to be doubled up in order to escape them and the string concatenations make the queries quite difficult to read.

It is possible that through the use of .Net Functions and “Stored Procedures” within Analysis Services that a better solution than the OPENQUERY one could be developed. One that would be easier to maintain and possibly even better performing (cutting out the linked server layer would cut out a level of indirection)

Print | posted on Saturday, January 14, 2006 6:39 PM