none
JOIN across two servers without sp_addlinkedserver

    Question

  • QUESTION 1

    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?"

     

    QUESTION 2

    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’,

                                                     @srvproduct=''”,

                                                     @provider='SQLNCLI',

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

    http://blogs.msdn.com/sql_protocols/archive/2006/08/10/694657.aspx

    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','Server=xxx.xxx.x.xx;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','xxx.xxx.x.xx';'sa';'sa',
    'SELECT *
    FROM DService.dbo.device') AS a;

    Error:

    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:

     

    Select * FROM OPENROWSET('SQLOLEDB','DRIVER={SQL Server};SERVER=

    @ServerName;Uid=@LogIn;PWD=@Password',@DbName.dbo.UserProcessLogNew)

     

    -Supriya

    Monday, March 03, 2008 12:30 PM