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