SQL view from 2 different Server

From Logic Wiki
Jump to: navigation, search


Create Linked Server First

   EXEC sp_addlinkedserver 
   @server='RemoteServer', 
   @srvproduct='', 
   @provider='SQLNCLI', 
   @datasrc='remtoe.database.address'; -- IP of the remote server

   EXEC sp_addlinkedsrvlogin 
   @rmtsrvname='RemoteServer', 
   @useself='false', 
   @rmtuser='username', 
   @rmtpassword='password';

Create View

CREATE VIEW CombinedView AS
SELECT 
    Local.Income,
    Local.CustomerId,
    Remote.Name
FROM 
    Finance.Income As Local
JOIN 
    [RemoteServer].[TableName].[dbo].[tblCustomers] AS Remote 
ON 
    Local.CustomerId = Remote.ID;