Problems with linked server to Analysis Services (SQL 2008)

Question

I have problem with creating linked server from SQL database to Analsis services. BOth services are running on same machine. Operating system is Windows 2008. I create linked server (I use windows authentication and I am administrator on AS): 

EXEC sp_addlinkedserver 
@server= 'OLAP_PRETOKI', 
@srvproduct = '', 
@provider='MSOLAP', 
@datasrc='localhost', 
@catalog='DWDatabase'

 But when I try to test connection I get error (in the event log) and in the error log/dump I get this:

2010-09-03 13:48:28.41 Server   Error: 17310, Severity: 20, State: 1.
2010-09-03 13:48:28.41 Server   A user request from the session with SPID 57 generated a fatal exception. SQL Server is terminating this session. Contact Product Support Services with the dump produced in the log directory.
2010-09-03 13:48:32.53 spid58   Open of fault log C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\log\exception.log failed.
2010-09-03 13:48:32.65 spid58   Using 'dbghelp.dll' version '4.0.5'
2010-09-03 13:48:32.66 spid58   SqlDumpExceptionHandler: Process 58 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.

2010-09-03 13:48:32.66 spid58   * *******************************************************************************

2010-09-03 13:48:32.66 spid58   *

2010-09-03 13:48:32.66 spid58   * BEGIN STACK DUMP:

2010-09-03 13:48:32.66 spid58   *  09/03/10 13:48:32 spid 58

2010-09-03 13:48:32.66 spid58   *

2010-09-03 13:48:32.66 spid58   *

2010-09-03 13:48:32.66 spid58   *  Exception Address = 698753BF Module(msolap100+000D53BF)

2010-09-03 13:48:32.66 spid58   *  Exception Code  = c0000005 EXCEPTION_ACCESS_VIOLATION

2010-09-03 13:48:32.66 spid58   *  Access Violation occurred reading address 00000004

2010-09-03 13:48:32.66 spid58   * Input Buffer 102 bytes -

2010-09-03 13:48:32.66 spid58   *       EXEC sp_testlinkedserver N'OLAP_PRETOKI'

2010-09-03 13:48:32.66 spid58   * 

Any Ideas what could be wrong or where can I look for better info?

Answer 1

Hi Matej,

I believe there is (a small) chance the Analysis services part are miscnfigured/underconfigured, can you check?

Another option (you did not list) a SP is missing, can you check this, too?

---

Arthur My Blog

Answer 2

What do you think with "analysis services" part are misconfigured? Analysis services is working fine, provider (MSOLAP) exists...

Also SP is not missing, and working with other linked server (not analysis services) is working fine.

Answer 3

Hey

Can you try :-

@provider='MSOLAP.4'
, 

Answer 4

What version of SQL Server you are using?

Please do EXEC sp_dropserver 'OLAP_PRETOKI' and then re-link, let us know what you got.

---

Arthur My Blog

Answer 5

After reading some hints i've done:

USE [master]
GO
EXEC master.dbo.sp_MSset_oledb_prop N'MSOLAP.4', N'AllowInProcess', 1
GO

and recreate dblink. After that I get new/old error - same as I get it if I use "MSOLAP" instead of "MSOLAP.4".

So If I set on provider "AllowInProcess = 1", I get error:

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

if I set on provider "AllowInProces = 0" 

Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "MSOLAP.4" for linked server "OLAP_PRETOKI" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 2
Cannot fetch a row from OLE DB provider "MSOLAP.4" for linked server "OLAP_PRETOKI".

after executing openQuery.

Answer 6

In the absence of any other suggestions/replies that may help you solve this, it might be worth opening a support ticket with Microsoft..

Answer 7

Please check the provider ‘MSOLAP’, and then ensure option ‘Allow InProcess’ has been selected.

http://msdn.microsoft.com/en-us/library/ms188095.aspx

 

Could you give another try with below follow link step by step:

http://social.technet.microsoft.com/Forums/en-US/sqlanalysisservices/thread/08f7d8cf-1986-4204-9e84-efbe6e43772f

EXEC sp_addlinkedserver

@server='TestForOpenQuery', -- the given name to the linked server

@srvproduct='',

@provider='MSOLAP',

@datasrc='localhost',--the instanse name of SSAS(machine/instanse) @catalog='Adventure Works DW 2008'

 

Hope this helps,

Raymond

---

Raymond Li - MSFT

Answer 8

As I allready wrote I've checked and tried different setting (AllowInProcess) - it doesn't help I just get different errors with different settings.
I've tried same with some other server (another installation of mssql/analysis services) and it went ok. The only differnece is operating system (on windows 2003 it works OK, on 2008 doesn't work) but I cant belive that operating system maters.

Answer 9

This might be helpful. http://sqlblogcasts.com/blogs/drjohn/archive/2009/03/28/kerberos-kills-large-mdx-queries-on-windows-server-2008-ssas.aspx

Answer 10

Matej Golob, did you get a solution?

I have a similar problem and I don't understand what is going on...

thanks in advance

Answer 11

I saw a similar problem with error 'A severe error occurred on the current command' when reading SSAS 2000 cubes that were generated by SAS.

I never found a solution for it.

---

Josh Ash