linked server for ssas

  • 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]

  • 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.

  • 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]

  • 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 🙂

  • 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]

  • 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

  • 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.

  • 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

  • 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

  • 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

  • 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

  • Same windows authentication I used.

  • 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

  • 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.

  • 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

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

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