JOIN across two servers without sp_addlinkedserver



    Is it possible to run a JOIN on tables that are on seperate servers and youre not able to link the servers using "sp_addlinkedserver?"



    I try to run 


    "exec sp_addlinkedserver 'sqlserver1'"


    and I get the following:


    "Msg 15247, Level 16, State 1, Procedure sp_MSaddserver_internal, Line 36

    User does not have permission to perform this action."


    I try to then run

     "SELECT *

      FROM server1.database1.schema1.table1 AS a

      JOIN server2.database2.schema2.table2 AS b

      ON a.[column1] = b.[column2]"


    and I get the following:


    "OLE DB provider "SQLNCLI" for linked server "ITCMSARPT" returned message "Communication link failure".

    Msg 10054, Level 16, State 1, Line 0

    TCP Provider: An existing connection was forcibly closed by the remote host.

    Msg 18452, Level 14, State 1, Line 0

    Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection."


    It would appear the the server is now linked, yet it does't accept my credentials. I do have the ability to log into the server on a seperate query.


    Can anyone provide a solution to my issue?

    Sunday, February 10, 2008 5:18 AM

All replies

  • I guess the link servers are not added correctly. Go to SSMS -> server objects-> linked servers and make sure that both linked servers are added correctly.

    From the first error message it seems that you do not have permission to add a linked server. So I guess the linked servers are not added correctly.

    Adding a linked server is a two step process. First you need to add a linked server and then you need to set permissions to access the linked server. for example:

    EXEC sp_addlinkedserver @server=’LinkedServer’,



                                                     @datasrc=’SQLB’,--the data source

                                                     @provstr="Integrated Security=SSPI; "

    exec sp_addlinkedsrvlogin ‘LinkedServer’, 'true'”

    This article will give you a good understanding of how to configure linked servers.

    Sunday, February 10, 2008 7:09 AM
  • Have you considered OPENROWSET?

    Sunday, February 10, 2008 10:25 PM
  • Could anyone tell me how I am to use OPENROWSET.

    I executed the following query:


    SELECT a.*
    FROM OPENROWSET('SQLOLEDB',';Trusted_Connection=yes;',
         'SELECT *
          FROM DService.dbo.device') AS a;


    But it gives me this error:


    Msg 7416, Level 16, State 2, Line 1
    Access to the remote server is denied because no login-mapping exists


    Could someone please guide me here?


    Monday, March 03, 2008 9:19 AM

  • SELECT a.*
    FROM OPENROWSET('SQLOLEDB','';'sa';'sa',
    'SELECT *
    FROM DService.dbo.device') AS a;


    Msg 17, Level 16, State 1, Line 2
    [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

    What does this mean now?

    Monday, March 03, 2008 10:43 AM
  • Hii

    You can try this:






    Monday, March 03, 2008 12:30 PM