0

I'm doing Left join to get not existing records on a table which has 50k records and the right hand side table has 200k records.

removed .Dispose() calling from fianlly block

This is my case:

public int InsertRecord(DynamicParameters parameters, string sql, int? commandTimeout = null)
    {
        using (var transactionScope = new TransactionScope())
        {
            _db.Open();
            using (var transaction = _db.BeginTransaction())
            {
                try
                {
                    var Item = _db.Execute(sql, parameters, commandType: CommandType.StoredProcedure, commandTimeout: commandTimeout);
                    transaction.Commit();
                    transactionScope.Complete();
                    return Item;
                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    throw ex;
                }
                finally
                {
                    _db.Dispose();
                }
            }
        }
        return 0;
    }


  var parameters = new DynamicParameters();
            parameters.Add("ImportInventoryID", inventoryMovementID);
            parameters.Add("StoreID", storeID);
            parameters.Add("Mode", 4);
            DataMapper dataMapper = new DataMapper();
            int result = dataMapper.ExecuteRecords(parameters, "dbo.ExecuteInventoryItemMaster", 500);
            return true;



SELECT DISTINCT LTRIM(RTRIM(I.ItemNum)), LTRIM(RTRIM(I.AltSKU)), LTRIM(RTRIM(I.ItemName)), NumPerVenCase, I.Vendor_Number, I.DateCreated
    FROM [dbo].Inventory I (NOLOCK) 
    LEFT JOIN [dbo].StoreItemMaster IIM (NOLOCK) ON LTRIM(RTRIM(I.ItemNum)) = IIM.StoreItemNumber
        AND LTRIM(RTRIM(I.AltSKU)) = IIM.AltSKU
        AND LTRIM(RTRIM(I.Vendor_Number)) = IIM.VendorNumber
        AND IIM.StoreID = @StoreID
    WHERE IIM.StoreItemNumber IS NULL
        AND IIM.AltSKU IS NULL
        AND I.ImportInventoryID = @ImportInventoryID
        AND ISNULL(I.ItemNum, '') <> ''
HAREESH PONNAM
  • 21
  • 7
  • 1
    Hi, which dbms you are using? – Zeki Gumus Dec 20 '18 at 13:10
  • Try [this](https://stackoverflow.com/questions/6358806/this-sqltransaction-has-completed-it-is-no-longer-usable-configuration-er). – CodingYoshi Dec 20 '18 at 13:11
  • 3
    This is a *.NET* error. Where is the .NET code? How are you calling this query? Somehow, somewhere you *are* using a SqlTransaction. – Panagiotis Kanavos Dec 20 '18 at 13:17
  • @ZekiGumus I'm using SQL 2014 – HAREESH PONNAM Dec 20 '18 at 13:23
  • are you using `TransactionScope`? Are you invoking this code through some ORM/etc layer that might be adding transactions? – Marc Gravell Dec 20 '18 at 13:23
  • @PanagiotisKanavos I'm hitting db using dapper – HAREESH PONNAM Dec 20 '18 at 13:24
  • 1
    You will have to show your .NET code. – Lasse V. Karlsen Dec 20 '18 at 13:24
  • @HAREESHPONNAM post your code. You *are* using a transaction in some way. It's either in your code, or the method that calls your code. If you have an ASP.NET MVC application someone may have thought it's nice to use a transaction per request (it's not). – Panagiotis Kanavos Dec 20 '18 at 13:26
  • 2
    @HAREESHPONNAM btw all those `NOLOCK` hints suggest you have serious problems already. `NOLOCK` doesn't mean go fast. It means `read dirty data while taking excessive locks`. Those TRIM calls mean you can't use any indexes to improve performance. The same with that final `ISNULL()`. – Panagiotis Kanavos Dec 20 '18 at 13:28
  • @HAREESHPONNAM what is DataMapper? Googling for it returns an ORM for *Ruby*. Is it your own class? A third part library? You can't expect to get any help if you don't post any relevant information. If you have trouble with your code *post the code* – Panagiotis Kanavos Dec 20 '18 at 13:31
  • @PanagiotisKanavos Ok, I will try by removing Trims and IsNull. Shared mvc code in question – HAREESH PONNAM Dec 20 '18 at 13:32
  • We also need to see the entire stored procedure definition. – Crowcoder Dec 20 '18 at 13:41
  • @PanagiotisKanavos I'm attaching Data mapper method which is used to hit the db – HAREESH PONNAM Dec 20 '18 at 13:52
  • 2
    @HAREESHPONNAM and there are *two* transactions involved in that code. The `TransactionScope` and the explicit `BeginTransaction()`. Neither is needed though because individual SELECT queries are atomic – Panagiotis Kanavos Dec 20 '18 at 13:55
  • 2
    @HAREESHPONNAM even worse, even though both TransactionScope and SqlTransaction are declareed in `using` blocks, there's a `finally` block that calls `.Dispose()`. That guarantees an exception. `using` blocks are used so people *don't* have to call `Dispose()` explicitly. Even `transaction.Rollback()` isn't needed - disposing a transaction object will cause a rollback unless `Commit()` was called – Panagiotis Kanavos Dec 20 '18 at 13:57
  • @PanagiotisKanavos I have removed `.Dispose()` calling but still getting same exception. – HAREESH PONNAM Dec 21 '18 at 11:52
  • System.InvalidOperationException: This SqlTransaction has completed; it is no longer usable. at System.Data.SqlClient.SqlTransaction.ZombieCheck() at System.Data.SqlClient.SqlTransaction.Rollback() at DataAccess.Repository.InsertRecord(DynamicParameters parameters, String sql, Nullable`1 commandTimeout) at DataAccess.DataMapper.ExecuteRecords(DynamicParameters parameters, String strSPName, Nullable`1 commandTimeout) at Import.Program.UpdateSalesSummaryDailyTable(String storeID, String storeName) – HAREESH PONNAM Dec 21 '18 at 11:53
  • I believe something actually went wrong on the call to transaction.Commit() or transactionScope.Complete(), was thrown and caught, then transaction.Rollback() was called, then that threw. You are only looking at the exception from transaction.Rollback() call and not the exception that led to the rollback call in the first place. – Trioj Dec 21 '18 at 20:35

0 Answers0