SQL Server : Cannot roll back ...No transaction or savepoint of that name was found
<p>We have Perl script that bcp's data for various entities to stage tables , and then invokes a stored procedure via DBI(Autocommit=0) to move data from Stage-Core tables for these entities.</p>

<p>The stored procedure pseudo code is as below</p>

<pre><code>   -- GET DETAILS from stage to temproaray tables
  SELECT * FROM &lt;MAIN_STG_TBL&gt; INTO  #MAIN_TBL
  SELECT * FROM &lt;SUPP_STG_1&gt; INTO  #supp_1
  SELECT * FROM &lt;SUPP_STG_2&gt; INTO  #supp_2
  .....
  SELECT * FROM &lt;SUPP_STG_10&gt; INTO  #supp_10


  RAISERROR (&lt;INFO_MSG&gt;, 10, 1) WITH NOWAIT

  -- for each entity in #MAIN_TBL
  -- move datae from stage to core
  WHILE_LOOP ( EACH &lt;ENTRY&gt; in #MAIN_TBL )

    BEGIN TRY

     BEGIN TRAN STG_CORE

        INSERT INTO &lt;MAIN_CORE&gt; SELECT * FROM #MAIN_TBL WHERE ENTITY=&lt;ENTRY&gt;
        INSERT INTO &lt;SUPP_CORE_1&gt; SELECT * FROM #supp_1 WHERE ENTITY=&lt;ENTRY&gt;
        INSERT INTO &lt;SUPP_CORE_1&gt; SELECT * FROM #supp_2 WHERE ENTITY=&lt;ENTRY&gt;
           .....
        INSERT INTO &lt;SUPP_CORE_10&gt; SELECT * FROM #supp_10 WHERE ENTITY=&lt;ENTRY&gt;

     COMMIT TRAN STG_CORE;
    END TRY

    BEGIN CATCH
      ROLLBACK TRAN STG_CORE;
    END CATCH

    RAISERROR (&lt;INFO_MSG&gt;, 10, 1) WITH NOWAIT

  END LOOP

RAISERROR (&lt;INFO_MSG&gt;, 10, 1) WITH NOWAIT
</code></pre>

<p>The stored procedure itself is invoked in a <code>AutoCommit=0</code> connection , and is immediately followed with <code>DBI-&gt;commit</code></p>

<p><strong>ISSUE</strong></p>

<p>For all failed entities 200/1000 entities mostly due to <code>Primary Key Violation in some of the SUPP Tables</code> seeing the message <code>STAGE_TO_CORE_SP text=Cannot roll back STG_CORE. No transaction or savepoint of that name was found</code></p>