May 27, 2015 at 10:03 pm
I need some help to understand the process.
We are using sql server 2008r2. We have to run everyday one of the following statement through sql job
I have 2 questions here
1) Is this error handling is correct?
2) Do I need to create storeprocedure and execute this storeprocedure from sql job or directly I can use sql agent?
Which is best way of doing?
Declare @var datetime = getdate()+1
Begin try
begin tran
Update tableA set column1=5 and column2=@var
update tableB set column1=5 and column2=5
commit tran
End try
Begin catch
Rollback tran
End catch
May 28, 2015 at 12:17 pm
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
May 28, 2015 at 2:30 pm
SET XACT_ABORT ON
Add that line before you begin the transaction. With that setting, you may not even need a try/catch. The transaction will roll back on error.
May 28, 2015 at 2:53 pm
This is a well-documented "feature" of TRY...CATCH. From https://msdn.microsoft.com/en-us/library/ms175976.aspx:
The following types of errors are not handled by a CATCH block when they occur at the same level of execution as the TRY…CATCH construct:
Compile errors, such as syntax errors, that prevent a batch from running.
Errors that occur during statement-level recompilation, such as object name resolution errors that occur after compilation because of deferred name resolution.
These errors are returned to the level that ran the batch, stored procedure, or trigger.
(emphasis mine)
They actually then give an example of exactly this sort of thing, with a nonexistent table causing an error that bypasses the CATCH block.
To test this, you could cause the error to occur at a level of execution lower than the TRY...CATCH construct, either by putting the UPDATEs in a stored procedure and calling the procedure from the TRY...CATCH construct, or as in my quick example based on yours, by running it using sp_executesql:
DECLARE @RunDate DATETIME = DATEADD(hh,2,CAST(CAST(DATEADD(day,1,GETUTCDATE()) as DATE) as DATETIME))
DECLARE @sql nvarchar(max)='BEGIN TRANSACTION
UPDATE
[TableA]
SET
ScheduleDate = @RunDateInner
UPDATE
[Table B]
SET
ScheduleDate = @RunDateInner
COMMIT TRANSACTION'
BEGIN TRY
EXECUTE sp_executesql @statement=@sql,
@params=N'@RundateInner datetime',
@RunDateInner=@Rundate
END TRY
BEGIN CATCH
If @@TRANCOUNT >0
ROLLBACK tran
END CATCH
I hope this helps!
May 28, 2015 at 4:06 pm
Thank you.
Incase, if I use the correct table name TableB (If I didn't use any Xact_abort on and there are no syntax errors)
In case of any error occur, Is it rollback?
May 28, 2015 at 4:08 pm
ramana3327 (5/28/2015)
Thank you.Incase, if I use the correct table name TableB (If I didn't use any Xact_abort on and there are no syntax errors)
In case of any error occur, Is it rollback?
Best way to find out, in test environment set it up to fail and see what happens.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply