How do I query tables located in different database?

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 le开发者_StackOverflow中文版arned 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.

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

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.