Post reply

CREATE or ALTER PROCEDURE not detecting invalid object names.

  • May 21, 2010 at 4:28 am

    #221884

    Hello,

    I have an odd problem that is causing me some frustration. When I execute a CREATE PROC or ALTER PROC statement in SQL Management Studio, the query will complete successfully even when the procedure statements reference non-existant objects.

    I am used to the query failing with a error stating that the object does not exist. Is there some database, server or query configuration option that I have forgotten or overlooked?

    The following code demonstrates the issue.

    CREATE PROCEDURE testProc AS SELECT * FROM NonExistentTable

    GO

    When I execute this code, I get the message "Command(s) completed successfully."

    But when I execute the procedure, thus:

    EXEC testProc

    I get the message "Msg 208, Level 16, State 1, Procedure testProc, Line 1

    Invalid object name 'NonExistentTable'."

    Shouldn't the "Invalid bject name" error occur when I execute the CREATE PROCEDURE query??

    Any advice welcome.

  • May 21, 2010 at 4:48 am

    #1170706

    NO.. it wont give compile time errors..

  • May 21, 2010 at 5:28 am

    #1170724

    No? I used to get the "Invalid object name" error at compile-time several months ago. But something has changed and now I don't.

  • May 21, 2010 at 5:38 am

    #1170733

    late binding of objects are a feature of stored procedures; you can reference a non existent table/view/procedure/function without raising an error. the references will be checked when the procedure is actually ruun, instead.

    you cannot references columns that do not exist where the Table/View DOES exist however.

    you will get invalid object references with views and functions.

    you may have read that the view sysdepends is not always accurate, which shows which objects reference others the the database; it is accurate for everything except procedures becasue of this late binding feature.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • May 21, 2010 at 2:23 pm

    #1171100

    The concept Lowell is explaining is often referred to as Deferred Name Resolution.

    Seth Phelabaum

    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • December 10, 2014 at 4:07 am

    #1763727

    You can run this script to check for late-binding issues:

    -- Based on comment from http://blogs.msdn.com/b/askjay/archive/2012/07/22/finding-missing-dependencies.aspx

    -- Check also http://technet.microsoft.com/en-us/library/bb677315(v=sql.110).aspx

    select o.type, o.name, ed.referenced_entity_name, ed.is_caller_dependent

    from sys.sql_expression_dependencies ed

    join sys.objects o on ed.referencing_id = o.object_id

    where ed.referenced_id is null

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply