Inline transaction not committing properly

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Inline transaction not committing properly

Inline transaction not committing properly

Rate Topic

Display Mode

Topic Options

Author

Message

matthew.peters

Posted Sunday, January 06, 2013 5:38 PM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, February 21, 2013 10:48 PM
Points: 36, Visits: 349

Hello
I'm getting the following error when trying to run the attached script

Msg 207, Level 16, State 1, Line 76
Invalid column name 'FSLReturnRate'.
Msg 207, Level 16, State 1, Line 76
Invalid column name 'FSLZone'.

To break it down for you, the script runs 2 alter table statements that add 2 columns FSLReturnRate and FSLZone then runs a bunch of insert statements to insert data into those new columns. This is all part of 1 transaction, so what I can see is happening is that the alter table statements are not committing before the insert statements. I did alter the script and put in a nested transaction around the alter table statements so they would commit before the inserts but received the same error. I have to wrap all this in one transaction due to error handling so as everything will get rolled back together in case something fails.
Also this actually runs fine on one of the databases on our server but fails on another database??

Post Attachments

Copy of MyPlace_108_1_1.txt (6 views, 5.82 KB)

Post #1403383

GilaMonster

Posted Sunday, January 06, 2013 6:31 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:52 AM
Points: 38,104, Visits: 30,395

matthew.peters (1/6/2013)

This is all part of 1 transaction, so what I can see is happening is that the alter table statements are not committing before the insert statements.?

The committing is not your problem. The problem is that it's a single batch. A batch is first parsed in its entirety, then optimised, then executed. At parse time, the alter table hasn't executed (it's being parsed) and hence when the parser gets to the queries that use the new columns, it throws a parse-time error because the columns don't exist.

Break your script into two pieces, one that does the DDL, then one that does the DML, and execute them separately.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1403387

matthew.peters

Posted Sunday, January 06, 2013 11:06 PM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, February 21, 2013 10:48 PM
Points: 36, Visits: 349

What I dont understand is why does it work on one database and not the other, is there a specific configuration setting that would effect this?

Post #1403409

GilaMonster

Posted Monday, January 07, 2013 3:18 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:52 AM
Points: 38,104, Visits: 30,395

matthew.peters (1/6/2013)

What I dont understand is why does it work on one database and not the other, is there a specific configuration setting that would effect this?

No, no config setting, no options.

About the only thing I can think of is that the columns exist in the tables on that other database already.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1403510

« Prev Topic | Next Topic »

Permissions