OpenRowset Query

Question

Post reply

OpenRowset Query

jacowess

Old Hand

Points: 303
More actions
January 19, 2009 at 3:40 am

#128052

Hi,

I am having some problems with an openquery.

Whenever I run this query:

select * from OpenRowset('MSOLAP','DATASOURCE=remoteserver; Initial Catalog=Cube;',

'select NonEmpty [Tests].[Fixed] on columns,

from Cube

where ([Test Date].[Date].&[2009-01-16T00:00:00]'

I get an error message:

Msg 15281, Level 16, State 1, Line 1

SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

I have run

sp_configure 'ad hoc distributed queries', 1;

GO

RECONFIGURE;

GO

And ad hoc queries are definitely enabled but I am still stuck with this error.

The same query works fin using OpenQuery.

Can anyone please shed some light or this or have you experienced same?

Many Thanks.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

Ummm... what happens when you run just the SELECT?

select NonEmpty [Tests].[Fixed] on columns,



from Cube

where ([Test Date].[Date].&[2009-01-16T00:00:00]

I'm thinking that you have some illegal text in the where clause and you're missing a parenthesis.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

MD-372277 SSCommitted Points: 1930 More actions · Answer 2

You need to enable advanced options before running sp_configure 'ad hoc distributed queries', 1;

sp_configure 'Show Advanced Options', 1;

Go

Reconfigure

Go

sp_configure 'ad hoc distributed queries', 1;

Go

Reconfigure

Go

If you are using SQL 2008 will also need to add a registery setting to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SQLEXPRESS\Providers\MSOLAP DisallowAdhocAccess = 0

Change MSSQL10.SQLEXPRESS to whatever your installation is.

Check the SQLNCLI10 key for details

jacowess Old Hand Points: 303 More actions · Answer 3

Thanks,

I can confirm that I am running sp_configure 'Show Advanced Options', 1; first before I do the reconfigure.

I can get the query to work from my local machine now but when I run it on a remote server which has the same linked server setup I get the following error.

Msg 7302, Level 16, State 1, Line 1

Cannot create an instance of OLE DB provider "MSOLAP" for linked server "(null)".

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

jacowess,

Look at my previous post and look at your original post. If that's what you're actually running, you have syntax errors in the code.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

jacowess Old Hand Points: 303 More actions · Answer 5

Thanks Jeff,

I can run the original query succesfully from my local instance. I have cut it down as suggested and i can still run it on my local instance. It's when moving to a remote server and running it on there that I get this error.

Msg 7302, Level 16, State 1, Line 1

Cannot create an instance of OLE DB provider "MSOLAP" for linked server "(null)".

Carlos E. Mosquera SSC Enthusiast Points: 101 More actions · Answer 6

Carlos E. Mosquera

SSC Enthusiast

Points: 101

April 1, 2009 at 11:17 am

#970090

We have same result (Cannot create an instance of OLE DB provider "MSOLAP" for linked server "MYLNKMOLAPSRV".) please help!!!

:crying:

Carlos E. Mosquera