none
How to join tables on different servers? RRS feed

  • Question

  • I have  table dbo.storage.tx_trans  on server 1 and  table dbo.storage.tx_weight on server 2. They have joint columns, but how to join tables? I made connection to both servers in management studion and wrote such query:

    select * 

    from dbo.storage.tx_trans

    inner join dbo.storage.tx_weight

    on tx_weight.tr_num=tx_trans.car_num

    but, I received such message: "select prohibited for dbo.storage.tx_weight". How to cope with this?

    Monday, September 23, 2019 11:21 AM

Answers

All replies

    • Edited by SQLNeophyte Monday, September 23, 2019 2:44 PM
    Monday, September 23, 2019 12:27 PM
  • -- If you run the query on Server1
    SELECT t.*, w.*
    FROM dbo.storage.tx_trans AS t
    INNER JOIN Server2.db2.dbo.storage.tx_weight AS w
    ON w.tx_weight.tr_num = t.tx_trans.car_num

    A Fan of SSIS, SSRS and SSAS

    Monday, September 23, 2019 1:03 PM
  • Hey Guoxiong, tried your query, but received answer "incorrect syntax near server2". 
    Monday, September 23, 2019 2:14 PM
  • You need to create a linked server on your Server1.
    Monday, September 23, 2019 2:21 PM
    Moderator
  • How to create linked server on my Server1?
    Monday, September 23, 2019 2:53 PM
  • You may please refer: https://www.sqlshack.com/how-to-create-and-configure-a-linked-server-in-sql-server-management-studio/

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped.
     [Blog]  [LinkedIn]

    Monday, September 23, 2019 2:55 PM
  • Hey Guoxiong, tried your query, but received answer "incorrect syntax near server2". 

    As the others mentioned, you need to create a linked server. Try this:

    USE [master];
    GO
    -- Create a linked server
    EXEC master.dbo.sp_addlinkedserver
     @server = N'Your_Server_Name',
     @srvproduct = N'SQL Server';
    -- Add a linked server remote logins
    EXEC master.dbo.sp_addlinkedsrvlogin
     @rmtsrvname = N'Your_Server_Name',
     @rmtuser = N'SQL_UserName',
     @rmtpassword = 'Password_For_SQL_UserName';
    GO


    A Fan of SSIS, SSRS and SSAS

    Monday, September 23, 2019 3:13 PM
  • Hi pavlob,

     

    1.Please try to create  a linked server . For more information , please refer to Create Linked Servers (SQL Server Database Engine)

     

    2.execute the script

    ------execute it in Server1
    select * 
    from dbo.storage.tx_trans a
    inner join Server2.Dbname.dbo.storage.tx_weight b
    on b.tr_num=a.car_num
    	
    -----OR 
    	
    ;with cte as (
    SELECT *
    FROM OPENQUERY(Server2, 'Select * from dbo.storage.tx_weight')  
    )
    Select * from dbo.storage.tx_trans a
    inner join cte b on  b.tr_num=a.car_num
    


    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, September 24, 2019 7:46 AM
  • TITLE: Microsoft SQL Server Management Studio ------------------------------ A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.) (Microsoft SQL Server, Error: 10054) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=10054&LinkId=20476 ------------------------------ ADDITIONAL INFORMATION: An existing connection was forcibly closed by the remote host ------------------------------ BUTTONS: OK ------------------------------
    Tuesday, September 24, 2019 8:14 AM
  • I have user status in SQL database, not admin, is it possible create linked server without admin rights?
    Tuesday, September 24, 2019 8:21 AM
  • Linked Server - User Permissions

    A Fan of SSIS, SSRS and SSAS

    Tuesday, September 24, 2019 3:28 PM
  • Hi ,

     

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, September 25, 2019 9:15 AM
  • I tried your option, and had error, I pasted it higher, but I can do this one more: 

    TITLE: Microsoft SQL Server Management Studio ------------------------------ A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.) (Microsoft SQL Server, Error: 10054) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=10054&LinkId=20476 ------------------------------ ADDITIONAL INFORMATION: An existing connection was forcibly closed by the remote host

    Wednesday, September 25, 2019 12:34 PM
  • May be this related I have no admin rights, may be not
    Wednesday, September 25, 2019 12:35 PM
  • Did you try Create Linked Servers (SQL Server Database Engine)?

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Decompressor Thursday, September 26, 2019 11:03 AM
    Thursday, September 26, 2019 9:22 AM