Database upgrade script alter followed by update fails

[2Fuzzy4U]

Hi, to upgrade our database we use an sql file that is executed. So far this has never caused any problems.

I'm using stored procedures to check if columns or indexes exist to be able to detemine if a peace of code should be executed. In this case I need to create a column and after that fill it with a value and than set it to not null

However, I get a column doesn't exist on the update statement. If I replace it with 'select * from password' than it showns me the column.
I've already used this same mechanism to create a table and then insert intitial rows.. That worked fine.

As we are using 'ADO' (am i saying that right?) to execute the script I'm not allowed to add GO statements. I'm testing my sql file in the Query analyser.

Can anyone tell me why this doesn't work, or what I am missing?

Coretta Tak
=============
DECLARE @IndexuritopcPCExists smallint

EXEC sp_check_index_exists 'sk_uritopc_pc',@IndexuritopcPCExists OUTPUT
IF @IndexuritopcPCExists = 0
BEGIN
ALTER TABLE passwords ADD pos1 smallint NULL
END

DECLARE @Pos1Exists smallint

EXEC sp_check_column_exists 'passwords', 'pos1', @Pos1Exists OUTPUT
IF @IndexuritopcPCExists = 0 AND @Pos1Exists > 0
BEGIN
UPDATE passwords
SET pos1 = Ascii( substring( passwd, 1,1) )
END

IF @IndexuritopcPCExists = 0 AND @Pos1Exists > 0
BEGIN
ALTER TABLE passwords ALTER COLUMN pos1 smallint NOT NULL

CREATE UNIQUE INDEX sk_uritopc_pc ON uritopc(pc)
END

 

[ClayG]

You need to split into into two scripts (in lieu of an GO).

The entire script (batch/between GOs) is COMPILED before any statement in it is executed. So at 'compile-time', there is no such column, and the update statement cannot compile.

If the table didnt exist before and you were creating it, you get away with it, because of 'deferred resolution', but it doesnt to individual columns.

Another way around it is to put your Update (and any other references to the column) into an Execute statement:

Execute 'Update passwords ...'

 

[2Fuzzy4U]

Thanks... not what I hoped for, but still. Know I know. I'm going to split the stuff into two files.

Coretta