Skripts für Service Broker

Veraltet

Diese Funktion wurde durch den integrierten Hangfire Server ersetzt.

Service-Broker aktivieren

if (select is_broker_enabled from sys.databases where database_id = DB_ID()) = 0
    BEGIN
        DECLARE @SQL nvarchar(max)
        BEGIN TRY
            SET @SQL = 'ALTER DATABASE [' + db_name() + '] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE '
            EXEC sp_executesql @SQL
        END TRY
        BEGIN CATCH
            SET @SQL = 'ALTER DATABASE [' + db_name() + '] SET NEW_BROKER WITH ROLLBACK IMMEDIATE '
            EXEC sp_executesql @SQL
    END CATCH
    END
GO

Asynchrone Skripts erstellen

CREATE PROCEDURE [dbo].[spAsyncReceive]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @message_body XML,
@message_type_name NVARCHAR(256),
@dialog UNIQUEIDENTIFIER,
@message_body_text NVARCHAR(MAX),
@command nvarchar(MAX),
@evtlogmsg nvarchar(MAX);
-- This procedure continues to process messages in the
-- queue until the queue is empty.
WHILE (1 = 1)
BEGIN
-- Receive the next available message
BEGIN TRANSACTION
-- just handle one message at a time
WAITFOR(RECEIVE TOP(1)
--the type of message received
@message_type_name = message_type_name,
-- the message contents
@message_body = message_body,
-- the identifier of the dialog this
@dialog = conversation_handle
FROM AsyncTargetQueue),
-- if the queue is empty for two seconds,
-- give up and go away
TIMEOUT 2000;
-- If RECEIVE did not return a message,
-- roll back the transaction and break out
-- of the while loop, exiting the procedure.
IF (@@ROWCOUNT = 0)
BEGIN
ROLLBACK TRANSACTION
END CONVERSATION @dialog ;
BREAK
END
-- Check to see if the message is an end dialog message.
IF (@message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
BEGIN
END CONVERSATION @dialog;
-- Message processed
COMMIT TRANSACTION
BREAK
END
-- Check to see if the message can be processed.
ELSE IF(@message_type_name = 'AsyncMessageType')
BEGIN
-- Excecute Stored Procedure
-- Message processed
COMMIT TRANSACTION
-- Run command
BEGIN TRY
SET @command = CAST(@message_body.query('/command/text()') AS NVARCHAR(MAX))
EXEC sp_executesql @command
END TRY
-- Handle Error
BEGIN CATCH
SET @evtlogmsg = 'ERROR: ' + CAST(ISNULL(ERROR_NUMBER(), 0)
AS NVARCHAR(MAX)) + CHAR(13) +
CHAR(10) + 'SEVERITY: ' +
CAST(ISNULL(ERROR_SEVERITY(), 0)
AS NVARCHAR(MAX)) + CHAR(13) +
CHAR(10) + 'STATE: ' +
CAST(ISNULL(ERROR_STATE(), 0)
AS NVARCHAR(MAX)) + CHAR(13) +
CHAR(10) + 'PROCEDURE: ' +
ISNULL(ERROR_PROCEDURE(), '') +
CHAR(13) + CHAR(10) + 'LINE: ' +
CAST(ISNULL(ERROR_LINE(), 0)
AS NVARCHAR(MAX)) + CHAR(10) +
'LINE: ' + CAST(ISNULL
(ERROR_LINE(), 0)
AS NVARCHAR(MAX))+ CHAR(13) +
CHAR(10) + ISNULL
(ERROR_MESSAGE(), '')
-- INSERT INTO ztEvents (Event) VALUES(@evtlogmsg)
INSERT INTO tbl_Protokoll (Text,Kategorie) VALUES(@evtlogmsg,N'Fehler (Asnyc)')
-- Remark:
-- For using the event log, the owner of the
-- database must be member of the sysadmin role.
-- This is the case for a user who is in the
-- db_owner role of the master database
-- (e.g. ''sa'' who owns the master database)
--EXEC xp_logevent 60000, @evtlogmsg, error
END CATCH
END
-- Unknown message type
ELSE
BEGIN
-- Let others process the message
ROLLBACK TRANSACTION
--INSERT INTO ztEvents (Event)
-- VALUES(N''Message type '' + @message_type_name +
-- '' <> AsyncMessageType for dialog #'' +
-- cast(ISNULL(@dialog,N''NULL'')
-- AS NVARCHAR(40)))
END CONVERSATION @dialog
BREAK
END
END
END
GO

CREATE PROCEDURE [dbo].[spAsyncSend]
@cmd nvarchar(max)
AS
BEGIN
SET NOCOUNT ON
-- Create Message
DECLARE @message XML
SET @message = N'<command><![CDATA[' + @cmd + ']]></command>'
-- Search for same Message
DECLARE @count bigint
SELECT @count = count(*)
FROM [dbo].[AsyncTargetQueue] WITH(NOLOCK)
WHERE status=1 and message_type_name = 'AsyncMessageType' and CAST(message_body AS NVARCHAR(MAX)) = CAST(@message AS NVARCHAR(MAX))
If @count > 0
RETURN
BEGIN TRANSACTION
-- Declare a variable to hold the conversation handle.
DECLARE @dialog UNIQUEIDENTIFIER
BEGIN DIALOG CONVERSATION @dialog
FROM SERVICE AsyncInitiatorService
TO SERVICE 'AsyncTargetService'
ON CONTRACT AsyncContract
WITH ENCRYPTION = OFF;
-- Send the message on the dialog.
SEND ON CONVERSATION @dialog
MESSAGE TYPE AsyncMessageType
(@message)
-- Commit the transaction. Service Broker
-- sends the message to the destination
-- service only when the transaction commits.
COMMIT TRANSACTION
-- End conversation.
END CONVERSATION @dialog
END
GO

CREATE MESSAGE TYPE [AsyncMessageType] AUTHORIZATION [dbo]
VALIDATION = WELL_FORMED_XML
CREATE CONTRACT [AsyncContract] AUTHORIZATION
[dbo] ([AsyncMessageType] SENT BY INITIATOR)
CREATE QUEUE [dbo].[AsyncInitiatorQueue] WITH STATUS = ON,
RETENTION = OFF ON [PRIMARY]
CREATE QUEUE [dbo].[AsyncTargetQueue] WITH STATUS = ON,
RETENTION = OFF, ACTIVATION (STATUS = ON,
PROCEDURE_NAME = [dbo].[spAsyncReceive],
MAX_QUEUE_READERS = 1, EXECUTE AS OWNER) ON [PRIMARY]
CREATE SERVICE [AsyncInitiatorService] AUTHORIZATION [dbo]
ON QUEUE [dbo].[AsyncInitiatorQueue]
CREATE SERVICE [AsyncTargetService] AUTHORIZATION [dbo]
ON QUEUE [dbo].[AsyncTargetQueue] ([AsyncContract])

Quelle

• Ursprungsdatei: skripts_fuer_service_broker.htm