help linking 2 different SQl databases

[someoneelse]

i am trying to figure out the syntax to do a SQL inner join from 2 different databases...

The sameple code looks like this: select *
from database1.dbo.table1 t1 join database2.dbo.table2 t2 on
t1.field1 = t2.field2

but I dont know how to refernce the " database1.dbo" and "database2.dbo" parts.. can someone help?

i know the table names in all my databases.. i have the connections in include files..

i just dont see how to "call" the other database? basically what i to know is what need to change dqatabase1, database 2, and dbo to in my code?

[jstfsklh211]

same server or different server?

[ChrisHunter]

What does the error message say?

If they're on the same server you just have to put the name of the database before the schema and table names. For example:

SELECT * FROM Database1.dbo.Table1 db1t
INNER JOIN Database2.dbo.Table2 db2t
    ON db1t.ID = db2t.ID

You should also list the specific fields you want to pull from each table rather than using SELECT *.

[someoneelse]

they are on the same server... but whenever i try to change to change the datbase name from Database1.dbo.Table1 it says invalid object name.

[Taywin]

Hmm.. What are your database names? What are your table name? For example, you have 2 database -- mydb1 and mydb2. Each database have the same table named mytab1. The query could be...

SELECT *
FROM mydb1.mytab1
  INNER JOIN mydb2.mytab1
    ON mydb1.mytab1.id = mydb2.mytab1.id;

[ChrisHunter]

Check the spelling of you database name, schema name and table name.

The best thing to do will be to either use intilesence or copy and paste the names to make sure you've got the exact spelling as you might have miss spelled them when you created them (I speak from experience).

[kgariando]

. Create a linked server in DB invironment, then create a SP to take care of it.

1. Get two DataSets for them, then merge two datatables into one based on usersID.

or try this:

SELECT a.userID, b.usersFirstName, b.usersLastName FROM databaseA.dbo.TableA a inner join database B.dbo.TableB b ON a.userID=b.userID