SIngle transaction batch script without go's ???

Dietz

Hi There

I am trying to write a script that applies all changes to a database within a single transaction, eveything must be applied or not.

The issue i have is with the following:

BEGIN TRAN

INSERT INTO .....

UPDATE .....

ALTER TABLE A ADD COLUMN

INSERT INTO TABLE A VALUES ...

COMMIT

The issue i have is that the insert after the ALTER TABLE references the new column, which it says does nto exist, normally to get around this you should do a GO statement before the INSERT.

However if i insert a GO statement it breaks the script since it ends the batch so in essence all i am executing is:

BEGIN TRAN

INSERT INTO .....

UPDATE .....

ALTER TABLE A ADD COLUMN

GO

This is obviously incorrect since the syntax does not commit, also it breaks all the sql up into seprate transactions.

Same applies if i create a stored proc in the bacth, the sp must be the first statement in a sql batch and a GO must delimt the end of the sp create statement, but i cann make it 1 tarnsaction with GO's obviously.

Any ideas

Thanx

Chris Howarth

Transactions can span multiple batches, try running this as proof:

Code Snippet

SELECT @@TRANCOUNT

BEGIN TRANSACTION

SELECT ''

GO

SELECT @@TRANCOUNT

ROLLBACK

GO

Chris

Dietz

I am stumped.

I open 2 sessions to the same database as the same user

I execute the following on sessions 1:

==============================================================================

BEGIN

BEGIN TRAN

CREATE TABLE TEST5 (Col1 int);

INSERT INTO TEST5 VALUES (1);

ALTER TABLE dbo.TEST5 ADD Col2 bit NULL;

UPDATE TEST5 SET Col2 = 0 WHERE Col1 = 1;

INSERT INTO TEST5 VALUES (2,1);

COMMIT TRAN

END

==========================================================================================

Everything is successful.

On session 2 i run the following:

==========================================================================================

BEGIN

BEGIN TRAN

ALTER TABLE dbo.TEST5 ADD Col3 bit NULL;

UPDATE TEST5 SET Col3 = 0

COMMIT TRAN

END

==========================================================================================

I get the following error:

Invalid column name 'Col3'.

Why can i create a new table and do whatever i want to it in a transaction, but i cannot alter and existing table and update the new column ina transaction

Thanx

Dietz

Hi Chris

Ok but if you ar ein a BEGIN END it does not work.

Try this.

BEGIN

SELECT @@TRANCOUNT

BEGIN TRANSACTION

SELECT ''

GO

SELECT @@TRANCOUNT

ROLLBACK

GO

END

You will see you get a syntax erro because the GO terminates the sql batch before the END statement.

In my script i am in a IF statement within a BEGIN END, what then

Thanx

Chris Howarth

Well, adding GO after the ALTER TABLE statement works fine for me:

Code Snippet

BEGIN TRAN

ALTER TABLE dbo.TEST5

ADD Col3 bit NULL

GO

UPDATE TEST5 SET Col3 = 0

GO

COMMIT TRAN

You might want to have a look at the final answer in this thread for ideas on error handling when using DDL statements:

http://forums.microsoft.com/TechNet/ShowPost.aspx PostID=1594266&SiteID=17

Chris

Dietz

Hi Chris

Ok but if you are in a BEGIN END it does not work.

Try this.

BEGIN

SELECT @@TRANCOUNT

BEGIN TRANSACTION

SELECT ''

GO

SELECT @@TRANCOUNT

ROLLBACK

GO

END

You will see you get a syntax erro because the GO terminates the sql batch before the END statement of course.

In my script i am in a IF statement within a BEGIN END, what then

Thanx

Chris Howarth

Well that casts an entirely different light on the problem - you made no mention in your original post that this code is part of an IF statement.

The problem is not related to transactions spanning batches but to the fact that you're attempting to start a new batch within your IF statement's BEGIN END block.

You could try something like the example below.

Chris

Code Snippet

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL

DROP TABLE #tmp

GO

CREATE TABLE #tmp

(

TestID INT

)

GO

INSERT INTO #tmp(TestID)

VALUES (1)

GO

IF 1 = 1 --Insert your actual confition in here

BEGIN

BEGIN TRANSACTION

ALTER TABLE #tmp

ADD TestColumn INT

--Some form of error handling is required here

--ROLLBACK TRANSACTION

EXEC('UPDATE #tmp SET TestColumn = 5')

--Some form of error handling is required here

--ROLLBACK TRANSACTION

COMMIT TRANSACTION

END

GO

Dietz

Hi CHris

Yes my apologies, i was being a dunbass!

This works 100% , why does this work within an exec , but not within normal sql I find that very interesting.

Chris Howarth

It works because the SQL contained within the string that is executed via EXEC isn't parsed and compiled until the EXEC statement is reached during the execution of the batch, by which time the column referenced in the statement has already been added to the table.

Chris

Dietz

Cool make sense, thanx a million Chris

SIngle transaction batch script without go's ???

Re: SIngle transaction batch script without go's ???

Re: SIngle transaction batch script without go's ???

Re: SIngle transaction batch script without go's ???

Re: SIngle transaction batch script without go's ???

Re: SIngle transaction batch script without go's ???

Re: SIngle transaction batch script without go's ???

Re: SIngle transaction batch script without go's ???

Re: SIngle transaction batch script without go's ???

Re: SIngle transaction batch script without go's ???