CODEWITHSUNDEEP
azureazure-sql-databaseazure-elastic-scale
JoeGeeky
JoeGeeky

Reputation: 3796

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:

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.

Upvotes: 79

Views: 108814

Answers (2)

AdaTheDev
AdaTheDev

Reputation: 147344

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.

Upvotes: 66

Silvia Doomra
Silvia Doomra

Reputation: 953

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.

Upvotes: 10

Related Questions