September 8, 2006 at 8:07 am
Hello All
I have the following TSQL
Begin Tran T1;
Begin Tran T2;
ALTER TABLE [MyTable] Add IDCOL int default 0;
Commit Tran T2;
Update [MyTable] Set IDCOL=75;
Commit Tran T1;
It Fails with Invalid column name 'IDCOL'. The actual update clause is a little more complex than presented here, but this gives the general idea. I have tried executing the statement both with and without the transactions and with and without the default constraint. If I run the alter statement seperate from the update statement everything works. This statement is being executed by a program using ADO.net so it kinda needs to run as a batch. It fails when run as a batch both in Ado.Net and SQL Management Studio [SQL 2005]. It appears to be a latency issue. Any ideas on what I need to do to get past this error?
Thanks a million
Steve
Steve
September 8, 2006 at 8:37 am
Have u tried this:
Begin Tran T2;
ALTER TABLE [MyTable] Add IDCOL int default 0;
Commit Tran T2;
Begin Tran T1;
Update [MyTable] Set IDCOL=75;
Commit Tran T1;
I am assuming since T2 is inside T1, Alter table is not commited when u are trying to add value to it.
Thanks
Sreejith
September 8, 2006 at 10:21 am
Yes I have. It seems to be a latency problem when running this as a batch.
Steve
September 8, 2006 at 11:41 am
try this
SET
TRANSACTION ISOLATION LEVEL READ COMMITTED
GO
Begin
Tran T2
ALTER
TABLE [MyTable] Add IDCOL4 int default 0;
Commit
Tran T2
GO
Begin
Tran T1
Update
[MyTable] Set IDCOL4=75;
Commit
Tran T1
September 8, 2006 at 11:46 am
Only problem with this is that GO throws a syntax error in ADO.NET
Steve
September 8, 2006 at 11:49 am
SET
TRANSACTION ISOLATION LEVEL READ COMMITTED
that was the trick, Thanks Gopi.
Steve
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply