Insert Trigger - Foreign Key Violation - Un-Committable transaction - SQL Server 2005

Topics: Data Access Application Block, Enterprise Library Core, General discussion, Validation Application Block
Sep 25, 2010 at 7:44 AM

I am facing a problem in “After Insert Trigger” using “Enterprise Data Library” in SQL Server 2005.

I have 2 tables. Whenever I insert into Table 1, I have written a trigger to insert into Table 2. There is a stored procedure to insert in to Table 1 and there is an After Insert Trigger to insert into Table 2. The table 2 has one primary key and one foreign key.

The trigger is working properly if there is no foreign key or unique key violation. But If there is a foreign key constraint or unique key constraint violation, I am not able to catch the error "Foreign Key Violation". Instead I am getting an error called "Un-Committable transaction…".

But I am not facing the same issue "Un-Committable transaction…", If I run the same stored procedure using direct SQL connection string (without Enterprise Data Library), 

Is there any FIX to resolve this?

Note: I have tried this in Version 3.0 and 4.0 also.

Thanks and Regards,

Peri

Sep 27, 2010 at 4:31 AM

How exactly are you executing the insert statement that fires the trigger which gives you the Uncommittable transaction error?  I'm also confused if you want to catch the Foreign key violation error or the uncommittable transaction error.

 

Sarah Urmeneta
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com

Sep 27, 2010 at 9:48 AM

 

I would like to Catch Foreign Key Violation error. But I am getting "Un-Committable transaction…".

Attached is the detailed sql scripts for you to reproduce the error. Please see at the end of the post also.

/*----------------------------------------Table/Trigger/Stored Procedure Creating Scripts Start----------------------------------------------*/

--Table For Region View Table

CREATE TABLE [dbo].[RegionView]( [RegionID] [int] NOT NULL, [RegionShortName] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [RegionName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Notes] [varchar](7000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [ApprovalID] [int] NULL, [CanOperate] [bit] NULL, [CreatorSessionID] [int] NULL, [CreatorID] [int] NOT NULL, [CreatedDateTime] [datetime] NOT NULL, [ModifierSessionID] [int] NOT NULL, [ModifierID] [int] NOT NULL, [ModifiedDatetime] [datetime] NOT NULL, [LastChanged] [timestamp] NOT NULL, CONSTRAINT [PK_RegionView_1] PRIMARY KEY CLUSTERED ( [RegionID] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY], CONSTRAINT [UQ__RegionView__1530FE3E] UNIQUE NONCLUSTERED ( [RegionShortName] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]

GO

--Table for Country

CREATE TABLE [dbo].[Country]( [CountryID] [int] IDENTITY(1,1) NOT NULL, [Active] [bit] NOT NULL, [CreatorSessionID] [int] NOT NULL, [CreatorID] [int] NOT NULL, [CreatedDateTime] [datetime] NOT NULL, [ModifierSessionID] [int] NOT NULL, [ModifierID] [int] NOT NULL, [ModifiedDatetime] [datetime] NOT NULL, [LastChanged] [timestamp] NOT NULL, CONSTRAINT [PK_Country] PRIMARY KEY CLUSTERED ( [CountryID] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]

GO


--Table For CountryDetails

CREATE TABLE [dbo].[CountryDetails]( [IDCol] [int] IDENTITY(1,1) NOT NULL, [CountryID] [int] NOT NULL, [RegionID] [int] NOT NULL, [CountryShortName] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [CountryName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [Notes] [varchar](7000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [ApprovalID] [int] NOT NULL, [Active] [bit] NOT NULL, [CanOperate] [bit] NOT NULL, [ModifierSessionID] [int] NOT NULL, [ModifierID] [int] NOT NULL, [EffectiveFromDate] [datetime] NOT NULL, [EffectiveToDate] [datetime] NOT NULL, [LastChanged] [timestamp] NOT NULL, CONSTRAINT [PK_CountryDetails] PRIMARY KEY CLUSTERED ( [IDCol] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]

GO


--Table For CountryView

CREATE TABLE [dbo].[CountryView]( [CountryID] [int] NOT NULL, [RegionID] [int] NULL, [CountryShortName] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [CountryName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Notes] [varchar](7000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [ApprovalID] [int] NULL, [CanOperate] [bit] NULL, [CreatorSessionID] [int] NULL, [CreatorID] [int] NOT NULL, [CreatedDateTime] [datetime] NOT NULL, [ModifierSessionID] [int] NOT NULL, [ModifierID] [int] NOT NULL, [ModifiedDatetime] [datetime] NOT NULL, [LastChanged] [timestamp] NOT NULL, CONSTRAINT [PK_CountryView] PRIMARY KEY CLUSTERED ( [CountryID] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY], CONSTRAINT [UK_CountryShortName] UNIQUE NONCLUSTERED ( [CountryShortName] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]

GO

ALTER TABLE [dbo].[CountryView]  WITH CHECK ADD  CONSTRAINT [FK_CountryView_Country] FOREIGN KEY([CountryID])REFERENCES [dbo].[Country] ([CountryID])

GO

ALTER TABLE [dbo].[CountryView]  WITH CHECK ADD  CONSTRAINT [FK_CountryView_RegionView] FOREIGN KEY([RegionID])REFERENCES [dbo].[RegionView] ([RegionID])

GO

 

--Country Trigger For Inserting View Table

CREATE TRIGGER [tri_CountryInsertTrigger] ON [dbo].[Country]

AFTER INSERT

AS

BEGIN

      INSERT INTO [CountryView](

            [CountryID],

            [CreatorSessionID],

            [CreatorID],

            [CreatedDateTime],

            [ModifierSessionID],

            [ModifierID],

            [ModifiedDatetime])

      SELECT

            [CountryID],

            [CreatorSessionID],

            [CreatorID],

            [CreatedDateTime],

            [ModifierSessionID],

            [ModifierID],

            [ModifiedDatetime]

      FROM

            Inserted

END

GO

 

--CountryDetails Trigger For Updating View Table

CREATE TRIGGER [tri_CountryDetailsInsertTrigger] ON [dbo].[CountryDetails]

AFTER INSERT

AS

BEGIN

      DECLARE

      @CountryID INT,

      @RegionID INT,

      @CountryShortName VARCHAR(20),

      @CountryName VARCHAR(100),

      @Notes VARCHAR(7000),

      @ApprovalID INT,

      @CanOperate BIT,

      @ModifierSessionID INT,

      @ModifierID INT,

      @ModifiedDateTime DATETIME

IF EXISTS(SELECT 1 FROM Inserted WHERE [Active] = 1)

BEGIN

      SELECT

            @CountryID = [CountryID],

            @RegionID = [RegionID],

            @CountryShortName = [CountryShortName],

            @CountryName = [CountryName],

            @Notes = [Notes],

            @ApprovalID = [ApprovalID],

            @CanOperate = [CanOperate],

            @ModifierSessionID = [ModifierSessionID],

            @ModifierID = [ModifierID],

            @ModifiedDateTime = EffectiveFromDate

      FROM

            INSERTED

       UPDATE

            [CountryView]

      SET

            [CountryID] = @CountryID,

            [RegionID] = @RegionID,

            [CountryShortName] = @CountryShortName,

            [CountryName] = @CountryName,

            [Notes] = @Notes,

            [ApprovalID] = @ApprovalID,

            [CanOperate] = @CanOperate,

            [ModifierSessionID] = @ModifierSessionID,

            [ModifierID] = @ModifierID,

            [ModifiedDateTime] = @ModifiedDateTime

      WHERE

            [CountryID] = @CountryID

       UPDATE

            [Country]

      SET   [ModifierSessionID] = @ModifierSessionID,

            [ModifierID] = @ModifierID,

            [ModifiedDateTime] = @ModifiedDateTime

      WHERE

            [CountryID] = @CountryID

       END

 END

GO

--Stored Procedure to Insert to Country Table

CREATE PROCEDURE [dbo].[usp_CountryInsert] (   @CreatorSessionID INT,   @CreatorID INT,   @CreatedDateTime DATETIME,   @ModifierSessionID INT,   @ModifierID INT,   @ModifiedDatetime DATETIME,   @CountryID INT OUTPUT,   @ErrorLogID int = 0 OUTPUT  )  AS  BEGIN      SET NOCOUNT ON;      BEGIN TRY          INSERT INTO [Country] (           [Active],           [CreatorSessionID],           [CreatorID],           [CreatedDateTime],           [ModifierSessionID],           [ModifierID],           [ModifiedDatetime]          ) VALUES (           '1',           @CreatorSessionID,           @CreatorID,           @CreatedDateTime,           @ModifierSessionID,           @ModifierID,           @ModifiedDatetime          );          SET @CountryID = @@IDENTITY          SET @ErrorLogID = 0      END TRY      BEGIN CATCH   /*DECLARE @ErrorLogIDRecd INT;   EXECUTE [dbo].[usp_LogError]   @UserID = @ModifierID,   @ErrorLogID=@ErrorLogIDRecd  OUTPUT   SET @ErrorLogID = @ErrorLogIDRecd  */
        PRINT ERROR_MESSAGE()        SET @ErrorLogID = ERROR_NUMBER()    END CATCH;  END

GO

--Stored Procedure to Insert to Country Details Table

CREATE PROCEDURE [dbo].[usp_CountryDetailsInsert] (   @CountryID INT,   @RegionID INT,   @CountryShortName VARCHAR(20),   @CountryName VARCHAR(100),   @Notes VARCHAR(7000),   @ApprovalID INT,   @Active BIT,   @CanOperate BIT,   @ModifierSessionID INT,   @ModifierID INT,   @EffectiveFromDate DATETIME,   @EffectiveToDate DATETIME,   @IDCol INT OUTPUT,   @ErrorLogID INT = 0 OUTPUT  )  AS  BEGIN   SET NOCOUNT ON;   BEGIN TRY    IF EXISTS(SELECT 1 FROM [CountryDetails] WHERE [CountryShortName] = @CountryShortName  AND Active = 1)     BEGIN      RAISERROR(3, 16, 1)     END    Else     BEGIN        INSERT INTO [CountryDetails] (       [CountryID],       [RegionID],       [CountryShortName],       [CountryName],       [Notes],       [ApprovalID],       [Active],       [CanOperate],       [ModifierSessionID],       [ModifierID],       [EffectiveFromDate],       [EffectiveToDate]      ) VALUES (       @CountryID,       @RegionID,       @CountryShortName,       @CountryName,       @Notes,       @ApprovalID,       @Active,       @CanOperate,       @ModifierSessionID,       @ModifierID,       @EffectiveFromDate,       @EffectiveToDate     );      SET @IDCol = @@IDENTITY      SET @ErrorLogID = 0     END   END TRY   BEGIN CATCH   /*DECLARE @ErrorLogIDRecd INT;   EXECUTE [dbo].[usp_LogError]   @UserID = @ModifierID,   @ErrorLogID=@ErrorLogIDRecd  OUTPUT   SET @ErrorLogID = @ErrorLogIDRecd  */
PRINT ERROR_MESSAGE()    SET @ErrorLogID = ERROR_NUMBER() END CATCH;  END

GO

/*----------------------------------------Table/Trigger/Stored Procedure Creating Scripts End----------------------------------------------*/

 

/*----------------------------------------To Test the Foreign Key Violation start---------------------------------------------*/

 DECLARE

      @CountryID INT,

      @RegionID INT,

      @CountryShortName VARCHAR(20),

      @CountryName VARCHAR(100),

      @Notes VARCHAR(7000),

      @ApprovalID INT,

      @Active BIT,

      @CanOperate BIT,

      @ModifierSessionID INT,

      @ModifierID INT,

      @ModifieddateTime DATETIME,

      @MaxDateTime DATETIME,

      @IDCol INT,

      @ErrorLogID INT;

 

SET @RegionID = 101

SET @CountryShortName = 'SG'

SET @CountryName = 'Singapore'

SET @Notes = ''

SET @ApprovalID = -1

SET @Active = 1

SET @CanOperate = 1

SET @ModifierSessionID = 1

SET @ModifierID = 51

SET @ModifieddateTime = GetDate()

SET @MaxDateTime = '31-Dec-9999'

 

--This will insert a record to the country table.

--After inserting the record, a record is inserted to CountryView using the insert trigger

EXEC [usp_CountryInsert]

      @ModifierSessionID,

      @ModifierID,

      @ModifieddateTime,

      @ModifierSessionID,

      @ModifierID,

      @ModifieddateTime,

      @CountryID OUT ,

      @ErrorLogID OUT

 

--There is no issue in executing the above stored procedure

 

 

--This will insert a record to the country details table.

--After inserting the record, a record is updated to CountryView using the update trigger

EXEC [usp_CountryDetailsInsert]

                        @CountryID,

                        @RegionID,

                        @CountryShortName,

                        @CountryName,

                        @Notes,

                        @ApprovalID,

                        @Active,

                        @CanOperate,

                        @ModifierSessionID,

                        @ModifierID,

                        @ModifieddateTime,

                        @MaxDateTime,

                        @IDCol OUT,

                        @ErrorLogID OUT

GO

 

-- Since here there is no record in Region table, there should be foreign key violation error.

/*----------------------------------------To Test the Foreign Key Violation End---------------------------------------------*/

After running the usp_CountryInsert and usp_CountryDetailsInsert procedure, in SQL query window, you will get the an error: The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_CountryView_RegionView". The conflict occurred in database "TEST", table "dbo.RegionView", column 'RegionID'.

But if you call the same procedures using Enterprise Library (both of them are called within the same transaction), you will get an error: "Un-committable Transaction..."

I hope this will give you a good clarity on the issue.

 

Thanks and Regards,

Peri

Sep 27, 2010 at 10:44 AM

I tried to repro this before and I got the foreign key violation error.  Could you post your DAAB codes?  And what version of Enterprise Library are you using? 

 

Sarah Urmeneta
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com

Sep 27, 2010 at 7:11 PM

Thanks for immediately responding and I appreciate really your help.

Error that I get when I run from VB.NET code:

"Uncommittable transaction is detected at the end of the batch. The transaction is rolled back. The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_CountryView_RegionView". The conflict occurred in database "TEST", table "dbo.RegionView", column 'RegionID'."

The complete code in a single class. You need to create a TESTConnectionString in machine.config file and call the procedure TransactionalInsert. You will be getting the above error.

Please ensure that you run the SQL scripts in the previous post and then run this function.

Please let me know how to avoid the error "Uncommittable transaction is detected at the end of the batch. The transaction is rolled back."?

Public Class FKeyTest

 

   Public Const m_Database As String = "TESTConnectionString"

 

   ''' <summary>

   ''' Inserts a transactional record into the Country parent table and [RelatedTables] child table

   ''' </summary>

   Public Shared Sub TransactionalInsert()

 

       ' Create database connection factory

       Dim db As Database = DatabaseFactory.CreateDatabase(m_Database)

       ' Create Connection

       ' Declare Return Param

 

       Dim countryID As Integer = 0

       Dim regionID As Integer = 222

       Dim countryShortName As String = "CONTESTTODELETE"

       Dim countryName As String = "CONTESTTODELETE"

       'Dim countryShortName As String = "IND"

       'Dim countryName As String = "INDIA"

       Dim notes As String = "REGTESTTODELETE"

       Dim approvalID As Integer = 1

       Dim active As Boolean = True

       Dim canOperate As Boolean = True

       Dim CreatorSessionID As Integer = 4083

       Dim CreatorID As Integer = 60

       Dim modifierSessionID As Integer = 4083

       Dim modifierID As Integer = 60

       Dim effectiveFromDate As DateTime = DateTime.Now

       Dim effectiveToDate As DateTime = DateTime.Now

 

       Using connection As DbConnection = db.CreateConnection()

           connection.Open()

           Dim transaction As DbTransaction = connection.BeginTransaction()

           Try

 

               Dim currentDateTime As DateTime = DateTime.Now

               Dim maximumDateTime As DateTime = DateTime.Now

 

               ' ChildTransactionInsert Method of Country

               countryID = InsertIntoCountry(db, connection, transaction, CreatorSessionID, CreatorID, currentDateTime, modifierSessionID, modifierID, currentDateTime)

 

               ' ChildTransactionInsert Method of CountryDetails

               InsertIntoCountryDetails(db, connection, transaction, countryID, regionID, countryShortName, countryName, notes, approvalID, active, canOperate, modifierSessionID, modifierID, currentDateTime, maximumDateTime)

 

               transaction.Commit()

 

           Catch sqlEx As SqlException

               transaction.Rollback()

               'Throw Util.Helper.ParseException(sqlEx)

           Catch appEx As ApplicationException

               transaction.Rollback()

               'Throw Util.Helper.ParseException(appEx)

           Catch ex As Exception

               transaction.Rollback()

               'Throw Util.Helper.ParseException(ex)

           End Try

       End Using

   End Sub

 

   ''' <summary>

   ''' Inserts a transactional record into the Country child table

   ''' </summary>

   Private Shared Function InsertIntoCountry(ByVal db As Database, ByVal connection As DbConnection, ByVal transaction As DbTransaction, ByVal creatorSessionID As Integer, ByVal creatorID As Integer, ByVal createdDateTime As DateTime, ByVal modifierSessionID As Integer, ByVal modifierID As Integer, ByVal modifiedDatetime As DateTime) As Integer

       Try

           ' Create Command

           Dim dbCommand As DbCommand = db.GetStoredProcCommand("usp_CountryInsert")

 

           ' Declare Return and Error Value Params

           Dim countryID As Integer

           Dim errorLogID As Integer

           ' Add Parameters

           db.AddInParameter(dbCommand, "CreatorSessionID", DbType.Int32, creatorSessionID)

           db.AddInParameter(dbCommand, "CreatorID", DbType.Int32, creatorID)

           db.AddInParameter(dbCommand, "CreatedDateTime", DbType.DateTime, createdDateTime)

           db.AddInParameter(dbCommand, "ModifierSessionID", DbType.Int32, modifierSessionID)

           db.AddInParameter(dbCommand, "ModifierID", DbType.Int32, modifierID)

           db.AddInParameter(dbCommand, "ModifiedDatetime", DbType.DateTime, modifiedDatetime)

           db.AddOutParameter(dbCommand, "CountryID", DbType.Int32, countryID)

           db.AddOutParameter(dbCommand, "ErrorLogID", DbType.Int32, errorLogID)

 

           ' Execute the query

           db.ExecuteNonQuery(dbCommand, transaction)

 

           ' Check for Error

           errorLogID = Convert.ToInt32(dbCommand.Parameters("@ErrorLogID").Value)

           If errorLogID <> 0 Then

               Throw New Exception(errorLogID)

           End If

           ' Return Parameter

           countryID = Convert.ToInt32(dbCommand.Parameters("@CountryID").Value)

           Return countryID

       Catch sqlEx As SqlException

           Throw sqlEx

       Catch appEx As ApplicationException

           Throw appEx

       Catch ex As Exception

           Throw ex

       End Try

   End Function

 

   ''' <summary>

   ''' Inserts a transactional record into the CountryDetails child table

   ''' </summary>

   Public Shared Function InsertIntoCountryDetails(ByVal db As Database, ByVal connection As DbConnection, ByVal transaction As DbTransaction, ByVal countryID As Integer, ByVal regionID As Integer, ByVal countryShortName As String, ByVal countryName As String, ByVal notes As String, ByVal approvalID As Integer, ByVal active As Boolean, ByVal canOperate As Boolean, ByVal modifierSessionID As Integer, ByVal modifierID As Integer, ByVal effectiveFromDate As DateTime, ByVal effectiveToDate As DateTime) As Integer

       Try

           ' Create Command

           Dim dbCommand As DBCommand = db.GetStoredProcCommand("usp_CountryDetailsInsert")

 

           ' Declare Return and Error Value Params

           Dim IDCol As Integer

           Dim errorLogID As Integer

           ' Add Parameters

           db.AddInParameter(dbCommand, "CountryID", DbType.Int32, countryID)

           db.AddInParameter(dbCommand, "RegionID", DbType.Int32, regionID)

           db.AddInParameter(dbCommand, "CountryShortName", DbType.String, countryShortName)

           db.AddInParameter(dbCommand, "CountryName", DbType.String, countryName)

           db.AddInParameter(dbCommand, "Notes", DbType.String, notes)

           db.AddInParameter(dbCommand, "ApprovalID", DbType.Int32, approvalID)

           db.AddInParameter(dbCommand, "Active", DbType.Boolean, active)

           db.AddInParameter(dbCommand, "CanOperate", DbType.Boolean, canOperate)

           db.AddInParameter(dbCommand, "ModifierSessionID", DbType.Int32, modifierSessionID)

           db.AddInParameter(dbCommand, "ModifierID", DbType.Int32, modifierID)

           db.AddInParameter(dbCommand, "EffectiveFromDate", DbType.DateTime, effectiveFromDate)

           db.AddInParameter(dbCommand, "EffectiveToDate", DbType.DateTime, effectiveToDate)

           db.AddOutParameter(dbCommand, "IDCol", DbType.Int32, IDCol)

           db.AddOutParameter(dbCommand, "ErrorLogID", DbType.Int32, errorLogID)

 

           ' Execute the query

           db.ExecuteNonQuery(dbCommand, transaction)

 

           ' Check for Error

           errorLogID = Convert.ToInt32(dbCommand.Parameters("@ErrorLogID").Value)

           If errorLogID <> 0 Then

               Throw New Exception(errorLogID)

           End If

           ' Return Parameter

           IDCol = Convert.ToInt32(dbCommand.Parameters("@IDCol").Value)

           Return IDCol

       Catch sqlEx As SqlException

           Throw sqlEx

       Catch appEx As ApplicationException

           Throw appEx

       Catch ex As Exception

           Throw ex

       End Try

   End Function

 

End Class

 

 

Sep 28, 2010 at 3:25 AM

I see.  There was the addition of that message because it is executed inside a transaction.  But the rest of the error message is still there.   I don't see how you're going to remove that message but if you only want to get the foreign key violation error message, you can retrieve it from the Errors collection property of the SqlException.

 

Sarah Urmeneta
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com

Sep 28, 2010 at 4:54 AM

Thanks for the response.

But can you tell me why this error is coming "Uncommittable transaction is detected at the end of the batch. The transaction is rolled back." ? What action should be taken to avoid this error?

 

Thanks and Regards,

 

Peri

Sep 28, 2010 at 5:28 AM

As you've said, the second stored procedure will produce an error because there's no record yet in RegionView table.  The next reason is because you're executing the stored procedure inside a transaction.  You don't get the same error when executing the stored procedures directly in sql server management studio because they are not inside a database transaction. 

The foreign key violation error message is still part of the error message.  What is it that you want to specifically achieve that you want to get rid of this error message?

 

Sarah Urmeneta
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com

Sep 28, 2010 at 6:35 AM

As you should have noticed in both of my stored procedure (usp_CountryInsert, usp_CountryDetailsInsert) , I have commented a line at the end to log the error into the Error Log table.

/*DECLARE @ErrorLogIDRecd INT;   EXECUTE [dbo].[usp_LogError]   @UserID = @ModifierID,   @ErrorLogID=@ErrorLogIDRecd  OUTPUT   SET @ErrorLogID = @ErrorLogIDRecd  */

In case I uncomment the above line, I am getting an error like this.

"Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.Cannot log error since the current transaction is in an uncommittable state. Rollback the transaction before executing uspLogError in order to successfully log error information."

Can you please help me out on why this error is coming - "Uncommittable transaction is detected at the end of the batch. The transaction is rolled back" ?

 

Thanks and Regards,

 

Peri

Sep 28, 2010 at 7:38 AM
Edited Sep 28, 2010 at 7:39 AM

I tried to uncomment that line but I didn't get an exception like you did.  In fact, I didn't get an exception at all after the call to ExecuteNonQuery and was able to retrieve the errorID.   Could you post your script for creating usp_LogError and other related database objects? I created my own usp_LogError which inserts to an Error table which I also created.  Maybe copying your exact stored procedure will help me repro the error

 

Sarah Urmeneta
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com

Sep 28, 2010 at 8:07 AM

As requested, Please see below 3 scripts. One for table creation and other 2 procedures for Log Error and Print Error.

 

CREATE TABLE [dbo].[ErrorLog](

      [ErrorLogID] [int] IDENTITY(1,1) NOT NULL,

      [ErrorTime] [datetime] NULL CONSTRAINT [DF_ErrorLog_ErrorTime] DEFAULT (getdate()),

      [UserName] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

      [ErrorNumber] [int] NULL,

      [ErrorSeverity] [int] NULL,

      [ErrorState] [int] NULL,

      [ErrorProcedure] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

      [ErrorLine] [int] NULL,

      [ErrorMessage] [varchar](7000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

CONSTRAINT [PK_ErrorLog_ErrorLogID] PRIMARY KEY CLUSTERED

(

      [ErrorLogID] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

 

GO

 

-- usp_PrintError prints error information about the error that caused  

-- execution to jump to the CATCH block of a TRY...CATCH construct.  

-- Should be executed from within the scope of a CATCH block otherwise  

-- it will return without printing any error information.

CREATE PROCEDURE [dbo].[usp_PrintError]  

AS

BEGIN

   SET NOCOUNT ON;

   -- Print error information.  

   PRINT 'Error ' + CONVERT(varchar(50), ERROR_NUMBER()) +

         ', Severity ' + CONVERT(varchar(5), ERROR_SEVERITY()) +

         ', State ' + CONVERT(varchar(5), ERROR_STATE()) +  

         ', Procedure ' + ISNULL(ERROR_PROCEDURE(), '-') +  

         ', Line ' + CONVERT(varchar(5), ERROR_LINE());

   PRINT ERROR_MESSAGE();

END;

 

GO

 

-- uspLogError logs error information in the ErrorLog table about the  

-- error that caused execution to jump to the CATCH block of a  

-- TRY...CATCH construct. This should be executed from within the scope  

-- of a CATCH block otherwise it will return without inserting error  

-- information.  

CREATE PROCEDURE [dbo].[usp_LogError] (

@UserID [int],

   @ErrorLogID [int] = 0 OUTPUT) -- contains the ErrorLogID of the row inserted

AS                               -- by uspLogError in the ErrorLog table

BEGIN

   SET NOCOUNT ON;

   -- Output parameter value of 0 indicates that error  

   -- information was not logged

   SET @ErrorLogID = 0;

   BEGIN TRY

       -- Return if there is no error information to log

       IF ERROR_NUMBER() IS NULL

           RETURN;

       -- Return if inside an uncommittable transaction.

       -- Data insertion/modification is not allowed when  

       -- a transaction is in an uncommittable state.

       IF XACT_STATE() = -1

       BEGIN

           PRINT 'Cannot log error since the current transaction is in an uncommittable state. '  

               + 'Rollback the transaction before executing uspLogError in order to successfully log error information.';

           RETURN;

       END

  

-- Find the operating username from user table to log error  

DECLARE @UserName nvarchar(128)  

  

SELECT @UserName = [UserShortName]

FROM [dbo].[UserView]

WHERE [UserID] = @UserID

IF @UserName IS NULL

   SET @UserName = 'Unauthorized User'

       INSERT [dbo].[ErrorLog]  

           (

           [UserName],  

           [ErrorNumber],  

           [ErrorSeverity],  

           [ErrorState],  

           [ErrorProcedure],  

           [ErrorLine],  

           [ErrorMessage]

           )  

       VALUES  

           (

           @UserName,  

           ERROR_NUMBER(),

           ERROR_SEVERITY(),

           ERROR_STATE(),

           ERROR_PROCEDURE(),

           ERROR_LINE(),

           ERROR_MESSAGE()

           );

       -- Pass back the ErrorLogID of the row inserted

       SET @ErrorLogID = @@IDENTITY;

   END TRY

   BEGIN CATCH

       PRINT 'An error occurred in stored procedure uspLogError: ';

       EXECUTE [dbo].[usp_PrintError];

       RETURN -1;

   END CATCH

END;

GO

 

Sep 28, 2010 at 8:26 AM

I already tried this in 4.1 and 5.0 version but still unable to repro it.  I didn't encounter any exception after the call to ExecuteNonQuery and was able to retrieve the error id.  What version of EntLib are you using?

Sep 28, 2010 at 9:54 AM

I was first using version 2.0 with .NET framework 2.0. I then tried with version 4.1 with .NET framework 3.5. Still I am getting the same error.

Uncommittable transaction is detected at the end of the batch. The transaction is rolled back. Cannot log error since the current transaction is in an uncommittable state. Rollback the transaction before executing uspLogError in order to successfully log error information.

I am using the same code that was sent to you.

Note: The first ExecuteNonQuery will not give you an error. The second ExecuteNonQuery will give you the error.

Please let me know if you are able to reproduce this issue.

Thanks and Regards,

Peri

Sep 28, 2010 at 9:59 AM

I'm wondering if this is due to difference of sql server.  I'm using SQL Server 2008.  If you have the 2008 version, could you try it?  'Coz I don't have the 2005 version.  If you don't have it, I suggest trying to look up if this is how it's done in SQL Server 2005. 

 

Sarah Urmeneta
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com

Sep 28, 2010 at 11:07 AM

Ok. Will try the same in SQL Server 2008 Express Edition and get back to you.

Note: Have you uncommented the below line in both the stored procedures - usp_CountryInsert, usp_CountryDetailsInsert

DECLARE @ErrorLogIDRecd INT;   EXECUTE [dbo].[usp_LogError]   @UserID = @ModifierID,   @ErrorLogID=@ErrorLogIDRecd  OUTPUT   SET @ErrorLogID = @ErrorLogIDRecd  

 

Thanks and Regards,

Peri

 

Sep 28, 2010 at 2:52 PM

Thanks for all the help. It works fine in SQL Server 2005 SP2 and higher.

This issue occurs only in SQL Server 2005 SP1.

 

Regards,

 

Peri

Jan 10, 2011 at 6:00 PM

I am facing the same issue with SQL Server SP 2 and SP 3 also. Can you suggest a way out to resolve the issue?

 

Regards,

Peri

Jan 11, 2011 at 1:09 AM

I thought it doesn't occur in SP2?  Is it still SQL Server 2005 or 2008?  I suggest trying your code out using the regular ADO.NET and see if you still encounter the error.  If the issue still persists, then I suggest you post this in other forums as it isn't an EntLib concern and probably more of a sql server matter.

 

Sarah Urmeneta
Global Technologies and Solutions
Avanade, Inc.
entlib.support@avanade.com