ALTER TABLE from sqlcmd script

[Jeff_in_MD]

Hi,

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.

If need be I can just do a SELECT INTO and add the column there, but
it's a pain in the butt and I'm moving a LOT of data just to do what I
want. And no, I can't go back to where the table is created and add
the column. Does anyone have any suggestions? TIA!

- Jeff

[Jens]

Re: ALTER TABLE from sqlcmd script

Hi,

put a GO after the ALTER TABLEa nd you should be done.

HTH, Jens Suessmeyer.

---

http://www.sqlserver2005.de

---

[Erland Sommarskog]

Re: ALTER TABLE from sqlcmd script

Jeff_in_MD (jfowler@dsoftw are.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@sommarsk og.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