Create query using two databases

Question

I am developing a web application (using classic asp for now) that needs to query two tables on two separate databases on the same server.

I have been given separate application-level login/passwords for each database by the IT team which will be used in my code to connect.

I want to create a query that links two tables (one from each database) together, but not too sure how, and Google hasn't helped me much either.

I'm assuming I have to create two connections to do this as I have two separate logins, but how then would I create a query that uses tables from each.

Just a note - our IT team will not at this stage link the two databases together. I'm pretty much stuck with what I have.

Hope you can help. Mat

score 2 · Answer 1 · answered Sep 13 '12 at 09:34

2

If the IT team wants the databases to be separate, you obviously can't use the database to do the join for you.

You'd have to execute the join client side. Linq is an easy way to do that, but a loop with two datasets also works.

answered Sep 13 '12 at 09:34

Andomar

232,371
49
380
404

Briefly checked for similar SO questions: http://stackoverflow.com/questions/2691890/linq-to-sql-get-records-from-two-dbs-that-are-on-different-servers and http://stackoverflow.com/questions/352949/linq-across-multiple-databases .. Looks like you'll need a link between the databases to make it work with LINQ – AardVark71 Sep 13 '12 at 09:53

score 1 · Accepted Answer · answered Sep 13 '12 at 09:34

The typical syntax for a cross database query would be something like:

SELECT <columns>
  FROM [schema].[table] t1 INNER JOIN
       [databaseName].[schema].[table] t2 ON <condition>

However, if you're using 2005 or above, it's well worth creating a synonym in the first database so that the reference to the second database isn't peppered throughout your code (what if the database name changes later)?

CREATE SYNONYM [schema].[synonymName] FOR [databaseName].[schema].[table]

Then your query becomes:

SELECT <columns>
  FROM [schema].[table] t1 INNER JOIN
       [schema].[synonymName] t2 ON <condition>

You may need to set permissions appropriately such that the user you are using to connect to the first database has access to the second. If the IT team simply grants the relevant access to both databases from the same server principal, that is the best way to achieve it. If you are stuck with having two totally separate logins then that sucks. But there isn't any need to 'link the databases together' in order to run a cross-database query.

'If the IT team simply grants the relevant access to both databases from the same server principal, that is the best way to achieve it.' He said the IT team doesn't want to link the databases together - not that they necessarily want to have separate server principals. — Matt Whitfield, Sep 13 '12 at 09:51

Create query using two databases

2 Answers2