SQL Server 2017 – Deferred Name Resolution Bug.

This issue came up recently while in the midst of an upgrade to SQL Server 2017, but it’s actually not related to the upgrade and has been around all along on previous versions. So I took a moment to write this, cause a friend told me it would make a nice blog post.

The issue occurs because it’s trying to check if it needs to use deferred name resolution or not, and hits a gap due to the target database’s current status. This may be a bug because I could see why Deferred Name Resolution would be needed if I were using a Create command but I don’t see why DNR would interfere here with the Execute command, especially within an If statement.

After searching UserVoice (Microsoft Azure Feedback Forums which retired Microsoft Connect) I found one related post to up-vote.

In order to see how Deferred Name Resolution is the culprit, we can test creating a stored procedure, which prints more clear messages about deferred name resolution.

create database offlinedb
GO
alter database offlinedb set offline
GO
create or alter procedure #test
as
if 1=0
execute dbdoesnotexist.dbo.someproc
GO
/*
The module '#test' depends on the missing object 'dbdoesnotexist.dbo.someproc'. The module will still be created; however, it cannot run successfully until the object exists.
*/
create or alter procedure #test
as
if 1=0
execute offlinedb.dbo.someproc
GO
/*
Msg 942, Level 14, State 4, Procedure #test, Line 5 [Batch Start Line 14]
Database 'offlinedb' cannot be opened because it is offline.
*/