How to link a data from two different Microsoft SQL Server?

Question

We have two servers, both have a MS Sql server installed, I need to make a view from each other database tables, Is there someone knows how to do it? What is the script?

Answer 1

You can also use the OPENROWSET T-SQL



Here's an example



server 1 (contains users table)

server 2 (contains users email accounts table)



Here's the view to get data from server 1



CREATE VIEW View1 AS

SELECT a.*

FROM OPENROWSET('SQLOLEDB','server1';'sa';'MyPass',

'SELECT UserID,LastName,FirstName FROM db1..tblusers ORDER BY LastName, FirstName') AS a



Here's the view to get data from server 2



CREATE VIEW View2 AS

SELECT b.*

FROM OPENROWSET('SQLOLEDB','server2';'sa';'MyPass',

'SELECT UserID,EmailAddress FROM db2..tblemailaccounts ORDER BY emailaddress') AS b

Answer 2

1. from server "A", set up a linked server to the other server, server "B" - using Enterprise manager, go to the security section and add one there.

2. at that point your queries can make references across servers. example if you added the reference on server_A, to server_B:

(from server_A)



select * from [server_B].[database name].[dbo].[table name]





of the [ ] are not required, others are, don't remember which.

also, this only works one way, you must add links on both servers for it work both ways.

Answer 3

from administration panel (you may could click on administrative strategies) then information components (ODBC) If the ODBC does not checklist the ms sq. server as an determination, it potential the driving force isn't wisely put in. I had maximum of problems with it, I used the jdbc-odbc bridge for the time of progression, just so i might have something that labored.