CODEWITHSUNDEEP
sql-server
pao
pao

Reputation: 1

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

I'm encountering this error when running my script in SQL Server 2008. But when I restore my database in SQL Server 2012, it runs successfully and I did not encounter any errors.

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

Here is my stored procedure:

CREATE PROCEDURE UpdateDependentViews  
(                                                              
 @TableName NVARCHAR(128),  
 @AllDependents bit = 1  
)                           
AS                                                              
    SET NOCOUNT ON;

    CREATE TABLE #Dependencies
    (
       [Counter] [int] IDENTITY(1,1) NOT NULL,
       [View_Name] [nvarchar](128),                          
    ) ON [PRIMARY];

    CREATE INDEX Counter ON #Dependencies(Counter);

    /* Get the first degree dependent views. */  
    INSERT INTO #Dependencies(View_Name)  
       SELECT V.[name] AS [View_Name]  
       FROM sys.sql_expression_dependencies SD  
       INNER JOIN sys.views V ON SD.referencing_id = V.object_id  
       INNER JOIN sys.objects D ON SD.referenced_id = D.object_id  
       WHERE SD.referencing_minor_id = 0  
         AND SD.referenced_minor_id = 0  
         AND SD.referencing_class = 1   
         AND D.type IN ('U', 'V')  
         AND D.is_ms_shipped = 0  
         AND V.is_ms_shipped = 0  
         AND D.[name] = @TableName  

    SELECT * 
    FROM sys.sql_expression_dependencies  
    WHERE referenced_entity_name IS NULL  

    /* Refresh the dependent views. */                          
    DECLARE @ViewName NVARCHAR(128)                          
    DECLARE @Counter INT  
SET @Counter = 1;  
DECLARE @Errors NVARCHAR(MAX)  
SET @Errors = ''  
WHILE EXISTS (SELECT [View_Name] FROM #Dependencies WHERE Counter = @Counter)                      
BEGIN                      
 SELECT @ViewName = View_Name FROM #Dependencies WHERE Counter = @Counter;                      
 /*Get Inner view dependencies */                      
 IF ISNULL(@AllDependents, 0) = 1   
 BEGIN     
  IF ISNULL(@AllDependents, 0) = 1 AND EXISTS(SELECT 1   
             FROM sys.sql_expression_dependencies SD   
             INNER JOIN sys.objects D ON SD.referenced_id =  D.object_id  
             WHERE SD.referencing_minor_id = 0  
              AND SD.referenced_minor_id = 0  
              AND SD.referencing_class = 1  
              AND D.type IN ('U', 'V')  
              AND D.is_ms_shipped = 0  
              AND D.[name] = @ViewName)  
  BEGIN  
   INSERT INTO #Dependencies(View_Name)  
   SELECT V.[name] AS [View_Name]  
   FROM sys.sql_expression_dependencies SD  
   INNER JOIN sys.views V  
    ON SD.referencing_id =  V.object_id  
   INNER JOIN sys.objects D  
    ON SD.referenced_id =  D.object_id  
   WHERE SD.referencing_minor_id = 0  
    AND SD.referenced_minor_id = 0  
    AND SD.referencing_class = 1   
    AND D.type IN ('U', 'V')  
    AND D.is_ms_shipped = 0  
    AND V.is_ms_shipped = 0  
    AND ISNULL(D.[name], '') <> ''    
    AND D.[name] = @ViewName  
    AND V.[name] NOT IN (SELECT View_Name FROM #Dependencies )        
  END  
 END;  

 /* Refresh the view */   
 BEGIN TRY  
     --BEGIN TRANSACTION  
  EXEC SP_REFRESHVIEW @ViewName  
  --COMMIT TRANSACTION    
 END TRY  
 BEGIN CATCH  
     --ROLLBACK TRANSACTION  
  IF EXISTS (SELECT 1 FROM [ISSIMODEL(15)].sys.objects WHERE [name] = @ViewName)  
   SET @Errors = @Errors + CHAR(13) + 'Error: Failed to RefreshView ' + @ViewName + '. Message: ' + ERROR_MESSAGE()   
 END CATCH  

 SET @Counter = @Counter + 1;  
END;  
IF ISNULL(@Errors, '') <> ''  
 RAISERROR (@Errors, 16, 1)  

DROP TABLE #Dependencies;

Upvotes: 0

Views: 6378

Answers (1)

knkarthick24
knkarthick24

Reputation: 3216

This often occurs when you use TRY\CATCH block in your code: Make sure you has this flow in your code:

DECLARE @ErrorMum INT 
DECLARE @ErrorMessage VARCHAR(200) 
DECLARE @Severity INT 
DECLARE @State INT 

BEGIN TRY

    BEGIN TRAN T1

    <Your Code goes here>

    COMMIT TRAN T1 
END TRY

BEGIN CATCH
    IF @@TRANCOUNT > 0 BEGIN 
    ROLLBACK TRAN T1 
    END

    SET @ErrorMum = ERROR_NUMBER()
    SET @ErrorMessage = ERROR_MESSAGE()
    SET @Severity = ERROR_SEVERITY()
    SET @State = ERROR_STATE()

    RAISERROR(@ErrorMessage, @Severity, @State)
END CATCH

Upvotes: 2

Related Questions