(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 Jansen

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

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

Show quoteHide quote

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.

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



Show quoteHide quote

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 ????)

Show quoteHide quote

Dynamic SQL with Datetime variables
automatical grow does not work
Better Way To Handle The Code?
Retrieving data from multiple tables
add column