January 6, 2013 at 5:38 pm
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??
January 6, 2013 at 6:31 pm
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 6, 2013 at 11:06 pm
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?
January 7, 2013 at 3:18 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply