Fehlerroutine funktioniert nicht bei allen Fehlern

[Peter Schoch]

Hallo Zusammen,
<p>ich habe&nbsp; folgendes Transactionsstatement für meinem SQL-Server 2000
geschrieben:</p>
<p>begin transaction<br>
deleteT_.........<br>
&nbsp;&nbsp;&nbsp; if @@error <> 0&nbsp;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; begin&nbsp;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n bsp;&nbsp;&nbsp; rollback transaction<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; end<br>
&nbsp;&nbsp;&nbsp; else<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; begin<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n bsp;&nbsp;&nbsp; commit transaction<br>
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp; end</p>
<p><br>
Wenn ich nun den Quelltext im Query Analyzer testen will habe ich folgende
Problem festgestellt. Wenn ich absichtlich einen Fehler in der Deleteanweisung
produziere dann bricht mir der Query Analyzer mit einer Fehlermeldung ab. Ich
will aber gerade Testen ob mein Quelltext richtig auf einen Fehler reagiert.
Kann mir jemand sagen wie ich dies testen kann.&nbsp;</p>
<p>MfG<br>
Peter Schoch<p>P.S Habe rausgefunden beim Fehler: Objekt nicht vorhanden &quot;Server: Nachr.-Nr. 208, Schweregrad 16, Status 1, Zeile 1&quot;
wird abgebrochen, aber bei dem Fehler Schlüsselverletzung &quot;Server: Nachr.-Nr. 229, Schweregrad 14, Status 5, Zeile 1&quot;
nicht. Ich will das aber alle Fehler abgefangen werden. Kann mir jemand helfen?

[Andreas Kosch]

Hallo,

man könnte eine Stored Procedure schreiben und aufrufen, die intern die verschiedenen Fälle prüft und als eigenen Rückgabewert zurückliefert. Falls das zu "ungenau" ist, kann man auch einen OUTPUT-Parameter nutzen. Das könnte dann so aussehen:

iError-Wert = 0 und iStatus-Wert = 0: Die Umbuchung war erfolgreich, die SP hat intern die Transaktion über COMMIT bestätigt.

iError-Wert = 0 und iStatus-Wert = 1: Das Konto des Absenders wurde nicht gefunden, daher wurde auch die Gutschrift für den Empfänger nicht ausgelöst sowie die Transaktion über ROLLBACK zurückgenommen.

iError-Wert = 0 und iStatus-Wert = 2: Das Konto des Empfängers wurde nicht gefunden. Da bereits vorher die Belastung des Absender-Kontos vorgenommen wurde, hat die SP ein ROLLBACK ausgelöst.

iError-Wert > 0 und iStatus-Wert = beliebig: Es ist ein Datenbankfehler aufgetreten, alle Aktionen werden in der SP über den ROLLBACK-Aufruf widerrufen.

<pre>
CREATE PROCEDURE TransferMoney
@iFromKontoNr INT, @iToKontoNr INT, @mValue MONEY, @iStatus INT OUTPUT
AS
DECLARE @iError INT
DECLARE @iRowCount INT
SET @iStatus = 0
-- Schritt 1: Transaktion starten
BEGIN TRANSACTION
-- Schritt 2: Konto des Absenders belasten
UPDATE Konto
SET Betrag = Betrag - @mValue
WHERE KontoNr = @iFromKontoNr
SELECT @iError = @@ERROR, @iRowCount = @@ROWCOUNT
-- Schritt 3: Prüfen, ob Schritt 2 erfolgreich war
IF @iRowCount = 0
-- Status 1 = Konto des Absenders wurde nicht aktualisiert
SELECT @iStatus = 1
-- Zweiten Teilschritt nur im Erfolgsfall starten
IF (@iERROR = 0) AND (@iRowCount = 1)
BEGIN
-- Schritt 4: Konto des Empfängers gutschreiben
UPDATE Konto
SET Betrag = Betrag + @mValue
WHERE KontoNr = @iToKontoNr
SELECT @iError = @@ERROR, @iRowCount = @@ROWCOUNT
IF @iRowCount = 0
-- Status 2 = Konto des Empfängers wurde nicht aktualisiert
SELECT @iStatus = 2
END
-- Schritt 5: Transaktion im Fehlerfall widerrufen
IF (@iError = 0) AND (@iStatus = 0)
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION
-- Fehlernummer zurückliefern
RETURN(@iError)
</pre>
Diese SP kann nun im Query Analyzer "am Stück" getestet werden, wobei man auf diesem Weg sowohl den Rückgabewert als auch den OUTPUT-Parameterwert abrufen kann:
<pre>
DECLARE @iResult INT
DECLARE @iStatus INT
EXEC @iResult = TransferMoney 1,22,12.50,@iStatus OUTPUT
SELECT @iResult,@iStatus
</pre>
Für das Schrittweise Debuggen kann man auf den Stored Procedure-Debugger des Query Analyzers zurückgreifen

[Peter Schoch]

Hallo Herr Kosch,&nbsp;</p>
<p> vielen Dank für das Beispiel. Das Problem liegt aber in der Abarbeitung der Exception begründet.
Stellt der SQL Server eine Verletzung der Objektreferenz fest (z. B<br>
falscher Tabellenname) resultiert eine Exception, die dazu führt, das das Script sofort unterbrochen wird.&nbsp;<br>
Es wird also weder ein Rollback noch ein Commit ausgeführt. Das Ergebnis ist eine begonnene Transaction die "verwaist".
Ich haben aber noch keine Möglichkeit<br>
gefunden, die "Objektreferenz-Prüfung" abzuschalten, oder den Fehlerschweregrad zu ändern um dieses Verhalten in den Griff zu<br>
bekommen.</p>
<p>Peter Schoc

[Andreas Kosch]

Hallo,

es ist Sache des Clients, auf eine Exception zu reagieren und mit einem Rollback zu antworten. Das gleiche Problem hat man bei einem LOCK_TIMEOUT-Fehler, auch hier macht der Server kein automatisches Rollback