How to join tables from two sql instances

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)

How to join tables from two sql instances

Author

Topic

Hommer
Aged Yak Warrior

808 Posts

Posted - 2004-12-09 : 10:25:12

I have SQLInstance1 with dbFinance and SQLInstance1 with dbSales.
I want to join Invoice from dbFinance to Order from dbSales on OrderID, but could not find the right way to do it.

I remember several months ago, when I discussed the possible designs of the databases, some experts on this forum replied that this is possible, thus recommend putting them in different sql instance.

I appreciate your help!

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-12-09 : 10:30:01

select x,y,z from dbfinance.dbo.invoice a
inner join server2.dbsales.dbo.order b on a.orderid = b.orderid

having 1st defined server2 to server1 using sp_addlinkedserver

Hommer
Aged Yak Warrior

808 Posts

Posted - 2004-12-09 : 12:43:26

Thanks.
Now I can select tables from a remote server.

But I also run into an error saying Cannot resolve collation conflict for equal to operation.
On the join column, I had Latin1_General_BIN on one side and
SQL_Latin1_General_CP1_CI_AS the other.

What function I can use to convert/cast them equal?

Hommer
Aged Yak Warrior

808 Posts

Posted - 2004-12-09 : 12:56:18

I found the solution:

ON a.id = b.id collate SQL_Latin1_General_CP1_CI_AS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources