how to join two tables from different servers

Question

Post reply

how to join two tables from different servers

htar htar

SSC Veteran

Points: 233
More actions
July 27, 2007 at 9:17 pm

#174654

hi!
i m trying to join two tables in different databases from different machines on LAN. i couldn't get it. i m using sql 2005.
it was ok to join two tables of different databases on lacal machine.
can anyone help me ?

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

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

Chand00 SSCertifiable Points: 6466 More actions · Answer 1

Hi Friend,

You can do that. Refer sp_addlinkedserver in the SQL Books online or the microsoft site or you google.

Thanks & Regards

Chandra Mohan N

[font="Verdana"]Thanks
Chandra Mohan[/font]

James Horsley Hall of Fame Points: 3196 More actions · Answer 2

Create linked server with sproc sp_addlinkedserver - also you may need to create linked server login using sp_addlinkedsrvlogin see SQL docs for info on both of these.

Then to assuming your current server needs to link to linked server linksvr you would select from that using e.g.

select * from linksvr.database.dbo.tablename

4 part dotted is server.database.owner.table

To join to local table would be e.g.

Select * from localtable l inner join linksvr.database.dbo.tablename r on l.key=r.key

If it gets into a transaction you will need to ensure distributed transaction coordinator (MSDTC) setup properly both ends

James Horsley
Workflow Consulting Limited

sairam-257016 SSC Journeyman Points: 95 More actions · Answer 3

While the linked server is the best option for that, do not forget to give the credentials on which the linkedserver should run which otherwise would give you a security exception. The credentials can be given in the properties of the linked server object.

the sqlist SSCrazy Points: 2398 More actions · Answer 4

You can also use OPENDATASOURCE or OPENROWSET without having to create a linked server.

Ex from Books Online:

with OLE DB provider for MS SQL

SELECT a.*

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

'SELECT * FROM pubs.dbo.authors ORDER BY au_lname, au_fname') AS a

or

with OLE DB Provider for ODBC

SELECT a.*

FROM OPENROWSET('MSDASQL',

'DRIVER={SQL Server};SERVER=seattle1;UID=sa;PWD=MyPass',

pubs.dbo.authors) AS a

ORDER BY a.au_lname, a.au_fname

Don't just give the hungry man a fish, teach him how to catch it as well.

the sqlist