SQL service broker wil niet werken

[Stefke]

Ik heb in een MS SQL database een tabel waar productiedata op binnenkomt, die data moet verwerkt worden door verschillende procedures.

In eerste instantie was ik van plan om de trigger verschillende stored procedures te laten starten (afhankelijk van het 'type' record) maar het leek mij slim na een gesprekje met ChatGPT om de procedures te laten verwerken via de service broker zodat de trigger niet wacht op de procedures (die mogelijk zwaar zijn/worden).
Dit is echter voor mij nieuw.

Dus ik heb het volgende ingesteld m.b.v. ChatGPT:

code:

ALTER DATABASE Dev20032 SET ENABLE_BROKER;
CREATE QUEUE [dbo].[StoredProcedureQueue]
CREATE SERVICE [StoredProcedureService] ON QUEUE [dbo].[StoredProcedureQueue];
ALTER QUEUE dbo.StoredProcedureQueue
    WITH ACTIVATION (
        PROCEDURE_NAME = dbo.ActivationProcedure,
        MAX_QUEUE_READERS = 1,
        EXECUTE AS OWNER)

Vervolgens heb ik een procedure ActivationProcedure die - bij wijze van test - alleen iets in een tabel moet loggen

code:

CREATE or ALTER PROCEDURE dbo.ActivationProcedure
AS
BEGIN
    print  'test'
    INSERT INTO tbl_Log (LogType, LogText) values ('test', 'ActivationProcedure')
END

De trigger zal het volgende richting de broker sturen (een XML msg die in de ActivationProcedure wordt uitgelezen en vervolgens via de juiste procedure wordt verwerkt

code:

--------------------
DECLARE @message_body XML;
DECLARE @dlg UNIQUEIDENTIFIER;
-- Construct the XML message body
SET @message_body = '
    <Message>
        <barcode>PS23060094/9.9</barcode>
        <palletweight>993</palletweight>
        <timestamp>' + CONVERT(NVARCHAR(30), GETDATE(), 120) + '</timestamp>
        <processing_procedure>spProcessStep1</processing_procedure>
    </Message>
';
-- Begin a dialog conversation
BEGIN DIALOG CONVERSATION @dlg
    FROM SERVICE [StoredProcedureService]
    TO SERVICE 'StoredProcedureService'
    ON CONTRACT [DEFAULT]
    WITH ENCRYPTION = OFF;
-- Send the XML message to the Service Broker queue using the conversation handle
SEND ON CONVERSATION @dlg (@message_body);
-- End the dialog conversation
END CONVERSATION @dlg;

Maar het lukt me niet om de ActivationProcedure aan het werk te krijgen.

Als ik een bericht stuur - normaal gesproken zou de tabeltrigger dit doen - op de volgende manier:

code:

DECLARE @message_body XML;
DECLARE @dlg UNIQUEIDENTIFIER;
-- Construct the XML message body
SET @message_body = '
    <Message>
        <barcode>PS23060094/9.9</barcode>
        <palletweight>993</palletweight>
        <timestamp>' + CONVERT(NVARCHAR(30), GETDATE(), 120) + '</timestamp>
        <processing_procedure>spProcessStep1</processing_procedure>
    </Message>
';
-- Begin a dialog conversation
BEGIN DIALOG CONVERSATION @dlg
    FROM SERVICE [StoredProcedureService]
    TO SERVICE 'StoredProcedureService'
    ON CONTRACT [DEFAULT]
    WITH ENCRYPTION = OFF;
-- Send the XML message to the Service Broker queue using the conversation handle
SEND ON CONVERSATION @dlg (@message_body);
-- End the dialog conversation
END CONVERSATION @dlg;
--debug
SELECT * FROM sys.services WHERE name = 'StoredProcedureService'
SELECT * FROM sys.service_queues WHERE name = 'StoredProcedureQueue'
SELECT * FROM StoredProcedureQueue
SELECT * FROM sys.transmission_queue
SELECT * FROM sys.conversation_endpoints
SELEcT top 10 * FROM tbl_Log order by logid desc
SELECT name, activation_procedure
FROM sys.service_queues
WHERE name = 'StoredProcedureQueue';

dan zie ik berichten, maar bij transmission_queue zie ik deze error/melding:
transmission_status

code:

The target service name could not be found. Ensure that the service name is specified correctly and/or the routing information has been supplied.
The target service name could not be found. Ensure that the service name is specified correctly and/or the routing information has been supplied.

en bij conversation_endpoints

code:

state_desc
DISCONNECTED_OUTBOUND

Wanneer ik TO SERVICE 'dbo.StoredProcedureService' aanpas naar TO SERVICE 'StoredProcedureService' zie ik niks terug.

Ik zie niet waar het nu mis gaat. Wie kan me hier mee helpen? Zit al een paar uur met ChatGPT aan de lijn, maar het wil niet werken. Denk dat de code die ChatGPT me gegeven heeft ook niet helemaal klopt

Voor de volledigheid: ik stuur dan wel een bericht naar de queue, maar wat ik nu stuur doet er natuurlijk verder niet toe (want de ActivationProcedure moet alleen maar starten en een insert doen)
Het probleem zit dus denk ik vooral in de link tussen het bericht dat binnenkomt en het starten van de A.P.

[ Voor 3% gewijzigd door Stefke op 03-04-2024 12:11 ]

[ElCondor]

In je bericht stuur code (4e snippet), regel 15 heb je [b] [/b] om dbo staan.
Klopt dat?

Even los van wat je probeert te bereiken: is het de bedoeling dat dit allemaal in SQL gebeurt?
Ik zou zelf denken dat je dit in de code laag hierboven wilt doen, dus de code die entiteiten naar SQL stuurt.
Of heb je daar geen invloed op? SQL Server is wellicht niet de aangewezen plek om records te manipuleren. I zou dit zelf altijd op voorhand voorbakken en dan pas de records naar SQL sturen zoals ik ze in de database wil hebben, ipv er in de database nog allerlei bewerkingen op te doen.
Ik kan je helaas op dit moment niet verder helpen met je daadwerkelijke SQL code. afgezien van de bold tags lijkt het wel te kloppen..
De logica van de dialog converstion ontgaat me en beetje. Je stuurt van de storedprocedure terug naar dezelfde procedure?

[ Voor 29% gewijzigd door ElCondor op 03-04-2024 12:02 ]

[Stefke]

Nee, dat was om te benadrukken (maar dat werkt dus niet ) dat het met dbo. records laat zien in die 2 laatste selects, zonder dbo. zie ik niks terug, maar het is me niet duidelijk of de messages nou niet aankomen, of wel aankomen, verwerkt worden maar de procedure niet triggert

De informatie in de tabel komt uit een (nieuwe) fabriek (via OPC UA), dat zijn diverse PLCs die allerlei meetgegevens gaan sturen. Daar heb ik geen invloed op, en omdat ik op dit punt ook nog niet weet hoe die informatie precies gaat binnenkomen heb ik gekozen voor deze oplossing: een generieke tabel waar alle data in binnenkomt en die ik verwerk op de juiste wijze, afhankelijk van wat voor record het is.
De procedures vertalen de meetgegevens naar voorraadmutaties.

Ik kan altijd in de trigger de juiste procedure kiezen en uitvoeren, maar inderdaad als de procedures zwaar zijn kan het zijn dat die de tabel/trigger gaan overbelasten.

De logica van de dialog converstion ontgaat me en beetje. Je stuurt van de storedprocedure terug naar dezelfde procedure?

Dat was me ook opgevallen, maar ik moest afgaan op wat ChatGPT me opgaf. Mogelijk een vergissing van ChatGPT (dat nogal eens voorkomt). Ik zou zo niet weten wat ik anders daar moet doen?

edit: ik heb een fout gemaakt, ik heb de activationprocedure iets te ver uitgekleed denk ik. Dit is de hele procedure:

code:

CREATE or ALTER PROCEDURE ActivationProcedure
AS
BEGIN
    DECLARE @message_body XML;
    DECLARE @barcode VARCHAR(30), @palletweight DECIMAL(18,2), @timestamp DATETIME, @processing_procedure NVARCHAR(128);
    DECLARE @conversation_handle UNIQUEIDENTIFIER;

    WHILE (1=1)
    BEGIN
        -- Receive a message from the queue
        WAITFOR (
            RECEIVE TOP(1)
                @message_body = message_body,
                @conversation_handle = conversation_handle -- Capture the conversation handle
            FROM dbo.StoredProcedureQueue
        ), TIMEOUT 1000; -- Set a timeout value

        -- Check if a message was received
        IF @@ROWCOUNT = 0
            print  'BREAK'
            INSERT INTO tbl_Log (LogType, LogText) values ('test', 'ActivationProcedure.BREAK')

            BREAK; -- Exit the loop if no message was received
        
        print  'test'
        INSERT INTO tbl_Log (LogType, LogText) values ('test', 'ActivationProcedure.TEST')

        -- Extract data from the message body
        SELECT @barcode = XMessage.value('(Message/barcode)[1]', 'VARCHAR(30)'),
                @palletweight = XMessage.value('(Message/palletweight)[1]', 'DECIMAL(18,2)'),
                @timestamp = XMessage.value('(Message/timestamp)[1]', 'DATETIME'),
                @processing_procedure = XMessage.value('(Message/processing_procedure)[1]', 'NVARCHAR(128)')
        FROM @message_body.nodes('/Message') AS XT(XMessage);

        -- Determine the processing procedure based on the value extracted from the message
        IF @processing_procedure = 'spProcessStep1'
        BEGIN
            print  @barcode
            --EXEC dbo.spProcessStep1 'PS23060094/9.6', 992.1, '2024-04-03 12:00' --@barcode, @palletweight, @timestamp;
            EXEC dbo.spProcessStep1 @barcode, @palletweight, @timestamp;
        END
        ELSE IF @processing_procedure = 'spProcessStep2'
        BEGIN
            EXEC dbo.spProcessStep1 @barcode, @palletweight, @timestamp;
        END
        -- Add more IF conditions for additional processing procedures as needed

        -- End the conversation
        END CONVERSATION @conversation_handle;
    END
END;

Nu heb ik mijn "log" er tussen gezet, maar er wordt nog steeds niks gelogd.

[ Voor 68% gewijzigd door Stefke op 03-04-2024 14:15 ]

[ElCondor]

Ik zou beginnen om dit eens door te nemen. En dan te kijken of dat wat CGPT zegt ook klopt met wat MS in zijn documentatie schrijft
Ik zou verwachten dat je een bron service en een doel service hebt. En dat die met elkaar moeten babbelen. Als de aanroepende service dezelfde is als de doelservice, zou het kunnen zijn dat MSSQL zegt: ik heb naast die anroepende service géén andere service die gebruikt kan worden...

[ Voor 35% gewijzigd door ElCondor op 03-04-2024 14:19 ]

[Stefke]

Als ik dat lees dan zie ik in ieder geval een fout:
BEGIN DIALOG CONVERSATION @dlg
FROM SERVICE [StoredProcedureService]
TO SERVICE 'StoredProcedureService'
ON CONTRACT [DEFAULT]
WITH ENCRYPTION = OFF;

Het moet "from service queue" naar "to service 'service'" zijn, niet van service naar service
Van die site

code:

CREATE QUEUE dbo.ExpenseQueue;
GO
CREATE SERVICE ExpensesService
    ON QUEUE dbo.ExpenseQueue; 

---
DECLARE @dialog_handle UNIQUEIDENTIFIER;

BEGIN DIALOG @dialog_handle  
FROM SERVICE ExpensesClient  
TO SERVICE 'ExpensesService';

alleen krijg ik een foutmelding als ik mijn StoredProcedureQueue daar gebruik

Ik duik er verder in, tnx

edit: ho wacht, ik lees er weer overheen. Het is FROM SERVICE xxxClient...
Maar die service maken ze niet in het voorbeeld, dus ik denk dat als er geen specifieke client is je idd dezelfde service moet gebruiken?

[ Voor 21% gewijzigd door Stefke op 03-04-2024 14:27 ]

[ElCondor]

Stefke schreef op woensdag 3 april 2024 @ 14:21:
Als ik dat lees dan zie ik in ieder geval een fout:
BEGIN DIALOG CONVERSATION @dlg
FROM SERVICE [StoredProcedureService]
TO SERVICE 'StoredProcedureService'
ON CONTRACT [DEFAULT]
WITH ENCRYPTION = OFF;

Het moet "from service queue" naar "to service 'service'" zijn, niet van service naar service
Van die site

code:

CREATE QUEUE dbo.ExpenseQueue;
GO
CREATE SERVICE ExpensesService
    ON QUEUE dbo.ExpenseQueue; 

---
DECLARE @dialog_handle UNIQUEIDENTIFIER;

BEGIN DIALOG @dialog_handle  
FROM SERVICE ExpensesClient  
TO SERVICE 'ExpensesService';

alleen krijg ik een foutmelding als ik mijn StoredProcedureQueue daar gebruik

Ik duik er verder in, tnx

edit: ho wacht, ik lees er weer overheen. Het is FROM SERVICE xxxClient...
Maar die service maken ze niet in het voorbeeld, dus ik denk dat als er geen specifieke client is je idd dezelfde service moet gebruiken?

Idd, ht zijn twee verschillende entiteiten die je moet definiëren. Wellicht hiermee eens kijken wat CGPT erover zegt. Hoe je zo'n client service definieert. Waarschijnlijk bijna hetzelfde als de andere service, vandaar dat CGPT ze niet uit elkaar weet te houden...

[Stefke]

Ja daar lijkt t op, tnx vooralsnog voor het wijzen van de juiste richting.

ChatGPT gaf aan dat het met één service kan, maar wellicht klopt dat niet. Op de MS site staat een voorbeeld van een singledatabase oplossing en daar werken ze ook met een initiator queue/service en target queue/service. Al kan ik me voorstellen dat het met één misschien ook zou kunnen werken is dat wellicht niet zo. De AI heeft het wel vaker fout