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.
Mad credit to @Kendra_Little for the help on this.
I had a SQL Server Agent Job that fails on a secondary replica because it’s being upgraded to 2017. However, I don’t think it should be failing.
The job has logic using TempDB to check first if its primary then execute the actual business logic (userdb.dbo.sproc) with a set variable.
And there’s the conundrum.
Why does it try to validate when the variable is assigned, even if the object doesn’t even exist? Is it trying to probe, build an exec plan for the sproc or cache?
use tempdb
go
if 1=0
begin
--job step here:
declare @var as date = dateadd(day, -1, getdate())
execute dbawork.dbo.uspTest
end
else begin
print '0'
end
/*
Results Messages: 0
*/
use tempdb
go
if 1=0
begin
--job step here:
declare @var as date = dateadd(day, -1, getdate())
execute dbawork.dbo.uspTest @tbd = @var
end
else begin
print '0'
end
/*
Msg 946, Level 14, State 1, Line 7
Cannot open database 'DBAWork' version 852. Upgrade the database to the latest version.
*/
To workaround the bug, I mustered the idea to rewrite the job step using dynamic SQL with sp_execustesql and avoid the issue. For example:
if db_id('DBAWork') is null
begin
create database DBAWork
end
go
use dbawork
go
create or alter procedure [dbo].[uspTest]
@tbd as date null
as
begin
set @tbd = isnull(@tbd,dateadd(day, - day(getdate()), getdate()))
select @tbd as MonthEnding
end
go
use tempdb
go
alter database dbawork set offline
go
if 1=0
begin
--job step here:
declare @tbd as date = dateadd(day, -1, getdate())
exec sp_executesql N'execute dbawork.dbo.uspTest @tbd = @tbd',
N'@tbd date',
@tbd = @tbd;
end
else begin
print '0'
end
go
alter database dbawork set online
go
/*
Results Messages: 0
*/
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.
And this is the script from Kendra:
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.
*/
Here’s my whole script on GitHub to reproduce the issue or fix.
Hope it helps you.
Hiram
