Databases
StackOverflow2013
Postsdbo
POFinding the cause of a 3930 SQL Server error
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POFinding the cause of a 3930 SQL Server error
    primarykey
    data
    text
    <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>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USWilliam
    1. USWilliam
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POFinding the cause of a 3930 SQL Server error
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POFinding the cause of a 3930 SQL Server error
      1. USTim Ferrell
      1. VTFavorite
    1. COAny update trigger on that table?
      singulars
      1. POFinding the cause of a 3930 SQL Server error
      1. USBlorgbeard
    2. COIf there isn't a trigger on the table, you'll need to show more code. Cursor declaration and transaction statements could be quite relevant, but the more details we have, the better we can help you. Right now we don't have enough details to guess.
      singulars
      1. POFinding the cause of a 3930 SQL Server error
      1. USAaron Bertrand
    3. COUsually, this error is preceded by another error that puts the transaction into an uncommitable state.
      singulars
      1. POFinding the cause of a 3930 SQL Server error
      1. USDamien_The_Unbeliever
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload