How do I query tables located in different database?

Question

My original question was about whether to keep separate ASPNETDB.MDF from the application database or merge all the tables in one database. Checking the previous questions/answers, I learned that it depends on whether the membership data would be shared across several applications.

Now, my question is this. In case I decide to keep ASPNETDB.MDF separate from the application DB, how can I query 2 tables located in 2 different databases?

Thanks for helping.

Would your queries need to access both databases at once? e.g. To Join a table in one database on a table in another? — Martin Smith
– Martin Smith, Commented Apr 24, 2011 at 21:42

Steve Mayne · Accepted Answer · 2011-04-24 21:38:35Z

13

If you have two databases/schemas on the same database server, you can query across databases with the following syntax:

select *
from database1.dbo.table1 t1 join database2.dbo.table2 t2 on
  t1.field1 = t2.field2

If they are on physically separate servers, you can still do a cross-database query, but you need to link the servers first:

http://msdn.microsoft.com/en-us/library/aa213778(v=sql.80).aspx

answered Apr 24, 2011 at 21:38

Steve Mayne

23k4 gold badges53 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Martin Smith Over a year ago

In addition to using 3 part object name syntax you would also have to either set up the server to allow cross database ownership chaining or mark the DB as trusworthy. Both have security implications.

Richard77 Over a year ago

To make things easier for now, let's say (i) I created a DB under the App_Data and (ii) ASPNETDB.MDF is generated for me when I access the membership module for the first time. Is that you mean by "same database server"?

HLGEM Over a year ago

He means the data is stored onthe same physical server, that has nothing to do with how it wsa created but with where it was created.

Talha Tayyab · Accepted Answer · 2022-08-01 12:52:12Z

0

You can check your SQL Server version by:

SELECT SERVERPROPERTY('Edition')

If you are using "SQL Azure" you will not be able to use a table from different database. You will get this error:

Reference to database and/or server name in 'DataBase.Schema.Table' is not supported in this version of SQL Server.

Even if you try to Query a different database from your file like this:

USE ANOTHER_DATABASE;

You will get this error:

USE statement is not supported to switch between databases. Use a new connection to connect to a different database.

edited Aug 1, 2022 at 12:52

answered Jul 27, 2022 at 14:23

Talha Tayyab

30.7k55 gold badges139 silver badges170 bronze badges

Collectives™ on Stack Overflow

How do I query tables located in different database?

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related