linked server for ssas

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Analysis Services » linked server for ssas

linked server for ssas

Rate Topic

Display Mode

Topic Options

Author

Message

miriyalasrihari

Posted Monday, January 17, 2011 1:06 AM

Valued Member

Group: General Forum Members
Last Login: Thursday, June 14, 2012 11:52 PM
Points: 63, Visits: 257

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

SRIHARI(:~

Post Attachments

linked server.JPG (20 views, 114.84 KB)

Post #1048591

jm99

Posted Monday, January 17, 2011 12:23 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 3:58 PM
Points: 114, Visits: 276

Create the linked server using sp_addlinkedserver

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>' ;

For example:
exec sp_addlinkedserver @server='My SSAS Server', @provider='MSOLAP', @datasrc'=localhost', @catalog='MySSASDatabase';

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

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.

Post #1048903

miriyalasrihari

Posted Monday, January 17, 2011 9:10 PM

Valued Member

Group: General Forum Members
Last Login: Thursday, June 14, 2012 11:52 PM
Points: 63, Visits: 257

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

SRIHARI(:~

Post Attachments

err.JPG (35 views, 41.73 KB)

Post #1049032

Satish Nagaraja

Posted Tuesday, January 17, 2012 2:32 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, March 29, 2013 2:25 AM
Points: 164, Visits: 251

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 Smile

Post #1237060

jm99

Posted Tuesday, January 17, 2012 7:24 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 3:58 PM
Points: 114, Visits: 276

Connections to SSAS must be made by integrated security.

Configure all connections through your SSAS to use their own credentials

exec sp_addlinkedsrvlogin @rmtsrvname = 'MySSASLinkedServerName', @useself = 'TRUE'

Post #1237209

bijupchandran

Posted Sunday, September 02, 2012 11:26 AM

Forum Newbie

Group: General Forum Members
Last Login: Today @ 10:26 AM
Points: 1, Visits: 2

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 Tongue

Post #1353254

rajiv 66419

Posted Monday, September 03, 2012 1:03 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:05 AM
Points: 22, Visits: 84

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.

Post #1353362

« Prev Topic | Next Topic »

Permissions