Multiple Database Stored Procedure

[Guest_imported]

I am trying to write a stored procedure that has an inner join between two tables from two different databases on the same sql server.

Something like..

Select * FROM
DB1-table1 INNER JOIN DB2-table1
ON DB1-table1.ID = DB2-table1.ID

yada yada yada.......

Does anyone know how to do this or is it possible? If so, what database should I put the stored procedure in or does it matter?

 

[foxdev]

Sure, you can do it. I would probably put the SP in the database where the bulk of the data resides, but no hard and fast rule.

[tt]Select *
FROM DB1..table1 TableA
INNER JOIN DB2..table1 TableB
ON TableA.ID = TableB.ID [/tt] Robert Bradley

http://www.lowcostprints.com

http://www.foxdev.com/

http://www.agrainofhope.org

 

[tlbroadbent]

You just need to fully qualify your table names as dbname.owner.tablename.

Select a.id, a.c1, a.c2, b.c3, b.c4
From db1.dbo.tbl1 a inner join db1.dbo.tbl2 b
On a.id=b.id

Make sure the login has permissions on both tables.

Good luck.

 

[foxdev]

Right, you will need permissions on both databases (good point).

The ".." evaluates to the dbo owner (the default); if it is other than dbo, or if you want to explicitly specify, then you'll need to include the table owner. Robert Bradley

http://www.lowcostprints.com

http://www.foxdev.com/

http://www.agrainofhope.org