September 28, 2016 at 6:25 am
The transaction is not getting commited or rollback when run in different db. The execution might throw error but its not commiting for below
BEGIN TRY
BEGIN TRANSACTION
UPDATE dbo.test
SETArrival= '20160928 11:30:00 AM',
Departure= '20160928 12:00:00 PM'
WHERE
ID= 1;
COMMIT
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
END CATCH
September 28, 2016 at 6:32 am
Rechana Rajan (9/28/2016)
The transaction is not getting commited or rollback when run in different db. The execution might throw error but its not commiting for belowBEGIN TRY
BEGIN TRANSACTION
UPDATE dbo.test
SETArrival= '20160928 11:30:00 AM',
Departure= '20160928 12:00:00 PM'
WHERE
ID= 1;
COMMIT
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
END CATCH
OK. What is your question, exactly?
Please post the full text of the error message, too.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 28, 2016 at 6:37 am
Phil Parkin (9/28/2016)
Rechana Rajan (9/28/2016)
The transaction is not getting commited or rollback when run in different db. The execution might throw error but its not commiting for belowBEGIN TRY
BEGIN TRANSACTION
UPDATE dbo.test
SETArrival= '20160928 11:30:00 AM',
Departure= '20160928 12:00:00 PM'
WHERE
ID= 1;
COMMIT
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
END CATCH
OK. What is your question, exactly?
Please post the full text of the error message, too.
Thanks Phil for the reply. What to know why the transaction is not commiting . Got below error while executing but the transaction will be in uncommited state.
Msg 208, Level 16, State 1, Line 3
Invalid object name 'dbo.test'.
September 28, 2016 at 6:43 am
Rechana Rajan (9/28/2016)
Phil Parkin (9/28/2016)
Rechana Rajan (9/28/2016)
The transaction is not getting commited or rollback when run in different db. The execution might throw error but its not commiting for belowBEGIN TRY
BEGIN TRANSACTION
UPDATE dbo.test
SETArrival= '20160928 11:30:00 AM',
Departure= '20160928 12:00:00 PM'
WHERE
ID= 1;
COMMIT
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
END CATCH
OK. What is your question, exactly?
Please post the full text of the error message, too.
Thanks Phil for the reply. What to know why the transaction is not commiting . Got below error while executing but the transaction will be in uncommited state.
Msg 208, Level 16, State 1, Line 3
Invalid object name 'dbo.test'.
This looks like a parse-time error - I doubt that the code even ran (and therefore, the code in the CATCH block would not have been executed).
One extra level of protection you can add to your code (before the BEGIN TRY) is
SET XACT_ABORT ON;
It will greatly reduce the chance that your code leaves open transactions hanging around.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 28, 2016 at 7:05 am
Phil Parkin (9/28/2016)
Rechana Rajan (9/28/2016)
Phil Parkin (9/28/2016)
Rechana Rajan (9/28/2016)
The transaction is not getting commited or rollback when run in different db. The execution might throw error but its not commiting for belowBEGIN TRY
BEGIN TRANSACTION
UPDATE dbo.test
SETArrival= '20160928 11:30:00 AM',
Departure= '20160928 12:00:00 PM'
WHERE
ID= 1;
COMMIT
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
END CATCH
OK. What is your question, exactly?
Please post the full text of the error message, too.
Thanks Phil for the reply. What to know why the transaction is not commiting . Got below error while executing but the transaction will be in uncommited state.
Msg 208, Level 16, State 1, Line 3
Invalid object name 'dbo.test'.
This looks like a parse-time error - I doubt that the code even ran (and therefore, the code in the CATCH block would not have been executed).
One extra level of protection you can add to your code (before the BEGIN TRY) is
SET XACT_ABORT ON;
It will greatly reduce the chance that your code leaves open transactions hanging around.
Thanks Phil. It would be great if I can get the reason why its in uncommited state so that i can share the reason with Devs.
September 28, 2016 at 7:20 am
It's a parse error and try-catch cannot catch parse errors.
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
September 28, 2016 at 7:38 am
Rechana Rajan (9/28/2016)
Phil Parkin (9/28/2016)
Rechana Rajan (9/28/2016)
Phil Parkin (9/28/2016)
Rechana Rajan (9/28/2016)
The transaction is not getting commited or rollback when run in different db. The execution might throw error but its not commiting for belowBEGIN TRY
BEGIN TRANSACTION
UPDATE dbo.test
SETArrival= '20160928 11:30:00 AM',
Departure= '20160928 12:00:00 PM'
WHERE
ID= 1;
COMMIT
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
END CATCH
OK. What is your question, exactly?
Please post the full text of the error message, too.
Thanks Phil for the reply. What to know why the transaction is not commiting . Got below error while executing but the transaction will be in uncommited state.
Msg 208, Level 16, State 1, Line 3
Invalid object name 'dbo.test'.
This looks like a parse-time error - I doubt that the code even ran (and therefore, the code in the CATCH block would not have been executed).
One extra level of protection you can add to your code (before the BEGIN TRY) is
SET XACT_ABORT ON;
It will greatly reduce the chance that your code leaves open transactions hanging around.
Thanks Phil. It would be great if I can get the reason why its in uncommited state so that i can share the reason with Devs.
It is not. The code does not even run.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 28, 2016 at 7:51 am
Phil Parkin (9/28/2016)
The code does not even run.
It does (try it, you'll end up with @@TRancount of 1), because of deferred compile.
When the batch is parsed, the table isn't there so its compilation is deferred until the first statement that refers to the table, that first parse doesn't throw an error (otherwise you could never create and use temp tables in the same batch). The Begin Try and Begin Transaction then execute, the table is referred to so the deferred compilation happens, the table still doesn't exist and so a parse error is thrown at that point, with an open transaction.
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
September 28, 2016 at 8:06 am
GilaMonster (9/28/2016)
Phil Parkin (9/28/2016)
The code does not even run.It does (try it, you'll end up with @@TRancount of 1), because of deferred compile.
When the batch is parsed, the table isn't there so its compilation is deferred until the first statement that refers to the table, that first parse doesn't throw an error (otherwise you could never create and use temp tables in the same batch). The Begin Try and Begin Transaction then execute, the table is referred to so the deferred compilation happens, the table still doesn't exist and so a parse error is thrown at that point, with an open transaction.
Thanks, Gail, for the detailed explanation.
I did try it, and @@TRANCOUNT was zero afterwards.
So I checked Tools/Options/Query Execution/SQL Server/Advanced
and see that I have
SET XACT_ABORT
checked ... so that was doing the sweeping up for me.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 28, 2016 at 8:10 am
I just did a quick check myself, running the query (Of course id do not have table) and strangely found a sleeping session with open transaction.
I ran the following query in a different window to check
Select open_transaction_count, * from sys.dm_exec_sessions where session_id=(my session id)
However I think this should be harmless since no real locks are held.
I found that Phil's suggestion of using XACT_ABORT ON resolves this issue. Its a strange behavior though.
Edit: Didn't see Gail's explanation. Makes sense. Thanks
September 28, 2016 at 8:16 am
Phil Parkin (9/28/2016)
So I checked Tools/Options/Query Execution/SQL Server/Advancedand see that I have
SET XACT_ABORT
checked ... so that was doing the sweeping up for me.
You have the latest SSMS 2016? XACT_ABORT has been turned on by default, in the last few releases, which is a change I really don't agree with.
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
September 28, 2016 at 8:23 am
GilaMonster (9/28/2016)
Phil Parkin (9/28/2016)
So I checked Tools/Options/Query Execution/SQL Server/Advancedand see that I have
SET XACT_ABORT
checked ... so that was doing the sweeping up for me.
You have the latest SSMS 2016? XACT_ABORT has been turned on by default, in the last few releases, which is a change I really don't agree with.
Confirmed.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply