SQL 2005, rollback bij fout in cursor lukt niet

[Martin?81]

Zie hieronder een procedure in SQL (2005). Het is een transaction. De rollback werkt, tenzij deze er iets mis gaat in de cursor, bijvoorbeeld wanneer ik een verkeerde tabel selecteer.

Bij de cursor gebruikte ik tabel #WorkTableContactmomenten in plaats van #WorkTable. Dan kloppen de velden uiteraard niet. Echter als de procedure dan draai krijg ik de volgende foutmelding (behalve natuurlijk de foutmeldingen over de velden):
Msg 266, Level 16, State 2, Procedure SP_IMPORT, Line 463
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.

Waarom wordt de rollback niet uitgevoerd? Kennelijk is de waarde van @@trancount niet juist. Hoe kan ik dit wel opvangen? Ook als er bij het declaren van de cursor of in de cursor while loop iets mis gaat moet er ook een rollback worden uitgevoerd en een insert in de log-tabel.

Alvast bedankt.

SQL: SP_IMPORT

use DATABASE
set ansi_nulls on
go
set quoted_identifier on
go
alter procedure [dbo].[SP_IMPORT] 
as
begin
  set nocount on;

  begin transaction IMPORT;
    begin try
      if object_id('tempdb..#WorkTable') is not null
        drop table #WorkTable;

      select 0 as MailKeyStatus, 0 as PostaddressKeyStatus, 0 as PostaddressStatus, 0 as DuplicateMKStatus, 0 as DuplicatePKStatus, null as Priority, *
      into #WorkTable from WORK
      where isNull(FINISHED,'')='';

      if object_id('tempdb..#WorkTableContactmomenten') is not null
        drop table #WorkTableContactmomenten;

      create table #WorkTableContactmomenten (
        ParentID int, IMPORT_BRON nvarchar(200)
      );

  --Begin c1 cursor
      declare @ParentID int, @IMPORT_BRON nvarchar(200);

      declare c1 cursor for
      select ID, IMPORT_BRON
      from #WorkTable
      order by ID desc;

      open c1;

      fetch next from c1
      into @ParentID, @IMPORT_BRON;

      while @@fetch_status=0
        begin
          if @OPDEHOOGTE=1
            begin
              insert into #WorkTableContactmomenten (ParentID, IMPORT_BRON)
              values (@ParentID, @IMPORT_BRON+'|A1');

              insert into #WorkTableContactmomenten (ParentID, IMPORT_BRON)
              values (@ParentID, @IMPORT_BRON+'|A2');
            end;
          if @OPDEHOOGTE=2
            begin
              insert into #WorkTableContactmomenten (ParentID, IMPORT_BRON)
              values (@ParentID, @IMPORT_BRON+'|B');
            end;
    
          fetch next from c1
          into @ParentID, @IMPORT_BRON;
        end;

      close c1;
      deallocate c1;
  --End c1 cursor

    end try
    begin catch
      insert into LOG (ERROR_DT, ERROR_NUMBER, ERROR_SEVERITY, ERROR_STATE, ERROR_PROCEDURE, ERROR_LINE, ERROR_MESSAGE)
      select getdate(), error_number(), error_severity(), error_state(), error_procedure(), error_line(), error_message();

      if @@trancount>0
        begin
          rollback transaction

          return 1
        end;
    end catch;

  if @@trancount>0
    begin
      commit transaction

      return 0
    end;
end

[RobIII]

Heb je al eens gewoon gegoogled op je foutmelding Wat heb je zelf al geprobeerd/gezocht/gevonden etc.? (Zie onze Quickstart). Vooral dit (welke weer linkt naar dit) lijkt me wel relevant voor je (en anders wel om dat soort info te vermelden in de TS )

[ Voor 29% gewijzigd door RobIII op 31-03-2011 12:40 ]

[P_de_B]

Waarom gebruik je trouwens een cursor hiervoor? Zoals ik het nu kan zien kan dit eenvoudig ook zonder cursor gedaan worden, icm een CASE WHEN voor de @OpdeHoogte variabele.

[The Eagle]

Ik weet niet hoe SQL server met zijn DDL transacties omgaat. maar bij Oracle betekent een drop een impliciete commit. Wellicht gaat ie daar mis

[sopsop]

Mocht je het toch middels een temptable doen, kun je ook overwegen om een tablevar te gebruiken. Die zijn over het algemeen sneller en je hoeft ze niet te droppen.

[Martin?81]

Om te beginnen heb ik @@trancount toegevoegd, maar dit bleek het niet op te lossen.

Een begin try om de declare cursor zetten en over de gehele cursor, maar dit bleek het ook niet te zijn. Deze oplossingen had ik via Google gevonden.

Het is een cursor omdat hij sequentieel #WorkTable van boven naar onder moet doorlopen en afhankelijk van bepaalde voorwaarden verschillende inserts moet uitvoeren in #WorkTableContactmomenten. Het orginele script bevat meer voorwaarden.

Zoals ik het nu bekijk zou het ook met een while-loop kunnen en omzeil je het probleem met de cursor, wat wellicht efficienter is dan een cursor.

Echter zou je een fout bij de cursor toch ook moeten kunnen opvangen met een try en catch?

Een begin transaction hoogt je @@trancount op met 1. Een rollback zet deze naar 0 en een commit verlaagd je @@trancount met 1. Dus voor je commit of rollback dient @@trancount>0 te zijn. Echter lijkt er bij een fout in de cursor iets mis te zijn met de waar de van @@trancount. Of deze is er simpelweg niet? Daar ergens moet ik de oplossing zien te vinden heb ik het vermoeden.

Dus ondanks een while loop met case when of een if een oplossing kan zijn, zou ik toch ook graag willen weten hoe het zou kunnen werken met een cursor.

Buiten dit had ik ook nog de volgend URL gevonden: MSDN: ROLLBACK TRANSACTION (Transact-SQL)

Dit zegt ook iets over een cursor bij rollback, echter kom ik hier niet uit. Misschien dat iemand dit begrijpt.

[ Voor 9% gewijzigd door Martin?81 op 31-03-2011 13:15 ]

[Verwijderd]

The Eagle schreef op donderdag 31 maart 2011 @ 12:42:
Ik weet niet hoe SQL server met zijn DDL transacties omgaat. maar bij Oracle betekent een drop een impliciete commit. Wellicht gaat ie daar mis

SQL Server werkt anders dan Oracle omdat deze standaard niet impliciet een transactie start bij het uitvoeren van DDL of DML statements.
Dit is aan te passen met behulp van de implicit transactions connection setting, zie ook MSDN: Implicit Transactions