Cannot Create Stored Procedure in Azure SQL Database – Troubleshooting

azure-sql-databasesql servert-sql

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?

Best Answer

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.

Related Solutions

Running Brent Ozar’s sp_BlitzIndex on Azure SQL Database

Kendra here (the author of sp_BlitzIndex)

First, thanks for being interested in the procedure and trying it out.

Azure doesn't expose all the dynamic management views that we get in the boxed product. I do actually reference sys.dm_db_partition_stats, but there's other info I need to get from sys.partitions for other users. (Is it using compression? What type?)

I haven't had time to write an test an Azure specific version, just because I haven't had a lot of demand for it. But your question does tell me there's more interest than I knew of -- so there's that!

I will at minimum look at implementing some error handling so it lets you know in a graceful way though. (This is a bit of a moving target, because Azure has been expanding the features that are available to users as well.)

SQL Server – Unable to Generate Scripts for Publications

DECLARE @distributor sysname

DECLARE @distributiondb sysname
DECLARE @distproc NVARCHAR (255)
DECLARE @publisher sysname

SELECT @publisher = @@SERVERNAME

EXEC sys.sp_MSrepl_getdistributorinfo @publisher = @publisher,
@rpcsrvname = @distributor OUTPUT,
@distribdb = @distributiondb OUTPUT

select @distributor, @distributiondb

SELECT @distproc = QUOTENAME (@distributor) + '. ' + QUOTENAME (@distributiondb) + '.dbo.sp_MSenumdistributionagentproperties'

select @distproc ---what do you get here?

---Line 537
EXEC @distproc @publisher = @publisher,
@publisher_db = @dbname,
@publication = @orig_publication,
@show_security = 1

The exec @distproc section is roughly at Line 537 of sp_MSrepl_helpsubscription. It's building the proc call and should be something like [SeverName].distribution.dbo.sp_MSenumdistributionagentproperties

From the error, it appears that @distproc variable is not getting set properly, or at least getting set to an empty string.

Why? That's hard to know without more information or being able to test on your system. Some unusual setup with the distributor, perhaps? But this will hopefully point you in the right direction so you can walk through it. (Run that code on the Database that is being published).

Best Answer

Related Solutions

Running Brent Ozar’s sp_BlitzIndex on Azure SQL Database

SQL Server – Unable to Generate Scripts for Publications

Related Question