SQL script runs fine on one database, errors on another

Question

We have a script that must allow for being re-run several times.

We have an MS-SQL script that updates a table if a (now obsolete) column exists, then deletes the column. To ensure that the script can be run several times, it first checks for the existence of a column before performing the updates.

The script works as expected on our dev database, updating the data on the first run, then displaying the message 'Not updating' on subsequent runs.

On our test database the script runs fine on the first run, but errors with "Invalid column name 'OldColumn'" on subsequent runs; if I comment out the UPDATE and ALTER statements it runs as expected.

Is there a way to force the script to run even if there's a potential error, or is it something to do with how the database was set-up? (fingers crossed I'm not looking like a complete noob!)

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'MyTable' AND COLUMN_NAME = 'OldColumn')

    BEGIN
        PRINT 'Updating and removing old column...'
        UPDATE MyTable SET NewColumn='X' WHERE OldColumn=1;
        ALTER TABLE MyTable DROP COLUMN OldColumn;
    END

ELSE
    PRINT 'Not updating'
GO

It's dropping a column and you can't figure out why it's not updating the column you dropped? — user114600
– user114600, Commented Mar 31, 2011 at 0:10
@Randolph - That isn't the OP's question at all. On subsequent runs when the column doesn't exist that code doesn't get executed. They are asking why the non executed code causes a compilation problem in one environment but not another. — Martin Smith
– Martin Smith, Commented Mar 31, 2011 at 0:45

Martin Smith · Accepted Answer · 2011-03-31 01:04:37Z

1

As a work around you could do

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'MyTable' AND COLUMN_NAME = 'OldColumn')
    BEGIN
        PRINT 'Updating and removing old column...'
        EXEC ('UPDATE MyTable SET NewColumn=''X'' WHERE OldColumn=1;');
        ALTER TABLE MyTable DROP COLUMN OldColumn;
    END

ELSE
    PRINT 'Not updating'

edited Mar 31, 2011 at 1:04

answered Mar 31, 2011 at 0:38

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

cooman Over a year ago

exec sp_dbcmptlevel 'dbname' displays 'The current compatibility level is 100.' for both databases

Martin Smith Over a year ago

@cooman - Yes I just began to doubt the answer myself. Compilation of the statement should only be deferred if it refers to a table that does not exist. Does the table itself definitely still exist in the dev environment?

cooman Over a year ago

@Martin - the table is definitely there; if I change the update to "UPDATE MyTable SET NewColumn='X';" and remove the DROP it correctly displays the 'Not updating' message

Martin Smith Over a year ago

That doesn't mean the table is still there. Compilation would be deferred and you would never actually enter the if block if it wasn't. I get the same error as you on my machine unless I wrap it in dynamic SQL so it executes in another batch. This is how it should work (ie is documented to work) according to my understanding of it as well. If the table is absolutely definitely there in your dev environment would be interesting to know @@version for both.

cooman Over a year ago

I've verified the table is there (I also copied the UPDATE into the ELSE to be sure) but now dev and test both error :( I luckily have other databases to test against, and I've found that changing the compatibility level to 90 reproduces the error, but resetting back to 100 doesn't put it all back as it was... I still get the error. Perhaps I should just convert to dynamic SQL (which I've tried and works as expected)

|

Collectives™ on Stack Overflow

SQL script runs fine on one database, errors on another

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related