Can you use "WITH MEMBER" in an openquery statement? Is there some sort of
setting that we need to change in order to allow the use of WITH MEMBER or
SET in openquery.

The error I get when trying to do this says:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSOLAP" for linked server "OLAP_SERVER" reported an
error. The provider did not give any information about the error.
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "
WITH MEMBER [Measures].[TEST] AS
........
SELECT [Dim].Members ON ROWS
,[Measures].[TEST] ON COLUMNS
FROM [CUBE 1]
" for execution against OLE DB provider "MSOLAP" for linked server
"LINKED_OLAP_SERVER".

The query runs fine if I run it from the mdx query window in Management
studio, and I can't see any difference between the two Trace profiles other
than the "ApplicationName" and the "ClientProcessID" columns up to the point
of failure.

The Query End record gives an error of 3238658057 and severity 3

Any help would be much appreciated.

Thanks in advance

Did you set the Quoted_Identifiers, ANSI_NULLS and ANSI_WARNINGS ON
before running the openquery.

HTH

Milind

Hi

Thanks for the response. I have had a look and the ANSI_NULLS and
ANSI_WARNINGS are ON by default for the query. If I try turn them off I get
the following error:
Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be
set for the connection. This ensures consistent query semantics. Enable these
options and then reissue your query.



"Milind" wrote:

This is a known issue in RTM -- the WITH clause causes the prepare operation
to fail. I believe it has been fixed in SP1.

Thanks,
Akshai
--
This posting is provided "AS IS" with no warranties, and confers no rights
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

"Mike" <Mike (AT) discussions (DOT) microsoft.com> wrote