Join Tables across servers - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Join Tables across servers

Author

Topic

billsack
Starting Member

35 Posts

Posted - 2007-06-04 : 05:54:28

Hi experts!

I am trying to write a simple query that joins two tables across two servers.

Query is this:

SELECT Server1.Database.dbo.Table1.ID,
Server2.Database.dbo.Table5.Name

FROM Server1.Database.dbo.Table1
INNER JOIN
Server2.Database.dbo.Table5
ON
Server2.Database.dbo.Table5.ID = Server1.Database.dbo.Table1.ID

I have changed the server and table names to keep it simple. I get an error message saying:

"The number name 'Server2.Database.dbo.Table5' contains more than the maximum number of prefixes. The maximum is 3."

Does anyone have any ideas? I dont really want to copy tables etc as the data is from a different department and I dont want to confuse things. I'd rather keep this as a 'live' query that can be run on a regular basis.

I hope someone has the knowledge!!!!!!

Billsack

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2007-06-04 : 06:20:13

You need to set Server2 up as a linked server on Server1. Lookup "Linked Server" on BOL.

Mark

billsack
Starting Member

35 Posts

Posted - 2007-06-04 : 07:40:44

Cheers Mark. Looks pretty tricky - i've not done this kind of dba before. I'll give it a try.

Billy

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-04 : 07:53:06

How about aliases?

SELECT s1.ID,
s2.Name
FROM Server1.Database.dbo.Table1 AS s1
INNER JOIN Server2.Database.dbo.Table5 AS s2 ON s2.ID = s1.ID

Peter Larsson
Helsingborg, Sweden

billsack
Starting Member

35 Posts

Posted - 2007-06-04 : 08:34:13

It almost works!!!

Thing is this looks like a server instance rather than an actual ie

server1\server1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-04 : 08:36:31

SELECT s1.ID,
s2.Name
FROM [Server1\Instance4].Database.dbo.Table1 AS s1
INNER JOIN Server2.Database.dbo.Table5 AS s2 ON s2.ID = s1.ID

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-04 : 11:22:57

Instead of joining the table of other server, make a copy of that table in the source server so that you can join that table within the same server

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources