write query from databases on 2 different servers

Question

Post reply

write query from databases on 2 different servers

shipra20

Valued Member

Points: 67
More actions
June 12, 2009 at 12:08 pm

#99765

I have to write a query from 2 different databses on 2 different servers linking up bothe by an id in respective tables.How can i do this.
Thank you,
s

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Mayank Khatri SSCertifiable Points: 7984 More actions · Answer 1

Mayank Khatri

SSCertifiable

Points: 7984

June 12, 2009 at 12:21 pm

#1008843

using :

1)Linked server or

2)Openrowset

Alberto dbLearner Ten Centuries Points: 1246 More actions · Answer 2

You have two servers, local and remote. The remote server (or instance) must be a linked server:

From the current server write this:

SELECT a.Filed1, b.Field2

FROM LocalTable a

JOIN LinkedServer.OneDB.dbo.RemoteTable b

ON a.Id = b.Id

Translating LinkedServer.OneDB.dbo.RemoteTable

LinkedServer = Name of the linked server

OneDB = name of the database in the linked server

dbo = schema name

RemoteTable = name of the table in the linked server

Alberto De Rossi
Microsoft Certified Solutions Associate - SQL Server
Microsoft MVP - Data Platform
Power BI User Group Lima - Community Leader

Randy-574768 Default port Points: 1435 More actions · Answer 3

You have two servers, local and remote. The remote server (or instance) must be a linked server:
From the current server write this:
SELECT a.Filed1, b.Field2
FROM LocalTable a
JOIN LinkedServer.OneDB.dbo.RemoteTable b
ON a.Id = b.Id
Translating LinkedServer.OneDB.dbo.RemoteTable
LinkedServer = Name of the linked server
OneDB = name of the database in the linked server
dbo = schema name
RemoteTable = name of the table in the linked server

You might also:

select id, field into #tmp

from LinkedServer.OneDB.dbo.RemoteTable

where (just the records you need)

--and then

select a.field, b.field

from LocalTable a join #tmp b

on a.id = b.id

Randy

shipra20 Valued Member Points: 67 More actions · Answer 4

Thnanks for the reply.But when i try to write the following it gives me error as my server name is with a slash. Can i use ip adress instead of server name.

SELECT a.name,b.desc

FROM dbo.tbl1 a

JOIN SA_SB_SC\sa_sb_sc.Reports.dbo.tbl2 b

ON a.id = b.id

Please advise.

--sh

ps. One Orange Chip Points: 29423 More actions · Answer 5

shipra20 (6/16/2009)
Thnanks for the reply.But when i try to write the following it gives me error as my server name is with a slash. Can i use ip adress instead of server name.
SELECT a.name,b.desc
FROM dbo.tbl1 a
JOIN SA_SB_SC\sa_sb_sc.Reports.dbo.tbl2 b
ON a.id = b.id
Please advise.
--sh

JOIN [SA_SB_SC\sa_sb_sc].Reports.dbo.tbl2 b

should do the trick.

Pradeep Singh