How can I get data from 2 different SQL Servers

Question

I have the following situation. I am working with 2 separate SQL servers. Server A hosts the company HR data. There is a view on Server a that provides supervisor info for each employee. I need to get the next supervisor info going up the chain. So I used this to code, I got from the DB admin, to accomplish that

SELECT *
FROM [lawdata].[dbo].[All_Users] ru1 
left outer join [lawdata].[dbo].[All_Users] ru2 on ru1.SUPER_EID = ru2.EMP_EID

Now I have data on a separate SQL Server, Server B, that contains some report data the ReportData table contains the employee ID which matches employee ID numbers shown in the view above from Server A. The questions is how can I merge the view from Server A and the Employee ID on Server B so I can link the supervisors to the data rows on Server B.

I have seen this post but just cannot get the syntax right to make it work with my situation

Thanks

score 3 · Answer 1 · answered Jan 06 '16 at 21:03

3

You need linked servers. then use

[ServerName].[DatabaseName].[dbo].[tableName]

Create Linked Servers (SQL Server Database Engine)

answered Jan 06 '16 at 21:03

Juan Carlos Oropeza

47,252
12
78
118

score 1 · Answer 2 · answered Jan 06 '16 at 21:24

For this, I'd create an SSIS package to pull down the data from the lawdata server into the database on Server B once a night - probably just a truncate and reload. This way, all of your queries with lawdata data on Server B is localized to one database on one server.

score 0 · Answer 3 · answered Jan 06 '16 at 21:14

it looks like in your code you did a left outer join on something with itself. Try

SELECT *
FROM [server1].[dbname].[dbo].[tablename] A 
left outer join [server2].[dbname].[dbo].[tablename] B on A.columnname = B.columnname
where ["insert where clause here"]

score 0 · Accepted Answer · answered Jan 11 '16 at 16:42

Just in case someone else is trying to solve this same problem here is the solution I came up with; thanks to the suggestion given above

select rd.*, ru1.emp_first, ru1.emp_last, ru1.Super_Last as FirstLineLast,
Super_first as FirstLineFirst,
ru2.Super_Last as SecondLineLast,
2.Super_first as SecondLineFirst
from [TaserEvidence].[dbo].[ReportData] rd left outer join  
[soops-lawrept].[lawdata].[dbo].[My_View] ru1 on rd.OwnerBadgeId = ru1.emp_EID
left outer join 
[soops-lawrept].[lawdata].[dbo].[rob_users] ru2 on ru1.super_EID = ru2.EMP_EID

Is great you found a solution. But if you want share it with everyone else you need to provide some explanation. — Juan Carlos Oropeza, Jan 26 '16 at 02:20

How can I get data from 2 different SQL Servers

4 Answers4