After Insert Trigger & sp_send_dbmail

Question

Hallo zusammen,

ich habe heute versucht mir per Trigger den Inhalt einer in die Tabelle Test eingefügten Zeile mit Hilfe der Prozedur 'sp_send_dbmail' an meine Email senden zu lassen. Dabei habe ich wohl einen Fehler in der Programmierung des Triggers produziert. Nach absetzen eines INSERTS passierte lange Zeit nichts, bis ich die Abfrage abgebrochen habe.

Seitdem kann ich weder die Tabelle öffnen oder löschen noch den Trigger ändern oder löschen. Folgender Fehler erscheint:

Fehler bei Löschen für Tabelle 'dbo.test'.  (Microsoft.SqlServer.Smo)

Das Timeout für Sperranforderung wurde überschritten. (Microsoft SQL Server, Fehler: 1222)

Unten habe ich auch die SQL-Anweisung für meinen Trigger angehängt. Kann mir jemand sagen, was ich falsch gemacht habe? Und vor allem, wie ich die Tabelle und den Trigger reparieren oder wenigstens löschen kann?

USE [database]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[MailFromInserted] 
   ON  [dbo].[test]
AFTER INSERT
AS 
BEGIN
    SET NOCOUNT ON;

    SELECT * INTO ##temp_test FROM inserted

    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'myprofile',
    @recipients = '[e-mail]',
    @subject = 'Test',
    @query = 'SELECT * FROM ##temp_test'
END

Viele Grüße

Alex

Answer 1

So auf den ersten Blick sehe ich dort zunächst, das in eine global temporäre Tabelle Daten eingefügt werden, ohne das überhaupt geprüft wird, ob diese vielleicht schon existiert. Da es sich möglicherweise nicht um ein "Single-User-System" handelt, kann das ja sehr gut sein.

Warum überhaupt global temporär? Genügt nicht auch lokal? Oder, je nach Datenmenge auch eine Tabellen-Variable?Die Notwendigkeit einer Email bei jedem einzelnen insert wurde hoffentlich von allen Seiten bedacht. (Oft genügt auch eine Email am Tag)

Auch das send_dbmail sollte man in ein Try-Catch wrappen.

Was sagt denn ein Blick in den Activity Monitor zu dem Problem?
Wer hält was für eine Sperre auf diesem Objkekt?
Da Du offenbar noch am Testen bist, kannst Du diese blockierende Sitzung sicher einfach mit Rechtsklick dort direkt killen.

---

Andreas Wolter | Microsoft Certified Master SQL Server

Blog: www.insidesql.org/blogs/andreaswolter
Web: www.andreas-wolter.com | www.SarpedonQualityLab.com

Answer 2

Hallo Andreas,

vielen Dank für deine Antwort!

Der Hinweis auf den Aktivitätsmonitor war sehr gut. Durch meinen INSERT-Befehl werden zwei Sitzungen/Prozesse erzeugt. Einmal das SQLCMD, das als 'suspended' gekennzeichnet ist (Blockiert von 58) und die SQL-Abfrage von meinem Rechner, die als 'running' gekennzeichnet wird. Beide hängen in der Warteschleife und werden nie beendet. Ich habe die Prozesse wie du vorgeschalgen hast mit Rechtsklick gekillt.

Desweiteren habe ich bereits versucht eine lokale temporäre Tabelle zu nutzen, jedoch erzeugt dies den Fehler

'Ungültiger Objektname '#temp_test'.'

Ich glaube, das liegt daran, dass die Funktion sp_send_dbmail in einer anderen session arbeitet und die lokal erzeugte Tabelle also nicht findet.

Ähnliches passiert mir mit einer Tabellen-Variablen:

'Die "@temp_test"-Tabellenvariable muss deklariert werden.'

Also muss ich ja eine globale temporäre Tabelle nehmen, oder? Leider besteht da aber weiterhin das Problem, dass der Prozess sich aus irgendeinem Grund aufhängt und nie abgeschlossen wird.
 
Und zu deinem Hinweis (Eine E-Mail pro Tag), wie genau kann man das denn realisieren? Wie wähle ich nur die Datensätze aus, die in den letzten 24 Stunden hinzugekommen sind?

Answer 3

1) Ich glaube, das liegt daran, dass die Funktion sp_send_dbmail in einer anderen session arbeitet und die lokal erzeugte Tabelle also nicht findet.

2) Also muss ich ja eine globale temporäre Tabelle nehmen, oder? Leider besteht da aber weiterhin das Problem, dass der Prozess sich aus irgendeinem Grund aufhängt und nie abgeschlossen wird.
 
3) Und zu deinem Hinweis (Eine E-Mail pro Tag), wie genau kann man das denn realisieren? Wie wähle ich nur die Datensätze aus, die in den letzten 24 Stunden hinzugekommen sind?

..

1) da hast Du wohl dann Recht, das hatte ich nicht mehr in Erinnerung.

2) Ein Muss gibt es selten. Es gibt immer noch genug Varianten. Warum temporäre und "Neu Erstellen"? Waum nicht statisch? Da fällt natürlich die redundante Datenhaltung noch mehr auf. Aber wenn man das mit Punkt 3 kombiniert, hat man dort eben immer nur die neuen eines Tages.

Was die Sperren angeht, müsste ich das schon nachbauen. Ansonsten ist ja die Frage offen: "Wer hält was für eine Sperre auf diesem Objekt?"Ohne diese Informationen kann ich nur raten.

3) Die Frage ist: Warum kann man es nicht an den Daten selbst erkennen? Das wäre doch noch einfacher. Ein Zeitstempel, oder ein spezielles Attribut, oder anhand der ID..

Das wären alles statische Methoden

Etwas komplexer aber auch gangbar wäre der Weg über Service Broker, der für asynchrone Verarbeitung ja speziell geschaffen ist.

Und dann halt einen Job, der einmal am Tag die Queue/Tabelle ausliest, und verschieckt, was verschickt werden muss. Entweder anhand des Zeitstempels, oder auch über eine kleine Hilfstabelle...

Und und und.. Einfach mal ein bisschen die Phantasie spielen lassen.

---

Andreas Wolter | Microsoft Certified Master SQL Server

Blog: www.insidesql.org/blogs/andreaswolter
Web: www.andreas-wolter.com | www.SarpedonQualityLab.com

Answer 4

Hallo Andreas,

danke nochmal. Du hast Recht, ich werde mich erstmal ausprobieren und schauen, welche Variante für uns die günstigste und logischste ist.

VG Alex