Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
KurtW
Starting Member
6 Posts |
 Posted - 2012-04-05 : 11:30:04
|
Hello all. I am attempting to run a T-SQL batch to fix some issues. These are the steps I am taking:1) Declare variables.2) Begin Tran.3) Add columns to existing table.4) Set value in one of the new columns.5) Test @@ERROR and @@ROWCOUNT.6) If success, delete some records from existing table.7) Test @@ERROR and @@ROWCOUNT.8) If success, Commit tran. If not, Rollback.9) Loop existing table based on value in new column:WHILE (SELECT COUNT(*) FROM dbo.Date2 WHERE Processed = 0) > 0] This errors out at step 9 telling me the column 'Processed' doesn't exist. However, if I run the batch and stop at step 8, everything works fine my debug output looks great: Created col Processed in dbo.Date2. Success: Set Processed = 1 for current record, acc: NM8 Success: DELETED NULL rows. Transaction commit!My guess is that even though I explicitly call COMMIT on the transaction, SQL Server is caching the final changes to the table and so the new columns are not available to code after step 8 until some other action, like ending the script hre, actually pushed the changes. I had assumed that COMMIT would do that, but apparently not.Am I correct here? If so, what can I do to "finalize" the committed changes so that they are available to code later in the batch?Thanks in advance.Kurt |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-04-05 : 11:47:23
|
| sorry why do you need to do DDL step inside transaction? does column need to be added only in some specific condition?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
KurtW
Starting Member
6 Posts |
Posted - 2012-04-05 : 11:49:56
|
| Visakh:It's there because the source data for that table is coming from an Excel spreadsheet that doesn't contain a few columns that will be needed later on. I guess I could work around the DDL step, but I always like to know why, if possible, workarounds are needed.Thanks!Kurt |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-04-05 : 12:08:54
|
| in any case you need columns right?then i think ddl statement should be outside transaction------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
KurtW
Starting Member
6 Posts |
Posted - 2012-04-05 : 12:12:14
|
| Visakh:Originally I tried creating the columns without the transaction and got the same results. In fact, the only reason I wrapped the DDL in the transaction was to try and have something to commit before attempting to access the new columns.Or are you suggesting that I just run the DDL somewhere else? I mean, not inside this batch? That is what I am currently doing, and it does work, but I'd still like to know WHY that has to be.Thanks.Kurt |
|
|
|
|
|
|
|
|
Table structure not updating in batch.