Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

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.

1 vote
Sign in
(thinking…)
Sign in with: oidc
Signed in as (Sign out)

We’ll send you updates on this idea

AdminMicrosoft SQL Server (Product Manager, Microsoft Azure) shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

Upvotes: 30

<=-=Jul 21 2005 2:03PM=-=>

Greg,
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.
Thanks,
- Balaji

<=-=Jul 24 2005 3:37AM=-=>

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=-=>

HI Greg,
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

go

set dnr on

It should be a user- and database-specific setting.

1 comment

Sign in
(thinking…)
Sign in with: oidc
Signed in as (Sign out)
Submitting...
  • Hiram commented  ·   ·  Flag as inappropriate

    +1
    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.

    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.

Feedback and Knowledge Base