CODEWITHSUNDEEP
asp.net-web-api2stored-functionsin-memory-tables
Askarc Ali
Askarc Ali

Reputation: 348

Using Web API TransactionScope and SQL Server in-memory table Issue

Whenever I am accessing a stored procedure which has an in-memory table through the Web API TransactionScope I am getting this error:

The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction. The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction. The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction. Uncommittable transaction is detected at the end of the batch. The transaction is rolled back

I have tried to execute this same stored procedure directly in SQL Server Management Studio, and it is working fine.

Also, when I remove the TransactionScope from Web API, it is also working fine. But I want to use TransactionScope inside Web API

Inside the stored procedure, I have the following things:

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
BEGIN TRY
 SELECT ITEMID FROM SAMPLE_IN_MEMORY_TABLE
           ----
END TRY
BEGIN CATCH
--
END CATCH

Inside Web API following way I am consuming the stored procedure

QueriesTableAdapter qa = new QueriesTableAdapter();

using (TransactionScope scope = new TransactionScope())
{
      qa.SpSampleInMemoeryAccess(g_OutParameter64);

      if (g_OutParameter64 > 0)
      {
          scope.Complete();
          Status = true;             
      }
      else
      {
          Status = false;
      }
   }
}

Upvotes: 5

Views: 396

Answers (1)

Santiago Burbano
Santiago Burbano

Reputation: 11

You are changing isolation level in the middle of a transaction.

Default Isolation Level of TransactionScope is SERIALIZABLE, but inside your stored procedure you are changing it to READ UNCOMMITTED (!!! hope you know what you are doing).

Either:

  1. Do not set Isolation Level inside your stored procedure
  2. Specify Isolation Level READ UNCOMMITTED in the TransactionScope constructor.

Upvotes: 0

Related Questions