|
|
Posted by Erland Sommarskog on 08/10/06 21:32
Jeff_in_MD (jfowler@dsoftware.biz) writes:
> I'm trying to add a column to a table, then update that column with a
> query. This is all within a single batch. Sqlcmd gives me an error on
> the update, saying "invalid column xxx", because it doesn't know the
> column got added. We used to get around this in "osql" by using the
> EXECUTE command, like: EXEC ("ALTER TABLE tbl ADD newfield varchar(255)
> not null default ' '")
>
> However, it looks like sqlcmd actually checks each query within the
> script before it starts running, and throws the error because the field
> isn't there at the time.
The full story is that SQL Server never accepts a missing column. Still
you sometimes you get away with it. Why? Because of deferred name
resolution (one of the biggest misfeatures added in SQL 7). Deferred
name resolution means that if SQL Server finds a query in a batch, where
one or more tables are missing, it defers compilation until later, and
you will not get an error, unless execution reaches that query and the
table is still missing. Quite an aggravated cost for plain spelling
errors!
But if all tables in a query exists, SQL Server also requires that all
columns exist. Thankfully, there is no deferred name resolution on
columns!
The actual effect of these rules is a bit different in SQL 2000 and
SQL 2005, since in SQL 2000, the entire batch is always recompiled,
while SQL 2005 has statment recompile.
Anyway, the proper procedure in a case like yours is to put all
statements that refer to the new column in EXEC, so that they are
compiled after the new column was added. There is not really any
need to put the ALTER statement in EXEC though.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|