Error Number 3930 Sql
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings sql server the current transaction cannot be committed and cannot support operations and policies of this site About Us Learn more about Stack Overflow
Msg 3930, Level 16, State 1
the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags
Xact_state()
Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only
Uncommittable Transaction Is Detected At The End Of The Batch. The Transaction Is Rolled Back.
takes a minute: Sign up Finding the cause of a 3930 SQL Server error up vote 2 down vote favorite 1 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 xact_abort the script and run it manually the line executes fine. Does anybody know how I can track this error down? 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. The full error message is: Msg 3930, Level 16, State 1, Line 160 The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction. The offending line is: UPDATE [DBName].[dbo].[TableName] SET [XXX] = @XXX, [DateEdited] = GETDATE(), [EditedBy] = 'MyUser' WHERE [YYY] = @YYY EDIT: I sanitised the query so that I could post it here; see it below. A couple things to note. 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 2) Removing the try-catch block that parses the age fixes the
log in tour help Tour Start here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta @@trancount Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Database Administrators Questions Tags Users Badges Unanswered Ask Question _ Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database http://stackoverflow.com/questions/12101005/finding-the-cause-of-a-3930-sql-server-error skills and learn from others in the community. Join them; it only takes a minute: Sign up Here's how it works: Anybody can ask a question Anybody can answer The best answers are voted up and rise to the top Finding exact statement causing error 3930 up vote 4 down vote favorite 1 Lately I've been seeing errors http://dba.stackexchange.com/questions/28807/finding-exact-statement-causing-error-3930 in the SQL Server Log. Code:3930 Description:"The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction." Using SQL Profiler, how can I find the exact statement that is causing this error? Which column in SQL Profiler fills the "Message" column in the SQL Error log? Unfortunately, we are only getting this in production, so I will need to run a trace there with the lightest weight trace possible. What do you folks recommend to capture the offending code? sql-server profiler error-handling tracing share|improve this question edited Nov 16 '12 at 17:32 Aaron Bertrand♦ 114k14196335 asked Nov 16 '12 at 16:22 SomeGuy 63521127 2 What version of SQL Server? The lightest weight trace possible would probably not be a trace at all but extended events. –Martin Smith Nov 16 '12 at 17:26 This server is on SQL Server 2005. –SomeGuy Nov 16 '12 at 17:41 add a comment| 1 Answer 1 active oldest votes up vote 6 down vote acc
(Русский)ישראל (עברית)المملكة العربية السعودية (العربية)ไทย (ไทย)대한민국 (한국어)中华人民共和国 (中文)台灣 (中文)日本 (日本語) HomeLibraryLearnDownloadsTroubleshootingCommunityForums Ask a question Quick access Forums home Browse forums users FAQ Search related threads Remove From My Forums Answered by: try catch in https://social.msdn.microsoft.com/Forums/sqlserver/en-US/60b3d3e9-1dd7-431c-983f-866ffae30126/try-catch-in-trigger-giving-msg-3930-the-current-transaction-cannot-be-committed-error?forum=transactsql trigger giving Msg 3930 The current transaction cannot be committed error SQL Server > Transact-SQL Question 0 Sign in to vote hi everyone,I have a trigger that fires https://scn.sap.com/thread/3323217 on update. In the trigger I check if the input is valid with a function and catch the error from function in catch block and write the error number error to log file. however when it tries to insert into error log table it gives the below error. Msg 3930, Level 16, State 1, Procedure sLogError, Line 22The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.The statement has been terminated.Code:Trigger BEGIN TRY error number 3930 -- CHECK IF THE DATA FOR THAT SERIAL NO IS OK EXEC dbo.sCheckSerialNumberInfo @SerialNo -- do some other things END TRY BEGIN CATCH IF XACT_STATE() = -1 ROLLBACK --PRINT XACT_STATE() DECLARE @ErrNo INT, @ErrSeverity INT, @ErrState INT, @ErrLine INT DECLARE @ErrProcedure NVARCHAR(126) DECLARE @ErrMessage NVARCHAR(2048) DECLARE @CustomErrMessage NVARCHAR(512) SELECT @ErrNo = ERROR_NUMBER(), @ErrSeverity = ERROR_SEVERITY(), @ErrState = ERROR_STATE(), @ErrLine = ERROR_LINE(), @ErrProcedure = ERROR_PROCEDURE(), @ErrMessage = ERROR_MESSAGE() SET @CustomErrMessage = 'Error in tblorders_units Update Trigger' EXEC dbo.sLogError @ErrNo, @ErrSeverity, @ErrState, @ErrLine, @ErrProcedure, @ErrMessage, @CustomErrMessage END CATCH CheckInput function -- Declare the return variable here DECLARE @RetVal INT -- find RetVal -- IF THERE IS AN ERROR
IF @RetVal = 1 BEGIN -- RAISE ERROR SO CATCH IN TRIGGER CAN PROCESS THE ERROR RAISERROR ('Gtin, lot number, manufacture date or expiry date for Serial No %s is invalid', 16, 1, @SerialNo) END -- Return the result of the function RETURN @RetVal LogError function INSERT INTO dbo.ERROR_LOG ([ErrorNumber] ,[ErrorSeverity] ,[ErrorState] ,[ErrorLine] ,[ErrorProcedure] ,[ErrorMessage] ,[CustomErrorMessag
and SafetyAsset NetworkAsset Operations and MaintenanceCommerceOverviewSubscription Billing and Revenue ManagementMaster Data Management for CommerceOmnichannel CommerceFinanceOverviewAccounting and Financial CloseCollaborative Finance OperationsEnterprise Risk and ComplianceFinancial Planning and AnalysisTreasury and Financial Risk ManagementHuman ResourcesOverviewCore Human Resources and PayrollHuman Capital AnalyticsTalent ManagementTime and Attendance ManagementManufacturingOverviewManufacturing NetworkManufacturing OperationsResponsive ManufacturingMarketingOverviewMarket with Speed and AgilityUnique Customer ExperiencesReal-Time Customer InsightsR&D/EngineeringOverviewDesign NetworkDesign OrchestrationProject and Portfolio ManagementSalesOverviewCollaborative Quote to CashSales Force AutomationSales Performance ManagementSelling Through Contact CentersServiceOverviewEfficient Field Service ManagementOmnichannel Customer ServiceTransparent Service Process and OperationsSourcing and ProcurementOverviewContingent Workforce ManagementDirect ProcurementSelf-Service ProcurementServices ProcurementStrategic Sourcing and Supplier ManagementSupply ChainOverviewDemand ManagementDemand NetworkLogistics NetworkManufacturing Planning and SchedulingResponse NetworkResponse and Supply ManagementSales, Inventory, and Operations PlanningTransportation ManagementWarehouse ManagementSustainabilityOverviewEnvironment, Health, and SafetyProduct Safety and Stewardship NetworkBrowse by IndustryAerospace and DefenseAutomotiveBankingChemicalsConsumer ProductsDefense and SecurityEngineering, Construction, and OperationsHealthcareHigh TechHigher Education and ResearchIndustrial Machinery and ComponentsInsuranceLife SciencesMediaMill ProductsMiningOil and GasProfessional ServicesPublic SectorRetailSports and EntertainmentTelecommunicationsTravel and TransportationUtilitiesWholesale DistributionBrowse Platform and TechnologyAnalyticsOverviewBusiness IntelligencePredictive AnalyticsGovernance, Risk, and ComplianceEnterprise Performance ManagementApplication Platform and InfrastructureOverviewApplication PlatformContent and CollaborationInterface ManagementProcess Management and IntegrationData ManagementOverviewBig Data on HadoopData WarehousingDatabase ManagementEnterprise Information ManagementIT ManagementOverviewApplication Lifecycle ManagementIT Infrastructure ManagementSecurity SoftwareOverviewApplication and IT Infrastructure SecurityIdentity, Access and Authentication ManagementBrowse by ProductAnalyticsContent and CollaborationCustomer Relationship ManagementData ManagementEnterprise Man