Interesting Behavior of Alter Table

We've found something that seems odd for which we'd like an explanation.
We have a table defined as follows:
create table tester
(col1 int, col2 char(2), col3 int)
Our script executes something like this:
begin
alter table tester add id int not null constraint df_test default 0
alter table tester add id2 int identity
update tester
set id = id2
alter table tester drop column id2
end
The first time we run the script above, the following error occurs:
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'id'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'id2'.
All subsequent times in the same database, the code works as expected. Even
after the table is dropped and recreated, or we just drop the columns we
added and rerun it. Even after restarting query analyzer.
If I change the table name used to "tester2" and rerun the script, the error
is back. If I then drop that table and rerun the script, the errors
disappear.
Any ideas? We have worked around it by putting the schema altering
statements in their own batch, which is easy enough. But the behavior sure
seems erratic.
Thanks!
Steven Bras
Tessitura Network, Inc.have you tried it with GO in between the various sub steps , so you can send
each one as a transaction.?
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"StevenBr" <sbras@.community.nospam> wrote in message
news:F99CBBE7-4AF4-449A-B5EF-372115F37509@.microsoft.com...
> We've found something that seems odd for which we'd like an explanation.
> We have a table defined as follows:
> create table tester
> (col1 int, col2 char(2), col3 int)
> Our script executes something like this:
> begin
> alter table tester add id int not null constraint df_test default 0
> alter table tester add id2 int identity
> update tester
> set id = id2
> alter table tester drop column id2
> end
> The first time we run the script above, the following error occurs:
> Server: Msg 207, Level 16, State 1, Line 1
> Invalid column name 'id'.
> Server: Msg 207, Level 16, State 1, Line 1
> Invalid column name 'id2'.
> All subsequent times in the same database, the code works as expected.
Even
> after the table is dropped and recreated, or we just drop the columns we
> added and rerun it. Even after restarting query analyzer.
> If I change the table name used to "tester2" and rerun the script, the
error
> is back. If I then drop that table and rerun the script, the errors
> disappear.
> Any ideas? We have worked around it by putting the schema altering
> statements in their own batch, which is easy enough. But the behavior sure
> seems erratic.
> Thanks!
> Steven Bras
> Tessitura Network, Inc.|||When I run this in SQL server management studio I get the errors
everytime. After the first unsuccessful run has the table schema been
altered?|||I forgot to mention, as well as using GO , test it without the begin / end
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"StevenBr" <sbras@.community.nospam> wrote in message
news:F99CBBE7-4AF4-449A-B5EF-372115F37509@.microsoft.com...
> We've found something that seems odd for which we'd like an explanation.
> We have a table defined as follows:
> create table tester
> (col1 int, col2 char(2), col3 int)
> Our script executes something like this:
> begin
> alter table tester add id int not null constraint df_test default 0
> alter table tester add id2 int identity
> update tester
> set id = id2
> alter table tester drop column id2
> end
> The first time we run the script above, the following error occurs:
> Server: Msg 207, Level 16, State 1, Line 1
> Invalid column name 'id'.
> Server: Msg 207, Level 16, State 1, Line 1
> Invalid column name 'id2'.
> All subsequent times in the same database, the code works as expected.
Even
> after the table is dropped and recreated, or we just drop the columns we
> added and rerun it. Even after restarting query analyzer.
> If I change the table name used to "tester2" and rerun the script, the
error
> is back. If I then drop that table and rerun the script, the errors
> disappear.
> Any ideas? We have worked around it by putting the schema altering
> statements in their own batch, which is easy enough. But the behavior sure
> seems erratic.
> Thanks!
> Steven Bras
> Tessitura Network, Inc.|||Yes, that's essentially our workaround. Thanks.
--
Steven Bras
Tessitura Network, Inc.
"Jack Vamvas" wrote:

> have you tried it with GO in between the various sub steps , so you can se
nd
> each one as a transaction.?
>
> --
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> ___________________________________
>
> "StevenBr" <sbras@.community.nospam> wrote in message
> news:F99CBBE7-4AF4-449A-B5EF-372115F37509@.microsoft.com...
> Even
> error
>
>|||Hmm. We're using Query Analyzer against SQL 2k. And no, the alterations have
not been made.
This is very strange; to go back and check what you asked, I reran the same
script with a table name I've used before, and it worked without error.
Tried the same thing on a brand new table, and got the errors again.
Thanks!
--
Steven Bras
Tessitura Network, Inc.
"Will" wrote:

> When I run this in SQL server management studio I get the errors
> everytime. After the first unsuccessful run has the table schema been
> altered?
>|||Same problem, and we need those because it's in an IF block in the productio
n
script. I can leave that out and still repro the problem.
--
Steven Bras
Tessitura Network, Inc.
"Jack Vamvas" wrote:

> I forgot to mention, as well as using GO , test it without the begin / end
> --
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> ___________________________________
>
> "StevenBr" <sbras@.community.nospam> wrote in message
> news:F99CBBE7-4AF4-449A-B5EF-372115F37509@.microsoft.com...
> Even
> error
>
>|||This is what I have learned to do as well. I think that semicolons
following each statement also works, although I have not tested it in every
situation.
"Jack Vamvas" <delete_this_bit_jack@.ciquery.com_delete> wrote in message
news:9f2dneo6CqO3jv_ZRVnyiw@.bt.com...
> have you tried it with GO in between the various sub steps , so you can
send
> each one as a transaction.?
>
> --
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> ___________________________________
>
> "StevenBr" <sbras@.community.nospam> wrote in message
> news:F99CBBE7-4AF4-449A-B5EF-372115F37509@.microsoft.com...
> Even
> error
sure
>|||This is because how SQL Server parses the queries. When the parses sees the
text, the ALTER TABLE
haven't been executed yet. So this is why the parses throws the errors (as i
t tries to validate that
the columns exists).
Execute them in separate batches. Although the big question is why you creat
e columns at run-time...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"StevenBr" <sbras@.community.nospam> wrote in message
news:F99CBBE7-4AF4-449A-B5EF-372115F37509@.microsoft.com...
> We've found something that seems odd for which we'd like an explanation.
> We have a table defined as follows:
> create table tester
> (col1 int, col2 char(2), col3 int)
> Our script executes something like this:
> begin
> alter table tester add id int not null constraint df_test default 0
> alter table tester add id2 int identity
> update tester
> set id = id2
> alter table tester drop column id2
> end
> The first time we run the script above, the following error occurs:
> Server: Msg 207, Level 16, State 1, Line 1
> Invalid column name 'id'.
> Server: Msg 207, Level 16, State 1, Line 1
> Invalid column name 'id2'.
> All subsequent times in the same database, the code works as expected. Eve
n
> after the table is dropped and recreated, or we just drop the columns we
> added and rerun it. Even after restarting query analyzer.
> If I change the table name used to "tester2" and rerun the script, the err
or
> is back. If I then drop that table and rerun the script, the errors
> disappear.
> Any ideas? We have worked around it by putting the schema altering
> statements in their own batch, which is easy enough. But the behavior sure
> seems erratic.
> Thanks!
> Steven Bras
> Tessitura Network, Inc.|||I don't see it working when doing your steps:
create table tester
(col1 int, col2 char(2), col3 int)
go
--run the statement and get the error
begin
alter table tester add id int not null constraint df_test
default 0
alter table tester add id2 int identity
update tester
set id = id2
alter table tester drop column id2
end
go
--Step 1 run each statement manually
alter table tester add id int not null constraint df_test default 0
go
alter table tester add id2 int identity
go
update tester
set id = id2
go
alter table tester drop column id2
go
--Step 2 manually remove the default and ID
alter table tester drop constraint df_test
alter table tester drop column id
go
--Step 3 - rerun the code and the error appears for me
begin
alter table tester add id int not null constraint df_test
default 0
alter table tester add id2 int identity
update tester
set id = id2
alter table tester drop column id2
end
go
drop table tester
go