Problem mit dem Service Broker

[holzer00]

Hallo zusammen

Ich beschäftige mich seit gestern mit den Service Brokern und brauche eure Hilfe!

Herausforderung:
Sobald der Wert eines Datenfeldes einen bestimmten Wert annimmt sollen verschiedene Werte der gleichen Datenbank automatisch in ein PDF gedruckt werden.

Also entschied ich mich für folgenden Weg:
SQL --> Trigger --> VB-Script --> Crystal Reports --> PDF

Also eingentlich lief alles aber was passierte dann:
Weil der Triger synchron läuft gabs ein Deadlock ;-)
Crystal Reports will aus der gleichen Datenbank lesen auf der gerade der Trigger läuft! --> Die Datenbank hängt!

Also möchte ich nun das ganze mit nem Service Broker machen:
SQL --> Trigger --> Service Broker --> VB-Script --> Crystal Reports --> PDF

Somit sollte der Zugriff von Crystal Reports erfolgen "nachdem" der Trigger beendet wurde ...

Habe mir dann mal zum Einstieg folgenden Service Broker kopiert aber ich empfange keine Daten:

http://www.codeguru.com/cpp/data/mfc...ice-Broker.htm

USE db-sandbox
GO
-----------------------------------------------------

IF EXISTS (SELECT * FROM sys.services WHERE name = 'StockSendService') DROP SERVICE StockSendService
IF EXISTS (SELECT * FROM sys.services WHERE name = 'StockReceiveService') DROP SERVICE StockReceiveService
IF EXISTS (SELECT * FROM sys.service_contracts WHERE name = 'StockContract') DROP CONTRACT StockContract
IF EXISTS (SELECT * FROM sys.service_message_types WHERE name = 'StockMessage') DROP MESSAGE TYPE StockMessage
IF EXISTS (SELECT * FROM sys.service_queues WHERE name = 'StockSendQueue') DROP QUEUE StockSendQueue
IF EXISTS (SELECT * FROM sys.service_queues WHERE name = 'StockReceiveQueue') DROP QUEUE StockReceiveQueue
-----------------------------------------------------

CREATE MESSAGE TYPE StockMessage
VALIDATION = NONE

CREATE CONTRACT StockContract
(StockMessage SENT BY INITIATOR)

CREATE QUEUE StockSendQueue
CREATE QUEUE StockReceiveQueue

CREATE SERVICE StockSendService
ON QUEUE StockSendQueue (StockContract)

CREATE SERVICE StockReceiveService
ON QUEUE StockReceiveQueue (StockContract)
GO
-----------------------------------------------------

DECLARE @StockDialog uniqueidentifier
DECLARE @Message nvarchar(128)

BEGIN DIALOG CONVERSATION @StockDialog
FROM SERVICE StockSendService
TO SERVICE 'StockReceiveService'
ON CONTRACT StockContract
WITH ENCRYPTION = OFF

SET @Message = N'Add 12 widgets to inventory';
SEND ON CONVERSATION @StockDialog
MESSAGE TYPE StockMessage (@Message)

SET @Message = N'Remove 4 springs from inventory';
SEND ON CONVERSATION @StockDialog
MESSAGE TYPE StockMessage (@Message)

SET @Message = N'Add 7 twonkies to inventory';
SEND ON CONVERSATION @StockDialog
MESSAGE TYPE StockMessage (@Message);
GO
------------------------------------------------------------------

RECEIVE CONVERT(NVARCHAR(max), message_body) AS message
FROM StockReceiveQueue
GO
------------------------------------------------------------------


Also der Quellcode läuft ohne Fehlermeldung aber die Ausgabe "message" bleibt leer! Die Daten werden wohl in die Queue gesteckt aber ich empfange Sie nicht.

Wer weiß weiter?

Schonmal Danke fürs Helfen
Christian

[O. Helper]

Hallo Christian,

wenn ich es auf die Schnelle richtig sehe, fehlt ein END CONVERSATION nach dem SEND ON CONVERSATION.

Ein funktionierendes und mit der AdventureWorks nachvollziehbares Beispiel von mir findest Du hier:
http://olafhelper.over-blog.de/article-26077104.html

[holzer00]

Ich mag SQL nich ..

Heidihow und vielen Dank!

Habs jetzt hinbekommen!

Hier nochmal der Ablauf:
SQL --> Trigger --> Service Broker --> Stored Prozedure --> VB-Script --> Crystal Reports --> PDF

Jetzt hab ich nur noch eine Frage:
Der Trigger wird ja automatisch gestartet nach nem Update auf "QS_SERIAL_NUMBERS". OK, aber die Prozedur muss vom Trigger dann mit EXEC angestoßen werden? Oder?

Hier der Quelltext: (funktioniert!)

-- Service Broker aktivieren
ALTER DATABASE "dbquality-sandbox" SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE "dbquality-sandbox" SET NEW_BROKER
ALTER DATABASE "dbquality-sandbox" SET MULTI_USER
GO

USE "dbquality-sandbox"
GO

--------------- Drop all objects: TRIGGER, SERVICE, CONTRACT, MESSAGES, SEND/RECEIVE-QUEUE ---------------
IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'SBTrigger') DROP TRIGGER SBTrigger
IF EXISTS (SELECT * FROM sys.services WHERE name = 'SBSendService') DROP SERVICE SBSendService
IF EXISTS (SELECT * FROM sys.service_contracts WHERE name = 'SBContract') DROP CONTRACT SBContract
IF EXISTS (SELECT * FROM sys.service_message_types WHERE name = 'SBSendMessage') DROP MESSAGE TYPE SBSendMessage
IF EXISTS (SELECT * FROM sys.service_queues WHERE name = 'SBSendQueue') DROP QUEUE SBSendQueue
IF EXISTS (SELECT * FROM sys.sysobjects WHERE id = object_id(N'[dbo].[SBStoredProc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE SBStoredProc
GO
----------------------------------------------------------------------------------------------------------

------------------------------ Erstelle Nachricht ----------------------------
CREATE MESSAGE TYPE SBSendMessage
VALIDATION = NONE;
GO
------------------------------------------------------------------------------

------------------------------ Erstelle Vertrag ------------------------------
CREATE CONTRACT SBContract
(SBSendMessage SENT BY ANY);
GO
------------------------------------------------------------------------------

-------------- SP fürs Abarbeiten der Warteschlange erstellen ----------------
CREATE PROCEDURE SBStoredProc
AS
BEGIN
DECLARE @SBHandleID UNIQUEIDENTIFIER,
@ReceiveMessage NVARCHAR(100),
@ReceiveMessageName sysname,
@Result int;

-- Erste Nachricht abholen und je Lauf auch nur eine abarbeiten (alternative WHILE Schleife)
RECEIVE TOP(1)
@SBHandleID = conversation_handle,
@ReceiveMessage = message_body,
@ReceiveMessageName = message_type_name
FROM SBSendQueue

SELECT @SBHandleID AS SBHandleID
SELECT @ReceiveMessage AS ReceiveMessage
SELECT @ReceiveMessageName AS ReceiveMessageName
/*
EXEC @Result = xp_cmdshell 'dir'
IF (@Result = 0)
PRINT 'Success'
ELSE
PRINT 'Failure'
*/
END
GO

------------------------------- Erstelle Queue -------------------------------
CREATE QUEUE SBSendQueue
WITH STATUS = ON, RETENTION = OFF, ACTIVATION(STATUS = ON,
PROCEDURE_NAME = SBStoredProc, MAX_QUEUE_READERS = 1, EXECUTE AS Owner);
GO
------------------------------------------------------------------------------

------------------------------- Erstelle Dienst ------------------------------
CREATE SERVICE SBSendService
ON QUEUE SBSendQueue
(SBContract);
GO
------------------------------------------------------------------------------

------------------------------- Erstelle Trigger -----------------------------
CREATE TRIGGER SBTrigger
ON QS_SERIAL_NUMBERS
AFTER UPDATE
AS

DECLARE @SBHandleID UNIQUEIDENTIFIER,
@SendMessage NVARCHAR(128),
@LastState tinyint,
@SerialNumber varchar(64);

SET @LastState = (SELECT LAST_STATE FROM Inserted)
SET @SerialNumber = (SELECT SERIAL_NUMBER FROM Inserted)

IF @LastState = 6
BEGIN
BEGIN TRANSACTION;

BEGIN DIALOG @SBHandleID
FROM SERVICE SBSendService
TO SERVICE 'SBSendService'
ON CONTRACT SBContract
WITH ENCRYPTION = OFF;

SELECT @SendMessage = N'C:\Trigger2pdf\report_pdf.vbs ' + @SerialNumber;
--SELECT @SendMessage AS SendMessage;
PRINT @SerialNumber;
PRINT 'STATUS = 6';

SEND ON CONVERSATION @SBHandleID
MESSAGE TYPE SBSendMessage (@SendMessage);
END CONVERSATION @SBHandleID

COMMIT TRANSACTION;

EXEC SBStoredProc

END
GO
------------------------------------------------------------------------------

[O. Helper]

OK, aber die Prozedur muss vom Trigger dann mit EXEC angestoßen werden? Oder?

Nein; dann könntest Du Dir das ganze mit dem Service Broker sparen.
Die SP wird von der Queue angestartet, sobald Nachrichten eingehen und das hast Du mit
[highlight=SQL]
CREATE QUEUE SBSendQueue
WITH STATUS = ON, RETENTION = OFF, ACTIVATION(STATUS = ON,
PROCEDURE_NAME = SBStoredProc, MAX_QUEUE_READERS = 1, EXECUTE AS Owner);
GO
[/highlight]
schon ganz richtig gemacht, indem Du dort die SP angegeben hast.

[holzer00]

ohne EXEC läuft es nicht ...

Hi Olaf

vielen Dank für deine Antwort! Ich war eigentlich der gleichen Meinung wie du. Aber ohne das "EXEC SBStoredProc" wird die SP nicht ausgeführt!

Wenn ich den Aufruf in den Trigger schreibe werden die Werte über die Queue korrekt übergeben --> alles funktioniert wies es soll! Also stimmt ja die Kommunikation zwischen Trigger und SP. Aber die SP wird nicht automatisch aufgerufen wenn Daten vom Trigger anstehen! (was Sie ja eigentlich soll)

An was kann das liegen?

Gruss Christian