20

There is staging script, which creates new column DOCUMENT_DEFINITION_ID stages it with values of MESSAGE_TYPE_ID + 5 and then removes column MESSAGE_TYPE_ID.

First time everything run ok, but when I run script second time I'm getting this error:

Invalid column name 'MESSAGE_TYPE_ID'.

It makes no sense since, I have verification if that column exists.

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'MESSAGE_TYPE_ID' AND TABLE_NAME = 'DOCUMENT_QUEUE')
BEGIN
  UPDATE DOCUMENT_QUEUE SET DOCUMENT_DEFINITION_ID = MESSAGE_TYPE_ID + 5 --Error here.. but condition is not met

Why?

Andriy Buday
  • 1,959
  • 1
  • 17
  • 40

3 Answers3

26

Try this

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'MESSAGE_TYPE_ID' AND TABLE_NAME = 'DOCUMENT_QUEUE')
BEGIN
   EXEC( 'UPDATE DOCUMENT_QUEUE SET DOCUMENT_DEFINITION_ID = MESSAGE_TYPE_ID + 5 ')
END

It bypasses the delayed name resolution by wrapping the update in dynamic statement.

no_one
  • 1,852
  • 12
  • 11
0

Delayed resolution of names only happens on tables that don't exist. You will need to drop and create the entire table.

Zephyr was a Friend of Mine
  • 7,661
  • 4
  • 37
  • 39
-1

Also, you can cheat the SQL validator by appending the next code at the begging of your script:

IF 1 = 0
    alter table DOCUMENT_QUEUE add MESSAGE_TYPE_ID int NULL;

This code will never run but the SQL validator doesn't know about it. :)

Palindromer
  • 854
  • 1
  • 10
  • 29