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?

  • 1
    All you need to do in the proc is reference the external table using one or two part names. But you must first create the definition of the external data source and table in the database. See Cross-Database Queries in Azure SQL Database for an overview and example DDL. – Dan Guzman Jul 18 at 10:50
up vote 4 down vote accepted

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:

  1. Create an external data source - which is kinda like a linked server
  2. 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.

  • Thanks, but my proc has update statement, which I guess is not supported for Elastic query. – Shaurya S Jul 23 at 4:18
  • Seems kinda odd to do updates in a stored proc called sp_select. – Brent Ozar Jul 23 at 12:10
  • Haha, its not actually the name of the proc, have changed it for this question. In my proc, there is updation and many other DMLs. – Shaurya S Jul 24 at 4:20

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.