Solvedwhat am I missing …the current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction

See more:

C#

SQL

SQL-Server

T-SQL

, +

**********Tried the syntax from the below link and its working like charm ***********

sql server - SQL Transaction Error: The current transaction cannot be committed and cannot support operations that write to the log file - Stack Overflow[^]


Below procedure is an example of the issue I'm facing in most of the stored procedures. Whenever there is an issue m trying to insert the error data into Log table in Catch statement. But

when I send the data from the front end form the code is breaking instead of returning value OutputID=0 and inserting data in log table.

In the below FirstName and LastName are not null values in the Patient table. So for testing purpose to log the details I'm sending null data for First & lastname.

Can you please help me where am i going wrong wrt below proc. I followed the syntax correctly, but still not working.Below is the error when I send the data from front end form.

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

Alter proc [dbo].[SavePatientDetails]                                                            
@PatientId int,                                                            
@PatientGroupId int,                                                            
@CommunicationTypeId int,                                                            
@PatientTypeId int,                                                        
@PatientConditionTypeId int,                                                        
@PatientSourceId int,                                                        
@Title varchar(10),                                                          
@Firstname varchar(60),                                                            
@LastName  varchar(60),                                                            
@DateofBirth datetime,                                                            
@Gender  varchar(30),                                                            
@NHS_No  varchar(30),                                                            
@Address1 varchar(100),                                                            
@Address2 varchar(100),                                                               
@City varchar(50),                                                            
@Town  varchar(50),                                                            
@Postcode  varchar(25),                                                            
@Country varchar(80),                                                            
@HomePhone varchar(100),  
@OutputId int out              
                                                         
as                                                            
Begin 

SET XACT_ABORT, NOCOUNT ON;
                                                           
                                    
Begin Try                 
              
Begin Transaction PatientDetails          
                                                                     
------------------------- Save Patient Details --------------------------- 
                                                      
Begin                            
                                                           
insert Patient(PatientGroupId,CommunicationTypeId,PatientTypeId,PatientConditionTypeId,PatientSourceId,Title,Firstname,LastName,
DateofBirth,Gender,NHS_No,Address1,Address2,City,Town,Postcode,Country,HomePhone)values                                                            
(@PatientGroupId,@CommunicationTypeId,@PatientTypeId,@PatientConditionTypeId,@PatientSourceId,@Title,@Firstname,@LastName,@DateofBirth
,@Gender,@NHS_No,@Address1,@Address2,@City,@Town,@Postcode,@Country,@HomePhone)                                    
                           
set @OutputId = SCOPE_IDENTITY()                                                          
End                         
                                                       
------------------------- Save Patient Details ---------------------------  
                        

                                                            
Commit Transaction PatientDetails ;                                                   
                                                            
End Try                                                                                                  
                   
Begin Catch                                                               
set @OutputId=0                                    
 insert into ErrorLogs                                     
                          
    SELECT                                   
    ERROR_NUMBER() AS ErrorNumber                                        
    ,ERROR_SEVERITY() AS ErrorSeverity                                        
    ,ERROR_STATE() AS ErrorState                                        
    ,ERROR_PROCEDURE() AS ErrorProcedure                                        
    ,ERROR_LINE() AS ErrorLine                                        
    ,ERROR_MESSAGE() AS ErrorMessage, '[SavePatientDetails]','AtlWholesales'     
     if @@TRANCOUNT > 0 Rollback  Transaction PatientDetails;  
                                                                                   
End Catch                                                                 
                                                             
End

What I have tried:

The syntax everything is right but still getting error.