I executed a script that had multiple sections, separated by the
GO command, something like this:
Use DB1 GO ALTER TABLE foo ADD bar VARCHAR(10) NULL UPDATE foo SET bar = 'nananana' ALTER TABLE foo ALTER COLUMN bar VARCHAR(10) NOT NULL DROP TABLE oldtrash GO sp_rename 'zippidy.dodah', 'day', 'COLUMN'; GO USE DB2 GO sp_rename 'zing.bang', 'bong', 'COLUMN'; GO ALTER TABLE whiz ADD dum INT NULL DROP TABLE moretrash GO
When I ran it (all at once), I got a mix of success and error statements, something like:
(red text) Msg 207, Level 16, State 1, Line 6 Invalid column name 'bar'. (black text) Caution: Changing any part of an object name could break scripts and stored procedures. (red text) Msg 15248, Level 11, State 1, Procedure sp_rename, Line 238 [Batch Start Line 27] Either the parameter @objname is ambiguous or the claimed @objtype (COLUMN) is wrong.
I have some ideas about what went wrong here, but I have a few questions (hopefully these are more broadly applicable than just this one script):
- If an error refers to
Line 6, is that from the top of the script? Or from the top of the batch? (after the prior GO)
- If an error is encountered, does it roll back only the statements in that batch? (between the GOs)? So, if the
UPDATE foofails (because the column doesn't exist when the statement is evaluated), then none of the other statements in that batch commit, including the
DROP TABLE oldtrash?
- Would the order of the statements within the batch have mattered, if one of them errored out? For example, if my
DROP TABLEhad come before the
UPDATE foo, would it have run?
- The order of the output leads me to believe that the first
sp_renamesucceeded (with the "caution" output), and the second failed with the error. I'll check the tables, of course, but is that what you'd assume here? Or could the first
sp_renamehave both issued the caution and thrown the error?
- The failure of the prior batches doesn't prevent later batches from running, correct? So regardless of what happened above, my final
ALTER TABLE whizand
DROP TABLE moretrashlikely executed (and due to the lack of output, succeeded)?