title: Finding the cause of a 3930 SQL Server error
body: <p>I have a script I wrote which is giving me a 3930 error on a particular line. It's a simple update statement and it fails when I run it in the script but if I print the variables from the script and run it manually the line executes fine. Does anybody know how I can track this error down?</p>
  
  <p>The line is executed in a cursor and runs hundreds of times correctly previous to this loop. I get the error if I let it process all the records or update my cursor query to select only this one.</p>
  
  <p>The full error message is:</p>
  
  <blockquote>
    <p><em>Msg 3930, Level 16, State 1, Line 160<br>
    The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.</em></p>
  </blockquote>
  
  <p>The offending line is:</p>
  
  <pre><code>UPDATE [DBName].[dbo].[TableName] 
  SET [XXX] = @XXX, [DateEdited] = GETDATE(), [EditedBy] = 'MyUser' 
  WHERE [YYY] = @YYY
  </code></pre>
  
  <p>EDIT:</p>
  
  <p>I sanitised the query so that I could post it here; see it below. A couple things to note. </p>
  
  <p>1) For Contractor "ABCDEF" there is only one record in [FactoryDB].[dbo].[AuditInformation] so cursor B is not necessary for this record. I tried removing the cursor and leaving all of the contained code but that fixed the error</p>
  
  <p>2) Removing the try-catch block that parses the age fixes the error as well even though it's not used by any of the remaining code. FYI that code does error and the catch block executes.</p>
  
  <pre><code>--TABLE TO HOLD ERROR MESSAGES
  declare @errors TABLE (FormId int, FactoryContractorCode nvarchar(50), FactoryId nvarchar(50), ErrorMessage nvarchar(1000) )
  
  --DECLARE VARIABLES
  DECLARE @factoryCode nvarchar(50)
  DECLARE @factoryId nvarchar(50)
  DECLARE @formId int
  DECLARE @auditDate DateTime
  DECLARE @totalEmployees int
  DECLARE @ageRange nvarchar(50)
  DECLARE @ageAverage int
  DECLARE @percentageFemale int
  DECLARE @percentageMale int
  DECLARE @rowIdToUpdate nvarchar(50)
  
  DECLARE @tempFormId int
  DECLARE @tempDateString nvarchar(50)
  DECLARE @tempAuditDate DateTime
  
  --CURSOR TO LOOP THROUGH ALL FACTORY CODES
  DECLARE a CURSOR FOR
      SELECT DISTINCT [FactoryCode] FROM [FactoryDB].[dbo].[AuditInformation] WHERE [FactoryCode] = 'ABCDEF'
  OPEN a
  FETCH a into @factoryCode
  WHILE(@@FETCH_STATUS = 0)
  BEGIN
  BEGIN TRANSACTION
  
      --RESET VARIABLES TO NULL
      SET @totalEmployees = NULL
      SET @ageRange = NULL
      SET @formId = NULL
      SET @rowIdToUpdate = NULL
      SET @tempAuditDate = NULL
      SET @auditDate = NULL
      SET @ageAverage = NULL
      SET @percentageFemale = NULL
      SET @percentageMale = NULL
      SET @tempFormId = NULL
      SET @tempDateString = NULL
      SET @factoryId = NULL
  
      SELECT @factoryId=[ContractorID] FROM [DataDB].[dbo].[ContractorIdentifier] WHERE [ContractorCode] = @factoryCode
  
      --FIND THE FORM ID WITH THE MOST RECENT DATE
      DECLARE b CURSOR FOR
          SELECT [FormId],[StartDate] FROM [FactoryDB].[dbo].[AuditInformation] WHERE [FactoryCode] = @factoryCode AND NOT [StartDate] IS NULL
      OPEN b
      FETCH b into @tempFormId, @tempDateString
      WHILE(@@FETCH_STATUS = 0)
      BEGIN
          --PARSE THE FORM DATE
          BEGIN TRY
              SET @tempAuditDate = CAST(@tempDateString AS DateTime)
          END TRY
          BEGIN CATCH
              INSERT INTO @errors ([FormId],[FactoryContractorCode],[FactoryId],[ErrorMessage]) VALUES (@tempFormId, @factoryCode, @factoryId, 'Unable to parse date value of: '''+@tempDateString+'''')
          END CATCH
  
          --FIRST TIME
          IF (@auditDate IS NULL)
          BEGIN
              SET @auditDate = @tempAuditDate
              SET @formId = @tempFormId
          END
  
          --UPDATE IF THE NEW DATE IS MORE RECENT THAN THE PREVIOUS ONE
          IF(@tempAuditDate &gt; @auditDate)
          BEGIN
              SET @auditDate = @tempAuditDate
              SET @formId = @tempFormId
          END
  
      FETCH b into @tempFormId, @tempDateString
      END
      CLOSE b
      DEALLOCATE b
  
      --IF A FORM WAS FOUND WITH A PARSABLE DATE
      IF (NOT @formId IS NULL)
      BEGIN
          --GET AUDIT INFORMATION
          SELECT TOP 1
              @totalEmployees = m.[TotarEmployees]
              ,@ageRange = m.[EmployeeAgeRange]
          FROM [FactoryDB].[dbo].[AuditInformation] g
          INNER JOIN [FactoryDB].[dbo].[AuditData] m on g._form_id = m._form_id
          WHERE g.[_form_id] = @formId
  
          --PARSE THE AVERAGE AGE FROM THE VARCHAR COLUMN
          BEGIN TRY
              DECLARE @low int
              DECLARE @high int
  
              SET @low = CAST(LTRIM(RTRIM(SUBSTRING(@ageRange,0,CHARINDEX('-',@ageRange)))) AS int)
              SET @high = CAST(LTRIM(RTRIM(SUBSTRING(@ageRange,CHARINDEX('-',@ageRange)+1,LEN(@ageRange)))) AS int)
              SET @ageAverage = ROUND((@low+@high)/2, 0)
          END TRY
          BEGIN CATCH
              INSERT INTO @errors ([FormId],[FactoryContractorCode],[FactoryId],[ErrorMessage]) VALUES (@formId, @factoryCode, @factoryId, 'Unable to parse age value of: '''+@ageRange+'''')
          END CATCH
      END
  
  
      --UPDATE THE FACTORY VIEW TABLE
      SELECT TOP 1 @rowIdToUpdate = [Id] FROM [DataDB].[dbo].[DataTable] WHERE [ContractorID] = @factoryId  AND [DateEdited] &lt; @auditDate ORDER BY [DateEdited] DESC
  
      IF (NOT @rowIdToUpdate IS NULL)
      BEGIN
  
          IF(NOT @totalEmployees IS NULL)
              UPDATE [DataDB].[dbo].[EmployeeStats] SET  [TotalEmployees] = @totalEmployees, [DateEdited] = GETDATE(), [EditedBy] = 'LstAD' WHERE [RowIDs] = @rowIdToUpdate
          ELSE
              INSERT INTO @errors ([FormId],[FactoryContractorCode],[FactoryId],[ErrorMessage]) VALUES (@formId, @factoryCode, @factoryId, 'Did not update Total Employees. Value was null or unable to be calculated.')
      END
  
  ROLLBACK
  FETCH a into @factoryCode
  END
  CLOSE a
  DEALLOCATE a
  
  --DO SOMETHING WITH THE ERRORS TABLE VARIABLE
  SELECT * FROM @errors
  </code></pre>