Joining 2 different databases on the same (SQL 7) server 2

[MartinSmithhh]

Does any one know how to do this?

I am currently trying the following
SELECT *
FROM DB1.Table1, DB2.Table1
WHERE (DB1.Table1.NT_Login = DB2.Table1.NT_domain_login)

And getting this error
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'DB1.Table1'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'DB2.Table1'.

 

[RickCole]

You need to put the table owner in the sql e.g.

SELECT *
FROM DB1.dbo.Table1, DB2.dbo.Table1
WHERE (DB1.dbo.Table1.NT_Login = DB2.dbo.Table1.NT_domain_login)

Rick.

 

[MartinSmithhh]

That's working great now, Thanks very much!

 

[JpPhysics]

Could you guys show a bit more information on how this query works. I am trying to do the same thing, but am having lots of difficulty. Also, does this work on all versions of SQL Server.

 

[RickCole]

This works on all versions of SQL Server 6.5 and higer. Not sure about version 6.0 as I've never tried it but I would assume it would.

Maybe if you posted your code and the error message we could help more.

Rick.

 

[JpPhysics]

Actually, before I read this post, I wasn't sure that it was possible, but this is what I'm looking for.

I am trying to pull data from two databases. The first Database name is 'M2MData06' and the second database is 'Engineering'. (Background: M2MData06 is our MRP System) Anyway, I've got a list of parts in the Engineering Database in the 'Parts' Table and would like to search the 'Job' Table in M2M to see if those parts have current jobs. I don't fully understand the syntax above:

SELECT *
FROM DB1.dbo.Table1, DB2.dbo.Table1
WHERE (DB1.dbo.Table1.NT_Login = DB2.dbo.Table1.NT_domain_login)

I don't know how to adapt this to my databases.

Thanks again for the help.

 

[MartinSmithhh]

Well what is the link? (Or what syntax would you use if they were both in the same database?)

If it is PartsID then you would use

SELECT *
FROM M2MData06.dbo.job, Engineering.dbo.Parts
WHERE (M2MData06.dbo.job.PartsID = Engineering.dbo.Parts.PartsID)

(assuming dbo owns both tables)

 

[JpPhysics]

Thanks, that works perfectly!

 

[Guest_imported]

Difficult Question for you...
I have two separate Databases

1. Microsoft SQL Server 2000
2. Pervasive SQL Server 2000i SP4

Both have product information and have common fields

1. Part Number
2. Price

The issue is, Our Microsoft server has Serialized Inventory, that means our inventory has serial numbers on each unit, and our Pervasive Server Doesn't. I'm trying to think of a logical way to link the two types of information.. Any ideas?

brown.2237@osu.edu