Seltsames Verhalten von ADO bei SET NOCOUNT ON

[McLane]

Moin,

ich habe eine StoredProcedure auf MSSQL 2000 Server, welche im Schnitt eine Laufzeit von ca. 40 bis 50 Sekunden hat. Angestoßen wird das ganze von einem Delphiprogramm per ADO-Connection. Bisher gab es keine Probleme, ausser dass ich Fehlermeldungen beim Starten per ADO nicht "hochgereicht" bekam, also keine Exception ausgelöst wurde. Ich habe dann erfahren, ein simples "SET NOCOUNT ON" veranlasst den SQL Server, die "x Rows affected" Meldungen zu unterlassen und der ADO-Treiber dann auf Fehlermeldungen auch regiert. Allerdings hatte ich daraufhin den Effekt, dass ich in ein "Timeout expired" gerannt bin. Der QueryAnalyzer macht da allerdings keine Probleme.
Da "SET NOCOUNT ON" die einzige Änderung war, habe ich den Befehl wieder entfernt und siehe da, die SP lief wieder unbedarft durch und hat auch brav getan, was sie sollte.

Meine Vermutung ist nun, dass durch das "SET NOCOUNT ON" ja keine Meldungen mehr seitens des SQL Servers abgeschickt werden und der Server bis zum Ablauf der 40 bis 50 Sekunden still schweigt und ADO daraufhin von einem Verbindungsabbruch ausgeht.
Ich habe ein schon in Google nach dem Thema gesucht, aber das Problem wurde nirgends erwähnt.

Kennt jemand dieses Verhalten und hat evtl. einen Workaroung, ohne das Timeout der Verbindung zu erhöhen? (Wenn wirklich mal Verbindungsabbruch nach 30 Sekunden ist, würde ich das auch gerne in den 30 Sekunden Standardtimeout erfahren).

Besten Dank

MfG McLane

[jensw_2000]

Hi,

du kannst Exceptions aus dem SQL-Code mit RAISEERROR auslösen. Diese kommen bei ADO auch mit SET ROWCOUNT ON an...
Voraussetzung dafür ist, das du die richtige Komponente nimmst.
Wenn du eine TAdoStoredProc anstatt der TAdoConnection zum Ausführen der SP verwendest, dann bekommt diese das Beenden der SP mit.
Zum einen liest die TAdoStoredProc den ReturnCode der SP aus (Parameters[0]), und zum Anderen würde MeineAdoStoredProc.Execute einen EDatabaseError auslösen, falls du in der SP eine Exception mit RAISEERROR erzeugst.

Da du die TAdoStoredProc ohnehin individuell mit dem ProcedureName und ggf. ein paar Parametern füttern musst, kannst du auch den Timeout-Wert zum Ausführen dieser "Riesen-SP" individuell hochsetzen.



Ich bin ja auch ein wenig neugierig ...
Was machst deine SP 40-50 Sekunden lang mit dem armen SQL-Server ... ?

Arbeitest du in der SP einen Cursor ab, der Datensätze verändert, hinter denen dann noch Trigger hängen?

Falls es dich nicht stört, dann zeige uns mal etwas SQL-Code ...
Eventuell können wir das noch ein wenig optimieren ...



Schöne Grüße,
Jens

[McLane]

Ich danke dir vielmals für deine Antwort, SQL Code darf ich leider nicht zeigen, ist Firmeneigentum. Tut mir schrecklich leid (bin selber OS-Verfechter, aber man muss ja von irgendwas leben).
Optimiert ist sie schon und sie läuft über ein eigenes StoredProc Objekt, in dem ich den Timeout höher setzen könnte. Das Problem ist, dies ist nur die Spitze des Eisberges, die längste Variante lief mal 45 Minuten (!!!). Ist ein Haufen Verarbeitung, was da dran hängt.
Momentan teste ich das weiterreichen der Exceptions, was mich auf dieses Verhalten von ADO stießen lies. Der Aufbau ist so gestaltet, dass sich die SP an die Situation zur Laufzeit anpasst, was zum Teil Cursor unausweichlich macht und je nach Situation halt zu extrem langen Laufzeiten führt. Daher ist es auch schwer, gezielt eine Exception auszulösen, wie in deiner Antwort beschrieben. Selbst wenn ich das tun würde, ich habe inzwischen folgendes Verhalten "rausfinden" (u. a. googel suche) können:

ADO "speichert" die Rückmeldungen bis zum Ende der Ausführung. Ist die erste eintreffende Meldung keine Fehlermeldung, hat man, wenn man Fehler abfangen will schon verloren, da ADO nicht mehr abbricht, bis der Ablauf beendet ist. Man hat nun nur noch "Zugriff" auf die erste eingetroffene Meldung. Deshalb musste auch das NOCOUNT rein und es darf keine PRINT Anweisung enthalten sein. Das erste PRINT erzeugt eine Ausgabe, welche im Erfolgsfall "verschluckt" wird bzw. im Fehlerfall wird die erste PRINT Meldung ausgegeben, aber nicht die Fehlermeldung.

Aufgrund der Komplexität und evtl. Laufzeit der SP ist wohl bereits erkennbar, dass das Script entsprechend groß ist. Daher bin ich auf aufschlussreiche Meldungen im Fehlerfall angewiesen.

Eine Erhöhung des Timeout ist leider nicht möglich, ich müsste es wohl bei ca. 60 Minuten ansetzen um die evtl. größte Laufzeit mit abzudecken. Und dann würde ich im "echten" timeout-Fall 60 Minuten warten müssen, bis ADO mir das meldet.

MfG McLane

[jensw_2000]

Eventuell ist dieser MSDN Artikel was für dich....


Man kann zusätzlich mit GOTO zwischen "Abbruch-Kreterien" und "verschmerzbare Fehler" differenzieren.
So kannst du schon im SQL-Code entscheiden, ob die Verarbeitung der SP abgebrochen oder vortgesetzt wird.
Deine Applikation bekommt auf diesem Wege das SP-Ende und ggf. die Exceptions mit ...

Hier mal ein ungetestetes Beispiel ...

SQL-Code:

BEGIN TRAN

INSERT INTO Adressen 
    ( Vorname
     ,Nachname
     ,Strasse
     ,PLZ
     ,Ort
    )
VALUES 
    ( 'Paul'
     ,'Panther'
     ,'Katzenberg 12'
     ,'12345'
     ,'Testdorf'
    )
SELECT @soft_error = @@ERROR 
-- weicher Fehler - Kein Abbruch !

INSERT INTO Adressen 
    ( Vorname
     ,Nachname
     ,Strasse
     ,PLZ
     ,Ort
    )
VALUES 
    ( 'Fred'
     ,'Feuerstein'
     ,'Höhle 12'
     ,'12345'
     ,'Testdorf'
    )
SELECT @soft_error = @@ERROR + @soft_error
-- weicher Fehler - Kein Abbruch + Commit!

INSERT INTO Adressen 
    ( Vorname
     ,Nachname
     ,Strasse
     ,PLZ
     ,Ort
    )
VALUES 
    ( 'Barney'
     ,'Geröllheimer'
     ,'Höhle 13'
     ,'12345'
     ,'Testdorf'
    )
IF @@ERROR <> 0 GOTO ROLLBACK_AND_ABORT
-- Abbruch Kreterium - im Fehlerfall Ausführung unterbrechen und Rollback!

INSERT INTO Adressen 
    ( Vorname
     ,Nachname
     ,Strasse
     ,PLZ
     ,Ort
    )
VALUES 
    ( 'Bux'
     ,'Bunny'
     ,'Irgendwo 22'
     ,'12345'
     ,'Testdorf'
    )
IF @@ERROR <> 0 GOTO ROLLBACK_AND_ABORT
-- Abbruch Kreterium - im Fehlerfall Ausführung unterbrechen und Rollback!


-- ...
-- viele weitere Befehle 
-- ...

COMMIT TRAN
IF @soft_error > 0 THEN
BEGIN
  -- Applikation über "weiche Fehler" informieren 
  -- Eventuell wäre anstatt der Exception auch ein Ausgabeparameter sinnvoll.
  RAISERROR(CAST(@soft_error AS VARCHAR(10))+' unwichtige Datensätze wurden nicht eingefügt',16,1,'Dein Prozedurname')
END

-- die weichen Fehler wurden ignoriert und die Transaktion Committed ...
-- jetzt springen wir an das Ende der SP
GOTO PROCEDURE_ENDE


ROLLBACK_AND_ABORT:

   -- Mindestens eine Abbruchbedingung ist erfüllt. 
   -- Transaktion rückgängig machen und die weitere Verarbeitung abbrechen
   ROLLBACK TRAN
   RAISERROR('Die Transaktion wurde verworfen, weil ein harter Fehler aufgetreten ist',16,1,'Dein Prozedurname')


PROCEDURE_ENDE:

[alzaimar]

ADO kann mit mehreren Resultsets arbeiten. Jedes 'X rows affected' erzeugt ein eigenes Resultset. Du kannst mit 'NextResultset' auf ADO-Seite durch alle Resultsets durchiterieren. Das Errors-Objekt der Connection korrespondiert mit den einzelnen Resultsets.

Bei den RAISERROR-Exceptions gibt es einen riesen Bug, der einfach nicht behoben wird:
Wenn in einer Transaktion NACH der ersten Änderung (INSERT, UPDATE, DELETE) eine Exception per RAISEERROR ausgelöst wird, bekommt die ADO-Komponente das nicht mit (es wird keine Exception ausgelöst, die mit Try..Except abzufangen wäre). Da führt kein Weg dran vorbei! Eigentlich peinlich, eine solche existentielle Geschichte nicht mal zu reparieren, aber MS hat wohl kein Bock.

[jensw_2000]

Zitat von alzaimar:

Bei den RAISERROR-Exceptions gibt es einen riesen Bug, der einfach nicht behoben wird:
Wenn in einer Transaktion NACH der ersten Änderung (INSERT, UPDATE, DELETE) eine Exception per RAISEERROR ausgelöst wird, bekommt die ADO-Komponente das nicht mit (es wird keine Exception ausgelöst, die mit Try..Except abzufangen wäre). Da führt kein Weg dran vorbei! Eigentlich peinlich, eine solche existentielle Geschichte nicht mal zu reparieren, aber MS hat wohl kein Bock.

Aber der Returncode der SP enthält im Fall eines SQL-Fehlers einen Wert <> 0 oder ?
(Der Parameter[0] = @RetCode entspricht imho dem Errorcode in @Error)

Falls ich mich jetzt wirklich komplett täusche, kann man die Fehlerinformationen immernoch per Ausgabeparameter an die Applikation übermitteln.

[alzaimar]

Davon weiss ich nichts, denn ich verwende den Returncode für eigene Zwecke.

[McLane]

danke erstmal für die vielen Antworten und Vorschläge.

Nach jeder Anweisung die @@Error auswerten ist nicht sehr praktikabel, das treibt zum einen die Laufzeit noch höher, zum anderen ist das ne Menge Arbeit, das einzubauen.

Zitat von alzaimar:

Bei den RAISERROR-Exceptions gibt es einen riesen Bug, der einfach nicht behoben wird:
Wenn in einer Transaktion NACH der ersten Änderung (INSERT, UPDATE, DELETE) eine Exception per RAISEERROR ausgelöst wird, bekommt die ADO-Komponente das nicht mit (es wird keine Exception ausgelöst, die mit Try..Except abzufangen wäre).

genau das hat mich zu SET NOCOUNT ON geführt (und zu oben beschriebenem Problem.

Ich glaube, es gibt momentan keine Lösung, solange ADO nicht ohne SET NOCOUNT ON auf Exceptions reagieren kann.

Alle bisherigen Vorschläge sind für mich einfach nicht praktikabel, aber ich danke vielmals für die Mühe, die ihr euch damit gemacht hab. Ich lasse das SET NOCOUNT ON einfach raus und debugge mich wie bisher mit dem QA durch, wenn ich ne Fehlermeldung kriege.

MfG McLane

P.S.:

Zitat von jensw_2000:

Man kann zusätzlich mit GOTO zwischen "Abbruch-Kreterien" und "verschmerzbare Fehler" differenzieren.

seit ich von Basic weg zu Borland Pascal gewechselt bin habe ich ein Motte: GOTO ist böse. Ja, es hilft oft auf einfache Art und Weise teils schwierige Probleme zu lösen, aber: "Nein, nicht stark ist die dunkle seit. Einfach, verführerisch, schnell sie ist. Schwer sich ihr zu entziehen..."

[alzaimar]

Ich verwende einen Rückgabeparameter.
Zu Anfang wird er z.B. auf 1 gesetzt, und am Ende auf 0. Wenn der Parameter, von Delphi aus aufgerufen, einen Wert von 1 hat, ist was schiefgelaufen.

[McLane]

Zitat von alzaimar:

Ich verwende einen Rückgabeparameter.
Zu Anfang wird er z.B. auf 1 gesetzt, und am Ende auf 0. Wenn der Parameter, von Delphi aus aufgerufen, einen Wert von 1 hat, ist was schiefgelaufen.

so simpel und doch nicht drauf gekommen... das löst immerhin das problem, überhaupt die exception mitzukriegen.
wunderbar, dank dir...
jetzt brauch ich nur noch die entsprechende fehlermeldung...
(*grübel*)

Zitat von alzaimar:

ADO kann mit mehreren Resultsets arbeiten. Jedes 'X rows affected' erzeugt ein eigenes Resultset. Du kannst mit 'NextResultset' auf ADO-Seite durch alle Resultsets durchiterieren.

das könnte funktionieren. probier ich morgen fürh gleich mal aus, müsste ja nur im fehlerfall auf das letzte resultset schauen... danke, ich werde vom ausgang des experiments berichten.