Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Linked Server to Analysis Services 2005 gets Access denied.

42 views
Skip to first unread message

Renato

unread,
Nov 17, 2005, 7:17:28 PM11/17/05
to
Has anyone had any success with setting up a linked server or MSOLAP
oledb connection to SQL2005 Analysis Services??
My main issue is I am having trouble getting drill-through to work via
SQL Reporting services.
I would like to do this as a SQL query accessing AS via linked server
set up using "Microsoft OLEDB Provider for Analysis Services 9.0".

Please read on if you have had any experience with this area:....

If I run this from SQL I get the following error:

select * from openquery(wrypgpvmw06AS, '
SELECT
[Measures].[Sales] ON 0 FROM [Sales]
WHERE
([Date].[Year Periods].[Year].&[2005].&[2005-P02],
[Products].[ProductMPGHierarchy].[Product Origin Group].&[CV]
)')

------------------------------->
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSOLAP" for linked server "wrypgpvmw06AS" reported
an error. Access denied.
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "MSOLAP" for
linked server "wrypgpvmw06AS".

I am running the RTM version of 2005 Enterprise.
The query is running in a SQL window of the management studio running
directly on the server itself. I am logged under my own login, which is
a local administrator.
The server is both the SQL server and the Analysis Services server.
I am using the same domain account to run both SQL and AS and this
account is a local admin on the server.
Another thing is Analysis Services is getting and parsing the query
because if I introduce a syntax error I get the appropriate error
message.

Darren Gosbell

unread,
Nov 17, 2005, 7:26:58 PM11/17/05
to
If you are doing drilltrough in SSRS you must be linking to a separate
report. Have you looked into just getting SSRS to use SSAS as a data
source directly?

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1132273048....@g47g2000cwa.googlegroups.com>,
renat...@iprimus.com.au says...

Renato

unread,
Nov 17, 2005, 9:52:53 PM11/17/05
to
Thanks Darren.

It seems that SSRS does not understand the DRILLTHROUGH syntax of MDX
but I am just trying to use DRILLTHROUGH in the DMX pane with some
success.

I will keep this thread posted.

As an example of what I mean try something like this in SSRS:

DRILLTHROUGH
SELECT


FROM [Sales]
WHERE
([Date].[Year Periods].[Year].&[2005].&[2005-P02],
[Products].[ProductMPGHierarchy].[Product Origin Group].&[CV]

,[Measures].[Sales])

Renato

unread,
Nov 17, 2005, 10:02:37 PM11/17/05
to
Thanks Darren,

I did try to run this direct in SSRS connected to Analysis Services
data source:

DRILLTHROUGH
SELECT


FROM [Sales]
WHERE
([Date].[Year Periods].[Year].&[2005].&[2005-P02],
[Products].[ProductMPGHierarchy].[Product Origin Group].&[CV]

,[Measures].[Sales])


And get:

TITLE: Microsoft Visual Studio
------------------------------
Query preparation failed.
Failed to parse the query to detect if it is MDX or DMX.
(MDXQueryGenerator)

It seems SSRS wants to parse the MDX and does not understand the
drillthrough syntax.

Darren Gosbell

unread,
Nov 18, 2005, 11:38:19 PM11/18/05
to
I get the same error trying to do a drillthrough against the Adventure
Works database. I pasted a query that was working in SSMS into SSRS and
it failed with the same error. I even defining the query in an
expression in an attempt to delay the parsing, but this did not work
either. This is probably worth taking up with Microsoft.

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1132280697.6...@f14g2000cwb.googlegroups.com>,
renat...@iprimus.com.au says...

Deepak Puri

unread,
Nov 19, 2005, 3:36:41 AM11/19/05
to
How about trying the OLE DB option? I couldn't get the following AW
Drillthrough query to work with the RS 2005 Analysis Services Provider;
but it returned 8 records when I used OLE DB for OLAP 9.0 instead:

>>
Drillthrough
Select [Ship Date].[Calendar].[Calendar Year].&[2001] on columns,
[Customer].[Customer Geography].[State-Province].&[TAS]&[AU] on rows
from [Adventure Works]
where [Measures].[Internet Order Quantity]
>>


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Darren Gosbell

unread,
Nov 20, 2005, 7:06:59 PM11/20/05
to
Excellent suggestion Deepak! This works for my test example too.

This probably means that the issue is isolated to the ADO MD provider in
SSRS. As going down a layer to OLE DB works a treat.

Renato, I don't know if anyone from MS is tracking this thread so I
logged this issue in the product feedback site here:
http://lab.msdn.microsoft.com/ProductFeedback/viewFeedback.aspx?
feedbackId=FDBK40868

But for the time being Deepak's workaround is an excellent way to get
the results back from a drillthrough query, and really only results in
the minor inconvenience of having to set up one extra data source.

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <uBlYeRO7...@TK2MSFTNGP14.phx.gbl>,
deepa...@progressive.com says...

Renato

unread,
Nov 22, 2005, 8:00:01 AM11/22/05
to
Thanks Darren and Deepak for your suggestions and feedback to
Microsoft.

Tht means there are 2 options that work - the OLAP 9 provider and using
and DMX type query with the RS 2005 Analysis Services Provider. And
good to know about the product feedback site - I think Ill be making
use of that.

I have been using the DMX drillthru for a few days now, and it works a
treat.
I am going to try the OLAP 9 provider to see what this does.

(by the way - a bit off topic - do either of you find that RS can
develop some inconsistencies between the parameters specified at the
report level and those specified in the dataset. This has happened to
me when writing MDX queries and changing the parameters manually (e.g.
to provide dynamic defaults and more complex sets of allowable
values).)

Darren Gosbell

unread,
Nov 22, 2005, 7:49:15 PM11/22/05
to
In article <1132664401.5...@g43g2000cwa.googlegroups.com>,
renat...@iprimus.com.au says...

> (by the way - a bit off topic - do either of you find that RS can
> develop some inconsistencies between the parameters specified at the
> report level and those specified in the dataset. This has happened to
> me when writing MDX queries and changing the parameters manually (e.g.
> to provide dynamic defaults and more complex sets of allowable
> values).)
>

I have had instances where I was manipulating parameters in the dataset
and RS either created additional report parameters or lost some of the
links between the report and dataset parameters. This was using SQL data
sources, so I don't think this is an MDX related issue.

I just got into a habit of double checking the parameters if I did any
playing with them.

I do remember that I started noticing a pattern of what would cause
these issues, but this was over 6 months ago and I can't remember the
details anymore. What I do remember is thinking that in some
circumstances, what the report designer was doing would have made sense,
but in my case it was just annoying. :)

0 new messages