Can't query between databases in SQL Azure

问题:

I have a SQL Azure Database Server and I need to query between the Databases but can't figure out how to accomplish this.

Here is the structure of my databases:

• Server.X
  • Database.A
  • Database.B
  • Database.C

In Database.A I have a Stored Procedure that needs to retrieve data from Database.B. Normally, I would reference the database like SELECT * FROM [Database.B].[dbo].[MyTable] but this does not appear to be allowed in SQL Azure.

Msg 40515, Level 15, State 1, Line 16 Reference to database and/or server name in 'Database.B.dbo.MyTable' is not supported in this version of SQL Server.

Is there a way to do this on the database end?

In the final version Databases A & C will both need data from Database B.

回答1:

Update:

As per Illuminati's comment and answer, the situation has changed since this answer was originally accepted and there is now support for cross database queries as per https://azure.microsoft.com/en-us/blog/querying-remote-databases-in-azure-sql-db/

Original Answer (2013): Cross database queries aren't supported in SQL Azure. Which means you need to either combine the databases to prevent the need in the first place, or query both databases independently and basically join the data in your application.

回答2:

Cross database queries are now supported in SQL Azure

https://azure.microsoft.com/en-us/blog/querying-remote-databases-in-azure-sql-db/

回答3:

Azure SQL DB is previewing Elastic Database Query feature at this point in time that will help you query among Azure SQL DBs with some limitations. You can get detailed information about the feature here.