I have a script that has a spelling error in the insert statement:

insert #tempTable(id, statux) values (1, 'this is a test')

This statement is deep in the script behind If and Case..when statements
and is never ever executed.  There is a validation, which prevents this
code from executing, that hasn't been hit in a very long time.

That said, I recently ran this script on a sql server 2000 machine, and
the script failed with an error saying that 'statux' was an invalid
column.  The odd thing is that the failure occured when the code
execution got close to the offending code statement.  It's like SQL
Server decided to recompile the piece of code where the insert statement
is located.

I ran the same script on a different sql server 2000 and sql server 2005
machines and the script did not fail.

Of course, I fixed the misspelling, but I am curious about the deeper
issue of how and when Sql Server enforces Deferred Name Resolution (if
that is what causes the issue).

Is there a setting that controls this issue?

Regards

Hello Frank,

I understand that you have some concerns about deferred name resolution.

When a stored procedure is created, the statements in the procedure are
parsed for syntactical accuracy. If a syntactical error is encountered in
the procedure definition, an error is returned and the stored procedure is
not created. If the statements are syntactically correct, the text of the
stored procedure is stored in the syscomments system table.

When a stored procedure is executed for the first time, the query processor
reads the text of the stored procedure from the syscomments system table of
the procedure and checks that the names of the objects used by the
procedure are present. This process is called deferred name resolution
because table objects referenced by the stored procedure need not exist
when the stored procedure is created, but only when it is executed. You may
want to refer to the following article for details:

http://msdn2.microsoft.com/en-us/library/aa214346(SQL.80).aspx

It seems that when the SQL is first executed the column is valid and the
compliation completed. However, when the exectuion plan is run again on
this statement, the error appears because the column is actually changed.

Please let's know if you have any further comments or questions. Thank you.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

To add to Perter's response, the column name will be validated only if the
table exists when the proc is created.

My guess is that the temp table existed on only the one server.  The script
below illustrates this.


CREATE TABLE #tempTable
(
    id int NOT NULL,
    status varchar(30) NOT NULL
)
GO

--this create will fail
CREATE PROC dbo.Test1
AS
    CREATE TABLE #tempTable
    (
     id int NOT NULL,
     status varchar(30) NOT NULL
    )

    INSERT #tempTable(id, statux)
    VALUES (1, 'this is a test')
GO


DROP TABLE #tempTable
GO

--this create will succeed
CREATE PROC dbo.Test1
AS
    CREATE TABLE #tempTable
    (
     id int NOT NULL,
     status varchar(30) NOT NULL
    )

    INSERT #tempTable(id, statux)
    VALUES (1, 'this is a test')
GO



Show quoteHide quote

Changing the text of the code of multiple stored procedure
How to configure SQLExpress on a workgroup
SQL Server Backup
How to avoid creating duplicate indexes on the same column
SQL Server Backup and Transactions
SQL Server 2005 compact edition with VB
Restore Failure OS Related ?
SA Password help
DATABASE Mail Account Setup Error
ISNULL not working