linked server for ssas

Question

Post reply

linked server for ssas

miriyalasrihari

Ten Centuries

Points: 1225
More actions
January 17, 2011 at 1:06 am

#230261

hi,
any one please tell me about how to create linked server for ssas in
sql server management studio2008
in step by step.....!
Relational Database=Exper
SSAS DATABASE=Exper
cube=Exper
[font="Verdana"]SRIHARI(:~[/font]

Viewing 15 posts - 1 through 15 (of 16 total)

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

jm99 SSCommitted Points: 1740 More actions · Answer 1

Create the linked server using sp_addlinkedserver

[font="Courier New"]EXEC sp_addlinkedserver

@server='<LinkedServerName>', -- The name that you will call this new server

@srvproduct='',

@provider='MSOLAP',

@datasrc='<ASServer>', -- This is the name of the Analysis Services server.

@catalog='<ASDB>' ;[/font]

For example:

[font="Courier New"]exec sp_addlinkedserver @server='My SSAS Server', @provider='MSOLAP', @datasrc'=localhost', @catalog='MySSASDatabase';

select * from openquery([My SSAS Server], '<MDX query>') as a;[/font]

Will create a linked server [My SSAS Server], square brackets required due to spaces in the name, to the default SQL SSAS installation on localhost and default to the MySSASDatabase. The select statement will execute the MDX query, passed as a string, on the SSAS linked server returning a flat table.

miriyalasrihari Ten Centuries Points: 1225 More actions · Answer 2

thanks for replay...

i am trying to create linked server as your's replay

linked server is created but unable to retrieve the data(connection failed)

i created the linked server like this

exec sp_addlinkedserver

@server='MOLAP', --name of the liked server

@srvproduct='ssas', -- anonymous name

@provider='MSOLAP', -- provider name

@datasrc='localhost', --source name(my DB and SSAS DB are in same sys.)

@catalog='sfd'; -- name of the analysis database

[font="Verdana"]SRIHARI(:~[/font]

Satish Nagaraja SSCrazy Points: 2632 More actions · Answer 3

Having the same issue any help would be great

EXEC sp_addlinkedserver

@server='TEST_OLAP', /* local SQL name given to the */

@srvproduct='MSOLAP', /* not used */

@provider='MSOLAP', /* OLE DB provider */

@datasrc='TESTSERVER:10500', /* analysis server name (machine name) */

@catalog='TESTDB' /* default catalog/database */

Also tried the steps below

EXEC master.dbo.sp_MSset_oledb_prop N'MSOLAP', N'AllowInProcess', 1

Cheer Satish 🙂

jm99 SSCommitted Points: 1740 More actions · Answer 4

Connections to SSAS must be made by integrated security.

Configure all connections through your SSAS to use their own credentials

[font="Courier New"]exec sp_addlinkedsrvlogin @rmtsrvname = 'MySSASLinkedServerName', @useself = 'TRUE'[/font]

bijupchandran SSC Journeyman Points: 75 More actions · Answer 5

EXEC sp_addlinkedserver

@server ='Link Server Name', -- //mention name for your link server--

@provider = 'SQLOLEDB',

@srvproduct = '',

@datasrc = 'instance name, --// eg: 192.168.1.100\sqlexpress--

@catalog = 'dbname' -- //use a db name form the above instance (its an optional field), use '')---

---//for adding login credential***---

EXEC sp_addlinkedsrvlogin

@rmtsrvname = 'Link Server Name', --// use the same name as your link server name---

@useself = 'false',

@locallogin = NULL,

@rmtuser = 'sa', --// sql user name-----

@rmtpassword = 'password' ---//your sql password---------

---***for testing the link server***----

sp_testlinkedserver N'Link Server Name' ---//add your link server name here------

Regards

Biju Chandran:-P

rajiv 66419 Valued Member Points: 62 More actions · Answer 6

if not exists(select * from sys.servers where name like 'Server1')

begin

exec sp_addlinkedserver 'Server1';

exec sp_addlinkedsrvlogin 'Server1','FALSE',NULL,'userName','Password';

end

First Line is for checking whether the server exists or not, if not making a linked server.

madhu.kowthalam Old Hand Points: 346 More actions · Answer 7

I was created linked server object to communicate between two SQL servers one is ssas tabular model another one is ssms.

After creating linked sever object using open query I executed sql query and the following error occurred.

"An error was encountered in the transport layer.".

"The peer prematurely closed the connection."

What is the cause for this can anyone help me out.

Thanks,

Madhusudhan

PB_BI SSCoach Points: 17463 More actions · Answer 8

madhu.kowthalam (5/16/2016)
I was created linked server object to communicate between two SQL servers one is ssas tabular model another one is ssms.
After creating linked sever object using open query I executed sql query and the following error occurred.
"An error was encountered in the transport layer.".
"The peer prematurely closed the connection."
What is the cause for this can anyone help me out.
Thanks,
Madhusudhan

According to this: http://blog.programmingsolution.net/ssas-2008/retrieve-ssas-cube-data-in-tabular-format-from-stored-procedure-using-linked-server/ (which I found by searching for the error using this website: www.google.com) this error is caused by connecting to the SQL instance using SQL credentials and then using the linked server. You need to use Windows credentials (that also have access to the SSAS instance/cube).

I'm on LinkedIn

madhu.kowthalam Old Hand Points: 346 More actions · Answer 9

Thank you Nice Article

I tried this,Remotely it is working fine.In my local machine I am facing same problem and I gave all credentials for my user still I am getting same error any other alternative ways for this issue.

Thanks,

Madhusudhan

PB_BI SSCoach Points: 17463 More actions · Answer 10

madhu.kowthalam (5/17/2016)
Thank you Nice Article
I tried this,Remotely it is working fine.In my local machine I am facing same problem and I gave all credentials for my user still I am getting same error any other alternative ways for this issue.
Thanks,
Madhusudhan

When you connect remotely are you using the same credentials and domain as your local machine?

I'm on LinkedIn

madhu.kowthalam Old Hand Points: 346 More actions · Answer 11

madhu.kowthalam

Old Hand

Points: 346

May 17, 2016 at 8:13 am

#1879542

Same windows authentication I used.

PB_BI SSCoach Points: 17463 More actions · Answer 12

madhu.kowthalam (5/17/2016)
Same windows authentication I used.

Do you have the same ADOMD drivers on your local machine?

I'm on LinkedIn

madhu.kowthalam Old Hand Points: 346 More actions · Answer 13

Hi,

We have the following versions

Remote desk top :

C:\Program Files\Microsoft Analysis Services\AS OLEDB --> 110 and 120 versions

Locally :

C:\Program Files\Microsoft Analysis Services\AS OLEDB -->120 version

Is this cause for this issue ?

Thanks,

Madhusudhan.

PB_BI SSCoach Points: 17463 More actions · Answer 14

madhu.kowthalam (5/18/2016)
Hi,
We have the following versions
Remote desk top :
C:\Program Files\Microsoft Analysis Services\AS OLEDB --> 110 and 120 versions
Locally :
C:\Program Files\Microsoft Analysis Services\AS OLEDB -->120 version
Is this cause for this issue ?
Thanks,
Madhusudhan.

It might be. What version of SSAS are you trying to connect to? When you look at the properties of the linked server on the SQL instance, what does it say in the "Provider" box? In the provider itself, is "AllowInProcess" checked?

I'm on LinkedIn