Cannot create SP in Azure SQL Database I am trying to create a stored procedure on my Azure SQL Database, which has joins to other database table in Azure SQL Database.

I am getting the following error while creating the Stored Procedure:


  Msg 40515, Level 15, State 1, Procedure sp_select, Line 16 [Batch Start
  Line 0] Reference to database and/or server name in 'database.dbo.table'
  is not supported in this version of SQL Server.


I understand we can make cross database queries through Elastic Database Query, but how to create the stored procedure for it?
Azure SQL DB doesn't allow ad-hoc 3-part names (database.schema.table) in queries.

Azure SQL DB Managed Instances do - but that's a different product. If you have a lot of existing stored procedures (say, hundreds) referring to a lot of existing objects in different databases (say, thousands), then it may be easier to migrate to Managed Instances than do what I'm about to describe.

To get cross-database queries working in Azure SQL DB, then in each database where you want to run the query, you need to:


Create an external data source - which is kinda like a linked server
Create an external table - which is kinda like a view that points to the table you want to query, going through the external data source


The instructions are fairly straightforward, but here's the painful part: you have to do this for every database you want to query, for every table/view/etc that you want to query. Plus, if the table changes (like new columns are added or a data type changes), then you have to go back and revisit all of the external table references that point to it, and change their structure as well.

This is one of those things that "technically works" in Azure SQL DB, but really only works for new from-scratch applications, not migrating an existing application to the cloud. Like I said - if you've got a lot of these queries referencing a lot of objects, now you understand why Managed Instances might be a better fit for you.