Can't query between databases in SQL Azure
AzureAzure Sql-DatabaseAzure Elastic-ScaleAzure Problem Overview
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.
Azure Solutions
Solution 1 - Azure
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.
Solution 2 - Azure
Cross database queries are now supported in SQL Azure
https://azure.microsoft.com/en-us/blog/querying-remote-databases-in-azure-sql-db/
Solution 3 - Azure
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.