How can I join two tables from two different Servers

[worldpeace]

Hello everyone.
I am trying to do a SELECT statement joining two tables from two different SQL Servers that have different authentication. Is it possible? Do I make any sense? Please help, THANKS

 

[chiph]

The different authentication is a problem. I doubt you'll be able to do this until the same credentials are available on both servers.

Otherwise, you do this:

Select A.*, B.*
From [Servername].[databasename1].dbo.[tablename1] A
INNER JOIN [OtherServerName].[DBName2].dbo.[tablename2] B
ON A.PriKey = B.PriKey

You can also replace dbo with the owner of the table. The square brackets are required, AFAIK.

Chip H.

 

[worldpeace]

Thanks Chiph.
I tried your codes but did not work. Is it because I did not execute sp-addlinkedserver? Would it help if I run that?
Again thanks for your helps

 

[cmmrfrds]

Yes, you will need to sp-addlinkedserver. Probably start out by using the sa login and when successful you can tighten up login security.

In this example bigtuna has the link to greatwhite.

select A.*, B.* from bigtuna.northwind.dbo.Employees A
inner join greatwhite.northwind.dbo.Employees B
On A.employeeid = B.employeeid

 

[worldpeace]

Thanks cmmrfrds.
I have been trying to do a sp-addlinkedserver but did not seem to work.
What I am trying to do it's to link CAT server to DOG server.
CAT is SQL server 7.0 and DOG is SQL server 2000 (that has a different sysadmin id and password from CAT).Could you please show me how to link these two servers so that I can inner join tables from? Once is linked do I need to do sp_addlinkedsrvlogin? Thanks.

 

[DougP]

Can you import the data from CAT to an new table in DOG? DougP, MCP