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

Comments on this post

# re: SQL Server 2005 OPENQUERY bug with linked Analysis Services server

Requesting Gravatar...
This post saved my butt. Thanks Darren. Couldn't figure out why I was getting an error until I read this.
Left by furmangg on Jun 07, 2006 2:03 AM

# re: SQL Server 2005 OPENQUERY bug with linked Analysis Services server

Requesting Gravatar...
I have install the sp1 ,but I can't extract data from cube with the mdx yet.I can't understand what matter is it. It may be a bug with SSIS,but why Micrsoft not modify the bug?
Left by jordan on Dec 07, 2006 2:59 PM

# re: SQL Server 2005 OPENQUERY bug with linked Analysis Services server

Requesting Gravatar...
Como puedo hacer un insert con el linked server desde sql 2005 a oracle??? ...
porq los ejemplos que e encontrado solo son de consultas usando como referencia el linked server para traer datos ... y quisiera saber como puedo insertar datos ... para mejor comprension ... tengo que migrar unos datos de sql a oracle ...pero por cuestiones de seguridad no puede ser solo asi.
Left by Javier on Nov 25, 2008 8:06 AM

# SQL Server 2005 OPENQUERY bug with linked Analysis Services server

Requesting Gravatar...
thanks, works just fine.
Left by ido biger on Apr 19, 2010 5:30 PM

Your comment:

 (will show your gravatar)