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
2条答案
按热度按时间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."
a64a0gku2#
you could try checking the transaction state before you commit or exit..