3

This:

use test;

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;


BEGIN TRANSACTION;

EXEC sp_RENAME 'table1.asd' , 'ads', 'COLUMN';

INSERT INTO table1 (ads) VALUES (12);

COMMIT

is a simple example that demonstrates what I would like to do.

I want to alter the table in some way and perform inserts/deletes in one transaction (or other modifications to the table).

The problem is that the results from sp_RENAME are never immediately visible to the INSERT statement. I've played with different transaction isolation levels - it's always the same (therefore the transaction never commits).

Normally I would just use GO statements for this to be in separate batches, but I need that in one batch, because...

My real task is to write a script that adds identity and FK to a table (this requires creating another table with the new schema, performing identity inserts from the old one, renaming the table and applying constraints). I need to play it safe - if any part of the procedure fails I have to rollback the whole transaction. This is why I wanted to do something like this:

BEGIN TRAN
    --some statement

    IF (@@ERROR <> 0) GOTO ERR_HANDLER

    -- some other statement

    IF (@@ERROR <> 0) GOTO ERR_HANDLER

COMMIT TRAN

RETURN 0

ERR_HANDLER:
PRINT 'Unexpected error occurred!'
ROLLBACK TRAN
RETURN 1

Since labels work only inside a batch I cannot use GO statements.

So how can I:

  • make statements(ie. ALTER TABLE, sp_RENAME) have an immediate effect ?

or

  • write the whole solution some other way so that it is safe to run in production DB ?
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
kubal5003
  • 7,186
  • 8
  • 52
  • 90
  • Very much related: a script to create a table and inserting values. – ANeves Dec 19 '11 at 13:32
  • You say you are using SQL 2008, I just tried your first example on SQL Express 2008 and it worked, no errors were reported. All I added at the top of the script was 'CREATE TABLE table1 ( asd int ) GO' to create the table. The rest of your script ran ok. – Tony Dec 19 '11 at 13:39
  • @Tony - This is because if the table does not exist at all then the statements using it will get deferred compilation. The problem occurs when making changes to an existing table. Also you need to remove the `GO` from the OP's script to see the problem. – Martin Smith Dec 19 '11 at 13:49
  • Thanks, I haven't noticed this GO there. It's gone now. – kubal5003 Dec 19 '11 at 13:55
  • @MartinSmith - To test what you said, I dropped/created the table, then in a new window executed kubal5003's script without the `GO` and it still worked. Although it did produce the message `Caution: Changing any part of an object name could break scripts and stored procedures.` I appreciate this does not help resolve his problem but I was interested to test it out. – Tony Dec 19 '11 at 13:57
  • @Tony - I get `Invalid column name 'ads'.` when I try what you describe. – Martin Smith Dec 19 '11 at 14:01
  • @MartinSmith - I tried to capture the process in a video, have a look at: http://www.youtube.com/watch?v=bMCMWo6hXFA – Tony Dec 19 '11 at 14:24
  • @Tony - Can't see any reason why you wouldn't get an error there. What version are you on? I'm on 2008 R1. – Martin Smith Dec 19 '11 at 14:30
  • @MartinSmith - Doing `SELECT @@version` I get: `Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (X64) Apr 22 2011 19:23:43 Copyright (c) Microsoft Corporation Express Edition with Advanced Services (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)` – Tony Dec 19 '11 at 14:34
  • @Tony - OK think I've figured out the reason. If you do `dbcc freeproccache` do you suddenly start seeing the error when you run the batch? Basically if at any point you successfully manage to compile the batch then the problem disappears as following the schema changes to `table1` then just that statement gets marked as needing a recompile. – Martin Smith Dec 19 '11 at 15:41
  • @MartinSmith - Yep, clearing the cache after the table creation causes the statement to fail with `invalid column name`. I'd already up-voted your answer, shame I can't do that twice! Thanks for looking in to it. – Tony Dec 19 '11 at 16:53

1 Answers1

5

The issue is that the parsing of the batch fails when it encounters the reference to the renamed column so the entire batch never gets executed - not that the effects of the transaction are not visible.

You can put your statements referencing the new name of the column in an EXEC('') block to defer compilation until after the column is renamed.

EXEC sp_rename 'table1.asd' , 'ads', 'COLUMN';
EXEC('INSERT INTO table1 (ads) VALUES (12);')
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • Does it also apply to two subsequent `ALTER TABLE` statements? – kubal5003 Dec 19 '11 at 13:36
  • @kubal5003 - Yes. If they are in the same batch you will have the same problem. – Martin Smith Dec 19 '11 at 13:38
  • Thank you for the info. What do you think is the best approach to error handling in this case? The only thing that comes to my mind is separating statements with GO and duplicating everything that is under the ERR_HANDLER label each time I check for errors. – kubal5003 Dec 19 '11 at 13:46
  • @kubal5003 - There are a couple of ways to abort processing a script separated into batches you can either use `RAISERROR` or `SET NOEXEC ON`. This is covered in [this question](http://stackoverflow.com/questions/659188/sql-server-stop-or-break-execution-of-a-sql-script) – Martin Smith Dec 19 '11 at 13:57
  • `SET NOEXEC ON` just performs the compilation without executing the script and RAISEERROR is like a throw in C#. How can this help me? I just want to handle the transaction so it is rolled back if anything goes wrong. Right now I'll probably stick to copy-pasting error check & handle after each statement and then using GOs. – kubal5003 Dec 19 '11 at 21:35
  • @kubal5003 - If you have `XACT_ABORT` on and abort the batch your transaction will automatically roll back. – Martin Smith Dec 20 '11 at 11:32