Ability to disable or workaround deferred name resolution
It would be highly desirable to be able to cause an immediate compile of a stored procedure to check for incorrect object names, rather than wait for the procedures to fail at run-time (when first compiled), as occurs with deferred name resolution.
Upvotes: 30<=-=Jul 21 2005 2:03PM=-=>
I agree that this would be valuable, but as you can probably guess it is too late in the product cycle to do this in SQL 2005. I will make sure that this gets tracked as a requirement in the next release.
I’ll chime in here to make a few points.
One idea I once saw floated was to add WITH SCHEMABINDING for stored procedures. I like to add here that this is both too harsh and too lax. Too harsh , because you may want to change tables by means of drop and recreate, and SCHEMABINDING would be a major hassle. Too lax, because it would not in itself address temp tables.
The other important issue is that temp tables created in the procedure needs to be handled. But this should be difficult, just restore the 6.5 behaviour.<=-=Oct 10 2006 6:09PM=-=>
Sorry for the very late response on this. Thanks for the suggestion; yes we need to provide a mode for restricting deferred name resolution to avoid the pitfalls that DNR can get developers into. Your feedback is duly noted.
- Balaji Rathakrishhan<=-=Dec 30 2010 11:41PM=-=>
So MS, what is the status of the ability to disable DNR? Will this ever be implemented without having to force the DB compatibility level to 6.5?<=-=Dec 30 2010 11:44PM=-=>
All we’re asking for is an option to dynamically disable/enable DNR on an as-needed basis, e.g.:
set dnr off
create procedure blah
set dnr on
It should be a user- and database-specific setting.
I agree it would be useful.
Perhaps this is a bug for the Execute command. I ran into this on SQL Server 2017 only when a variable is assigned. You can reproduce the issue by setting the user database offline.
--job step here:
declare @var as date = dateadd(day, -1, getdate())
execute dbawork.dbo.uspTest @tbd = @var
Msg 946, Level 14, State 1, Line 7
Cannot open database 'DBAWork' version 852. Upgrade the database to the latest version.