Hi,

I write the transaction below. I use Table B instead of TableB (i.e. table name wrong). It is updating TableA and then saying invalid objectname Table B. When I exectured

select SchedulDate from TableA , it is already updated and the rollback is not happening. It is showing blocking from dbcc opentran. When I again execute rollback tran it is going away. I need to write while updating tableA or tableB failed, that has to rollback.

I suspect it might not doing in the right way

DECLARE @RunDate DATETIME = DATEADD(hh,2,CAST(CAST(DATEADD(day,1,GETUTCDATE()) as DATE) as DATETIME))

BEGIN TRY

BEGIN TRANSACTION

UPDATE

[TableA]

SET

ScheduleDate = @RunDate

UPDATE

[Table B]

SET

ScheduleDate = @RunDate

COMMIT TRANSACTION

END TRY

BEGIN CATCH

If @@TRANCOUNT >0

ROLLBACK tran

END CATCH