QUEUE PROCEDURE_NAME on asi mis tuleb selgeks teha
Kui vaja, et saadaks sõnumi "saatmise" peale tuleb teha üks QUEUE ja panna serviced sama QUEUEd kasutama
Kui saadad sama serveri piires, siis WITH ENCRYPTION = OFF, muidu ei pruugi töötada
CREATE MESSAGE TYPE [KAMPUS_INVOICE] VALIDATION = WELL_FORMED_XML
CREATE CONTRACT [KAMPUS_SEND_INVOICE_EMAIL]
([KAMPUS_INVOICE] SENT BY INITIATOR)
CREATE QUEUE KAMPUS_INVOICES
WITH ACTIVATION
(
STATUS = ON,
PROCEDURE_NAME = KAMPUS_ARVE_EPOST3,
MAX_QUEUE_READERS = 2,
EXECUTE AS 'DBO' )
CREATE SERVICE KAMPUS_SEND3
ON QUEUE KAMPUS_INVOICES ([KAMPUS_SEND_INVOICE_EMAIL])
CREATE SERVICE KAMPUS_RECEIVE3
ON QUEUE KAMPUS_INVOICES ([KAMPUS_SEND_INVOICE_EMAIL])
ja kui nüüd saata sõnum
DECLARE @StockDialog uniqueidentifier
DECLARE @Message XML
BEGIN DIALOG CONVERSATION @StockDialog
FROM SERVICE KAMPUS_SEND3
TO SERVICE 'KAMPUS_RECEIVE3'
ON CONTRACT KAMPUS_SEND_INVOICE_EMAIL
WITH ENCRYPTION = OFF
SET @Message = CAST('<mess>Add 12 widgets to inventory</mess>' AS XML);
SEND ON CONVERSATION @StockDialog
MESSAGE TYPE KAMPUS_INVOICE (@Message)
SET @Message = CAST('<mess>Remove 4 springs from inventory</mess>' AS XML);
SEND ON CONVERSATION @StockDialog
MESSAGE TYPE KAMPUS_INVOICE (@Message)
END CONVERSATION @StockDialog
siis tõmmatakse protseduur KAMPUS_ARVE_EPOST3 käima
ALTER PROCEDURE [dbo].[KAMPUS_ARVE_EPOST3]
AS
BEGIN
SET NOCOUNT ON
DECLARE @dialog_handle UNIQUEIDENTIFIER, @sonu NVARCHAR(2300), @mb VARBINARY(MAX), @jrk INT, @jrkmax INT, @sub NVARCHAR(400)
DECLARE @tulem TABLE ( jrk INT IDENTITY(1,1) PRIMARY KEY, [STATUS] TINYINT, [SERVICE_NAME] NVARCHAR(512), [conversation_handle] UNIQUEIDENTIFIER, message_body
VARBINARY(MAX) )
;
WAITFOR
(
RECEIVE [status], [SERVICE_NAME], [conversation_handle],message_body
FROM KAMPUS_INVOICES
INTO @tulem
), TIMEOUT 8000
SET @jrk = 1
SET @dialog_handle = (SELECT TOP 1 [conversation_handle] FROM @tulem)
SET @jrkmax=(SELECT MAX(jrk) FROM @tulem)
WHILE @jrk <= @jrkmax
BEGIN
SELECT @sonu = CAST(message_body AS NVARCHAR(2000)) FROM @tulem WHERE jrk = @jrk
IF @sonu IS NOT NULL
BEGIN
SET @sub='KAMPUS_INVOICES_SEND3 '+CAST(CURRENT_TIMESTAMP AS NVARCHAR(25))
EXECUTE [dbo].[SEND_MAIL_KUIDO_I]
@mail_post_from='saatja@email.com'
,@mail_to='saaja@email.com'
,@message_subject=@sub
,@sonum=@sonu
END
SET @jrk=@jrk+1
END
IF @dialog_handle IS NOT NULL
END CONVERSATION @dialog_handle
END
END
Kui on vaja panna "lugemise" peale tööle, ehk tehakse kaks QUEUE
CREATE MESSAGE TYPE [KAMPUS_INVOICE] VALIDATION = WELL_FORMED_XML
CREATE CONTRACT [KAMPUS_SEND_INVOICE_EMAIL]
([KAMPUS_INVOICE] SENT BY INITIATOR)
--TEEME KAKS QUEUED
CREATE QUEUE KAMPUS_INVOICES_SEND
CREATE QUEUE KAMPUS_INVOICES_READ
WITH ACTIVATION
(
STATUS = ON,
PROCEDURE_NAME = KAMPUS_ARVE_EPOST,
MAX_QUEUE_READERS = 2,
EXECUTE AS 'DBO' )
--ja SERVICED on erinevate QUEUE-de peal
CREATE SERVICE KAMPUS_SEND
ON QUEUE KAMPUS_INVOICES_SEND ([KAMPUS_SEND_INVOICE_EMAIL])
CREATE SERVICE KAMPUS_RECEIVE
ON QUEUE KAMPUS_INVOICES_READ ([KAMPUS_SEND_INVOICE_EMAIL])
--kui nüüd saata
DECLARE @StockDialog uniqueidentifier
DECLARE @Message XML
BEGIN DIALOG CONVERSATION @StockDialog
FROM SERVICE KAMPUS_SEND
TO SERVICE 'KAMPUS_RECEIVE'
ON CONTRACT KAMPUS_SEND_INVOICE_EMAIL
WITH ENCRYPTION = OFF
SET @Message = CAST('<mess>Add 12 widgets to inventory</mess>' AS XML);
SEND ON CONVERSATION @StockDialog
MESSAGE TYPE KAMPUS_INVOICE (@Message)
SET @Message = CAST('<mess>Remove 4 springs from inventory</mess>' AS XML);
SEND ON CONVERSATION @StockDialog
MESSAGE TYPE KAMPUS_INVOICE (@Message)
END CONVERSATION @StockDialog
--tõmmatakse protseduur käima KAMPUS_ARVE_EPOST mis loeb ainult KAMPUS_INVOICES_READ QUEUE-st
ALTER PROCEDURE [dbo].[KAMPUS_ARVE_EPOST]
AS
BEGIN
SET NOCOUNT ON
DECLARE @dialog_handle UNIQUEIDENTIFIER, @sonu NVARCHAR(2300), @mb VARBINARY(MAX), @jrk INT, @jrkmax INT, @sub NVARCHAR(50)
DECLARE @tulem TABLE ( jrk INT IDENTITY(1,1) PRIMARY KEY, [STATUS] TINYINT, [SERVICE_NAME] NVARCHAR(512), [conversation_handle] UNIQUEIDENTIFIER, message_body
VARBINARY(MAX) )
;
WAITFOR
(
RECEIVE [status], [SERVICE_NAME], [conversation_handle],message_body
FROM [KAMPUS_INVOICES_READ]
INTO @tulem
), TIMEOUT 8000
SET @jrk = 1
SET @dialog_handle = (SELECT TOP 1 [conversation_handle] FROM @tulem)
SET @jrkmax=(SELECT MAX(jrk) FROM @tulem)
WHILE @jrk <= @jrkmax
BEGIN
SELECT @sonu = CAST(message_body AS NVARCHAR(2000)) FROM @tulem WHERE jrk = @jrk
IF @sonu IS NOT NULL
BEGIN
SET @sub='KAMPUS_INVOICES_READ '+CAST(CURRENT_TIMESTAMP AS NVARCHAR(25))
EXECUTE [dbo].[SEND_MAIL_KUIDO_I]
@mail_post_from='saatja@email.com'
,@mail_to='saaja@email.com'
,@message_subject=@sub
,@sonum=@sonu
END
SET @jrk=@jrk+1
END
IF @dialog_handle IS NOT NULL
END CONVERSATION @dialog_handle
END
salakaval ; peab olema õige koha peal
Blogged with the Flock Browser
