Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a encountered strange behaviour adding columns to an existing table. I run those scripts using osql several instances.

Let Table a_table have initially three columns A_col, B_col and C_col.

To extend this table I use the following statements:

IF NOT EXISTS (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS 
            WHERE COLUMN_NAME = N'D_col' AND TABLE_NAME = N'a_table')
BEGIN
ALTER TABLE [dbo].[a_table] ADD [D_col] int DEFAULT -1 NOT NULL
END

Up to now this script worked fine. I have a script adding two batches of columns (15 each) separated with a GO statement.

IF NOT ... -- col 1
...
END
:
:
IF NOT ... -- col 15
...
END
GO

IF NOT ... -- col 16
...
END
:
:
IF NOT ... -- col 30
...
END
GO

I expect the columns 1-15 to be added first and 16-30 second. But it happens from time to time that the second batch gets executed first so the columns appear in the wrong order (16-30, 1-15).

Any explanation for this behaviour is appreciated!

share|improve this question
2  
This would mean batch 2 is run before batch 1, no? –  gbn Jan 30 '12 at 12:06
    
I assume this. Although it would be really weird as both batches are in a single file! –  mrab Jan 30 '12 at 12:44
    
do you have the same table is 2 different schema eg dbo.table1 and wtf.table1? –  gbn Jan 30 '12 at 13:02
    
No. It's all in one schema and there is just one schema. –  mrab Jan 30 '12 at 13:06
    
How are you running the scripts? In SSMS? –  JNK Jan 30 '12 at 14:49
show 9 more comments

1 Answer

up vote 1 down vote accepted

Finally we were able to sort it out: It was a GO in a block comment /* ... GO ... */.

We tested the code in SSMS were the GO is ignored (as it is in a block comment). osql does not ignore it! We had the two batches in this block comment in a previous update. The first was ignored. The second was executed because the GO in the comment broke the comment.

Thanks to @gbn, @JNK and @Brandon for their help!

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.