1

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):

  1. 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)
  2. If an error is encountered, does it roll back only the statements in that batch? (between the GOs)? So, if the UPDATE foo fails (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?
  3. Would the order of the statements within the batch have mattered, if one of them errored out? For example, if my DROP TABLE had come before the UPDATE foo, would it have run?
  4. The order of the output leads me to believe that the first sp_rename succeeded (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_rename have both issued the caution and thrown the error?
  5. The failure of the prior batches doesn't prevent later batches from running, correct? So regardless of what happened above, my final ALTER TABLE whiz and DROP TABLE moretrash likely executed (and due to the lack of output, succeeded)?
1

After sufficient testing, I think I've answered all my questions:

  1. 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)

Errors are reported from the top of the entire script, not from the top of the batch.

One caveat: errors point to the line that starts the statement containing the error, even if the error itself is on a later line. For example, if badTableName doesn't exist, the following will report an error on line 12, not line 13:

SELECT *                 --line 12
FROM badTableName        --line 13
  1. If an error is encountered, does it roll back only the statements in that batch? (between the GOs)?

Yes. If an error is encountered, that entire batch (between the GOs) is rolled back.

  1. Would the order of the statements within the batch have mattered, if one of them errored out?

No, the order of commands within that block would not have mattered.

  1. The order of the output leads me to believe that the first sp_rename succeeded (with the "caution" output), and the second failed with the error.

After some testing, this appears to be correct. The Caution is only output when sp_rename completes successfully.

  1. The failure of the prior batches doesn't prevent later batches from running, correct?

Correct. Other batches are treated independently, and will be run.

2
  • 3
    In case you weren't aware, you can double-click the error message in the SSMS messages pane and it will take you to the erred statement in the query window. – Dan Guzman Sep 5 '18 at 1:33
  • @DanGuzman Really? Awesome tip, never knew that. Thanks! – BradC Sep 5 '18 at 13:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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