Dropped column being "dropped" before drop ??

(SQL Server 2000 SP3, Developer Edition, Windows XP Professional)
I am getting an error "Invalid column name 'sys_login_name'." when running
the following query. The query is a minor conversion of a table between
versions. One column (userid_fk) had been added in a previous batch. In
this batch several columns are being dropped IF they exist. In the first
column 'sys_login_name', before it is dropped, data is pulled in from
another table (sy_user) before the sys_user.sys_login_name is dropped. I've
used col_length() as a quick way to detect if a column exists (returns null
if it doesn't exist). In the code below, note the section where "if
col_length('sys_user', 'sys_login_name') is not null" which means it only
gets executed when sys_login_name DOES exist. The UPDATE statement in that
IF block pulls data into sys_user from sy_user based upon the sys_login_name
field. The next statement then DROPS the sys_login_name field. In Query
Analyzer, I'm getting an "Invalid column name 'sys_login_name'" error that
points back to the UPDATE statement, but the rest of the batch is executing.
The output from Query Analyzer is:
=====OUTPUT
START===================================
====================================
====
doing v2->v3 on sys_user
updating sys_user
dropping sys_login_name
dropping other columns
Server: Msg 207, Level 16, State 3, Line 14
Invalid column name 'sys_login_name'.
=====OUTPUT
END=====================================
====================================
==
Oddly enough, the error is after the PRINT statement's output, but I've seen
that asynchronous-ness (?) of PRINT and error output enough before to not be
alarmed.
Here's the batch:
=====BATCH
START===================================
====================================
====
-- v2->v3: Check if sy integration changes need to be done STEP 2: convert
and drop fields
if dbo.fn_sy_get_table_version(N'sys_user') = 2
begin
print 'doing v2->v3 on sys_user'
-- if sys_login_name exists, pull data from sy_user for conversion
-- and drop sys_login_name
if col_length('sys_user', 'sys_login_name') is not null
begin
-- fill in userid_fk from sy_user.userid_pk via login name
-- and set password to 'test' for all accounts
-- before dropping login name; entry may not exist in sy_user
print 'updating sys_user'
update sys_user
set userid_fk = isnull(SY.userid_pk, 0),
sys_password = '098f6bcd4621d373cade4e832627b4f6'
from sys_user SYS left join sy_user SY on SYS.sys_login_name =
SY.login
print 'dropping sys_login_name'
alter table sys_user drop column sys_login_name
end
print 'dropping other columns'
-- drop sys_user_first if it exists
if col_length('sys_user', 'sys_user_first') is not null
alter table sys_user drop column sys_user_first
-- drop sys_user_last if it exists
if col_length('sys_user', 'sys_user_last') is not null
alter table sys_user drop column sys_user_last
-- drop timestamp if it exists
if col_length('sys_user', 'timestamp') is not null
alter table sys_user drop column [timestamp]
exec sp_sy_addextprops N'PPD_Version', 3, N'USER', N'dbo', N'TABLE',
N'sys_user'
end
go
=====BATCH
END=====================================
===================================
Here's a sample I did to test to see if ALTER TABLE DROP COLUMN somehow gets
executed before the UPDATE, but it doesn't:
=====SAMPLE
START===================================
====================================
=
create table testdrop
(
ident int identity(100,1) not null primary key,
col1 int null,
col2 int null
)
go
insert testdrop (col1, col2) values (1,2)
update testdrop set col2 = 22 where col1=1
select * from testdrop
alter table testdrop drop column col2
select * from testdrop
go
drop table testdrop
go
=====SAMPLE
END=====================================
===================================
Thanks for any help!
Mike JansenOK, I was able to reproduce the problem by enhancing my sample:
========= BEGIN SAMPLE ================
create table testdrop
(
ident int identity(100,1) not null primary key,
col1 int null,
col2 int null
)
go
create table testdrop2
(
pk int identity(100,1) not null primary key,
col1 int null
)
go
insert testdrop2 (col1) values (1)
insert testdrop2 (col1) values (2)
insert testdrop (col1, col2) values (1,0)
insert testdrop (col1, col2) values (2,0)
insert testdrop (col1, col2) values (3,0)
select * from testdrop
update testdrop
set col2 = T2.pk
from testdrop T1 left join testdrop2 T2 on T1.col1=T2.col1
select * from testdrop
--go
alter table testdrop drop column col2
select * from testdrop
go
drop table testdrop
drop table testdrop2
go
========= END SAMPLE ====================
Note that if you uncomment the one GO statement, it works. If the ALTER
TABLE DROP COLUMN is in the same batch as the UPDATE with the JOIN in the
FROM clause, it has the error.
Thanks,
Mike|||Does anyone have any idea about the following problem with the UPDATE
statement not working (get "Invalid Column" error) when you use a column in
the UPDATE's FROM clause (in a JOIN) and then drop that column via ALTER
TABLE in the same batch?
I can work around the problem, but I'd like to know if this is a SQL bug or
if I am ignorant of something fundamental in SQL Server (working with the
guys I work with, I had to qualify what I might be ignorant about or they
might pipe in all too quickly to confirm that I'm just ignorant <g> )
Thanks,
Mike
"Mike Jansen" <mjansen_nntp@.mail.com> wrote in message
news:ek5KSIaVFHA.1508@.tk2msftngp13.phx.gbl...
> OK, I was able to reproduce the problem by enhancing my sample:
> ========= BEGIN SAMPLE ================
> create table testdrop
> (
> ident int identity(100,1) not null primary key,
> col1 int null,
> col2 int null
> )
> go
> create table testdrop2
> (
> pk int identity(100,1) not null primary key,
> col1 int null
> )
> go
> insert testdrop2 (col1) values (1)
> insert testdrop2 (col1) values (2)
> insert testdrop (col1, col2) values (1,0)
> insert testdrop (col1, col2) values (2,0)
> insert testdrop (col1, col2) values (3,0)
> select * from testdrop
> update testdrop
> set col2 = T2.pk
> from testdrop T1 left join testdrop2 T2 on T1.col1=T2.col1
> select * from testdrop
> --go
> alter table testdrop drop column col2
> select * from testdrop
> go
> drop table testdrop
> drop table testdrop2
> go
> ========= END SAMPLE ====================
> Note that if you uncomment the one GO statement, it works. If the ALTER
> TABLE DROP COLUMN is in the same batch as the UPDATE with the JOIN in the
> FROM clause, it has the error.
> Thanks,
> Mike
>|||Dropping the column forces a recompile of the entire batch. That's why
you get an error. It's the expected behaviour.
For this and other reasons try to keep DDL and DML code entirely
separate. Put your ALTER statements in a separate batch.
David Portas
SQL Server MVP
--|||1. If it's recompiling the batch because of the DDL, why does my "simple
sample" work where I have an UPDATE with no FROM clause but I SET the column
and then drop it in the next line with an ALTER TABLE? If it were
recompiling the batch, I'd think that it would fail on that as well.
Here's a re-post of my simple sample that works:
==== BEGIN SAMPLE ==============
create table testdrop
(
ident int identity(100,1) not null primary key,
col1 int null,
col2 int null
)
go
insert testdrop (col1, col2) values (1,2)
update testdrop set col2 = 22 where col1=1
select * from testdrop
alter table testdrop drop column col2
select * from testdrop
go
drop table testdrop
go
==== END SAMPLE ==============
2. What are the other reasons for putting DDL and DML in separate batches
(besides the re-compile issue)?
Thanks for your help,
Mike
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1115815055.958304.192040@.g47g2000cwa.googlegroups.com...
> Dropping the column forces a recompile of the entire batch. That's why
> you get an error. It's the expected behaviour.
> For this and other reasons try to keep DDL and DML code entirely
> separate. Put your ALTER statements in a separate batch.
> --
> David Portas
> SQL Server MVP
> --
>|||I did a little research and found the answer to question #2 (What are the
other reasons for putting DDL and DML in separate batches - besides the
re-compile issue): It's related to the re-compile issue: performance. The
recompilation obviously causes performance issues. Since the compilation of
the batches is probably 1% or less of the time in the scenario I'm talking
about and it's a once-in-a-while script, that isn't really a significant
factor. I did end up changing my script though to put the DDL and DML in
separate batches since I'm still getting the "invalid column" error -- which
probably is related to the re-compiling (perhaps in the simple example
something is optimized in such a way that the batch didn't need to be
re-compiled ')
"Mike Jansen" <mjansen_nntp@.mail.com> wrote in message
news:O1nV6niVFHA.2420@.TK2MSFTNGP12.phx.gbl...
> 1. If it's recompiling the batch because of the DDL, why does my "simple
> sample" work where I have an UPDATE with no FROM clause but I SET the
column
> and then drop it in the next line with an ALTER TABLE? If it were
> recompiling the batch, I'd think that it would fail on that as well.
> Here's a re-post of my simple sample that works:
> ==== BEGIN SAMPLE ==============
> create table testdrop
> (
> ident int identity(100,1) not null primary key,
> col1 int null,
> col2 int null
> )
> go
> insert testdrop (col1, col2) values (1,2)
> update testdrop set col2 = 22 where col1=1
> select * from testdrop
> alter table testdrop drop column col2
> select * from testdrop
> go
> drop table testdrop
> go
> ==== END SAMPLE ==============
> 2. What are the other reasons for putting DDL and DML in separate batches
> (besides the re-compile issue)?
>
> Thanks for your help,
> Mike
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1115815055.958304.192040@.g47g2000cwa.googlegroups.com...
>