Sql server. The current transaction cannot be committed and cannot support operations that write to the log file

nbewdwxp  于 2023-03-28  发布在  SQL Server
关注(0)|答案(2)|浏览(310)
begin tran
begin try
  select case when 1=0 then 0.0 else '' end --this will not work
end try
begin catch
--error has occured. But it doesnt matter. We want to continue anyway
end catch

select 1 --do something else

commit --unfortunatelly this producess error "The current transaction cannot be committed and cannot support operations that write to the log file"

What can I do to commit transaction? I know, select case when 1=0 then 0.0 else '' end is incorrect. Thats why it is in try/catch block (in real situations this is query defined by administrator). But I want to commit the rest of operations.

Edit:// The code works as I wanted if the "Incorrect query" is for example select 1/0

begin tran
begin try
  select 1/0-- this will not work
end try
begin catch
--error has occured. But it doesnt matter. We want to continue anyway
end catch

select 1--do something else
commit --commit is done without any errors
eivgtgni

eivgtgni1#

From this page :

"If an error generated in a TRY block causes the state of the current transaction to be invalidated, the transaction is classified as an uncommittable transaction. An error that ordinarily ends a transaction outside a TRY block causes a transaction to enter an uncommittable state when the error occurs inside a TRY block. An uncommittable transaction can only perform read operations or a ROLLBACK TRANSACTION."

From this page :

"SQL Server can tolerate some errors inside a transaction without it having to be marked as un-committable. For example SELECT 1/0 would cause an error but not force a transaction into an un-committable state."

a64a0gku

a64a0gku2#

you could try checking the transaction state before you commit or exit..

begin tran
begin try
    select case when 1=0 then 0.0 else '' end --this will not work
end try
begin catch
--error has occured. But it doesnt matter. We want to continue anyway
end catch

select 1 --do something else

IF (XACT_STATE()) = -1
    rollback tran
IF (XACT_STATE()) = 1
    commit tran

相关问题