Joining tables from different servers

by

Without more details, it’s hard to give direct examples, but here is the basic idea:

First, outside of the stored procedure, the host server (the server the stored procedure will be on) has to know about the second server, including (possibly) login information.

On your main server, run the sp_addlinkedserver stored procedure. This only has to be done once:

exec sp_addlinkedserver @server="(your second server)";

If you need to provide login information to this second server (for example, the process can’t log in with the same credentials that are used in the initial database connection), do so with the sp_addlinkedsrvlogin stored proc:

exec sp_addlinkedsrvlogin @rmtsrvname="(your second server)",
@useself=false, 
@rmtuser="yourusername", 
@rmtpassword='yourpassword';

Then, in your stored procedure, you can specify tables on the second server:

SELECT table1.*
FROM table1
INNER JOIN [secondserver].[database].[schema].[table] AS table2 ON
    table1.joinfield = table2.joinfield

More Related Contents:

  1. Check if table exists in SQL Server
  2. How do I generate a random number for each row in a T-SQL select?
  3. Find index of last occurrence of a sub-string using T-SQL
  4. Regular Expressions in SQL Server servers?
  5. ORDER BY DATE showing NULLS first then most recent dates
  6. How to create the linked server for SQL Server 2008 where we have the database from 2000 and 2005
  7. How can I insert binary file data into a binary SQL field using a simple insert statement?
  8. Restore a SQL Server 2000 backup on SQL Server 2012
  9. How to execute SSIS package when a file is arrived at folder
  10. INSERT INTO @TABLE EXEC @query with SQL Server 2000
  11. How to convert SQL Server’s timestamp column to datetime format
  12. Multiple rows to one comma-separated value in Sql Server [duplicate]
  13. SQL Server add auto increment primary key to existing table
  14. SQL Server: Difference between PARTITION BY and GROUP BY
  15. How do you truncate all tables in a database using TSQL?
  16. SQL Server: How to select all days in a date range even if no data exists for some days
  17. SQL – The conversion of a varchar data type to a datetime data type resulted in an out-of-range value
  18. TSQL: How to convert local time to UTC? (SQL Server 2008)
  19. SQL Server datetime LIKE select?
  20. How can I import an Excel file into SQL Server? [closed]
  21. Possible to set default schema from connection string?
  22. Entity Framework Core 2.0: How to configure abstract base class once
  23. SQL Server recursive query
  24. How to reuse calculated columns avoiding duplicating the sql statement
  25. Commas within CSV Data
  26. How do I move a table into a schema in T-SQL
  27. How to use RANK() in SQL Server
  28. Generate table relationship diagram from existing schema (SQL Server) [closed]
  29. Error converting data types when importing from Excel to SQL Server 2008
  30. EAV over SQL Server

Leave a Comment