Join 2 tables from two servers

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

SQL Server Development (2000)

Join 2 tables from two servers

Author

Topic

Brother52
Starting Member

9 Posts

Posted - 2004-10-20 : 09:43:50

I am fairly new to SQL programming, and I have a need to join two (or more) tables from two virtual servers. For example, lets say my virtual servers are SQL1 and SQL2. One SQL1, I have a table called tProducts, with a field called DEX_ROW_ID. One SQL2, there is a table called tCustoms, with a field that is also called DEX_ROW_ID.

How would I write a query, or a stored procedure that can combine these two table, with a equal join?

Thanks in advance,

MS SQL Server: 7
Windows NT4 Server

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-20 : 09:51:18

select *
from sql1.dbname.dbo.tProducts t1
inner join sql2.dbname.dbo.tCustoms t2 on t1.DEX_ROW_ID = t2.DEX_ROW_ID

you'll also need to add linked server.
look it up in BOL:
sp_addlinkedserver

Go with the flow & have fun! Else fight the flow

Brother52
Starting Member

9 Posts

Posted - 2004-10-20 : 10:45:02

Thanks for the information and the very quick reply...

Okay, I went ahead and used sp_addlinkedserver, and followed up with a exec sp_linkedservers to see what servers are avialable... and I see this in the results window...

SQL1\GREATPLAINS SQLOLEDB SQL Server SQL1\GREATPLAINS NULL NULL NULL
SQL2\SALESLOGIX SQLOLEDB SQL Server SQL2\SALESLOGIX NULL NULL NULL

When I run the following script,
select *
from dbo.iv00101 t1
join SQL2\saleslogix.product t2 t1.itemdesc = t2.name

I get:
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '\'.

Any advise?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-20 : 11:06:06

try

when u run sp_linkedservers there is a column srv_name. use that.

select *
from [name from srv_name].master.dbo.sysobjects

should get you result of all object. i guess rewriting this to your table won't be a problem, right?

Go with the flow & have fun! Else fight the flow

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2004-10-20 : 11:15:14

Please use "." inspite of /. This will work once u add link server.

Regards
Sachin

Brother52
Starting Member

9 Posts

Posted - 2004-10-20 : 12:10:30

i guess rewriting this to your table won't be a problem, right?

What does that mean? Remember, I am new to this stuff. I don't want to write anything to a table, just join two table together when they are different servers.

Would you mind giving me the steps needed to achieve this?

The server names are:
SQL1\greatplains
SQL2\saleslogix

and I would like to join IV00100 from SQL1\greatplains to PRODUCT on SQL2\saleslogix.

Thanks.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-20 : 12:17:02


select * 
from [SQL1\greatplains].[databaseName].[dbo].[IV00100] t1
     inner join [SQL2\saleslogix].[databaseName].[dbo].[PRODUCT] t2 on t1.itemdesc = t2.name

i guess this will do.

Go with the flow & have fun! Else fight the flow

Brother52
Starting Member

9 Posts

Posted - 2004-10-20 : 12:56:22

Hmmmm, I guess this is a lot harder then I thought. I am still not getting it.

Again, when I execute exec sp_linkedservers I receive the following:

SRV_NAME = SQL1\GREATPLAINS
SRV_PROVIDERNAME = SQLOLEDB
SRV_PRODUCT = SQL Server
SRV_DATASOURCE = SQL1\GREATPLAINS

SRV_NAME = SQL2\SALESLOGIX
SRV_PROVIDERNAME = SQLOLEDB
SRV_PRODUCT = SQL Server
SRV_DATASOURCE = SQL2\SALESLOGIX

Is that correct?

No matter how I attempt to use a linked server, I also get the same message ..Invalid object name

Thanks,

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-20 : 13:40:43

show us the whole query.

Go with the flow & have fun! Else fight the flow

Brother52
Starting Member

9 Posts

Posted - 2004-10-20 : 14:51:40

This is what I've entered into query manager....

select *
from [SQL1\GREATPLAINS].dbo.iv00101 t1
join [SQL2\SALESLOGIX].sysdba.product t2 on t1.itemdesc = t2.name

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-20 : 14:57:56

you're missing:

select *
from [SQL1\GREATPLAINS].DatabaseName.dbo.iv00101 t1
join [SQL2\SALESLOGIX].DatabaseName.sysdba.product t2 on t1.itemdesc = t2.name

but i guess you database names are SALESLOGIX and GREATPLAINS
you must do:
select *
from [SQL1].[GREATPLAINS].[dbo].[iv00101] t1
join [SQL2].[SALESLOGIX].[sysdba].[product] t2 on t1.itemdesc = t2.name

and that is what Sachin already suggested...

Go with the flow & have fun! Else fight the flow

Brother52
Starting Member

9 Posts

Posted - 2004-10-20 : 15:01:27

Slap me, why don't ya....

I don't know how many times I read your post, and examined your example, but each time, I missed the bit about the database name!....

As soon as I added the little nugget, all is well....

Thanks for your help.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-20 : 15:02:34

sure are you anywhere near me??
we can go for a beer and i can slap you

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources