USE Util
GO
DECLARE @Drop VARCHAR(MAX),
@Create VARCHAR(MAX),
@ALterQue VARCHAR(MAX),
@Trigger1 VARCHAR(MAX),
@Trigger2 VARCHAR(MAX),
@StoredProc VARCHAR(MAX)
SELECT @Drop = '--#region DROP QUE, SERVICES, ROUTES
' + Util.dbo.StringConcat('DROP SERVICE [//Util/SQLExecution/TargetService' + DS + ']
GO
DROP QUEUE SQLExecutionQueue' + DS + ';
GO
DROP ROUTE SQLExecutionTargetRoute' + DS + '
GO
', '') + '--#endregion',
@Create = '--#region CREATE QUE, SERVICES, ROUTES
GO
' + Util.dbo.STringConcat('CREATE QUEUE SQLExecutionQueue' + DS + ';
GO
CREATE SERVICE [//Util/SQLExecution/TargetService' + DS + '] ON QUEUE SQLExecutionQueue' + DS + ' ([//Util/SQLExecution/Contract]);
GO
CREATE ROUTE SQLExecutionTargetRoute' + DS + ' WITH SERVICE_NAME = ''//Util/SQLExecution/TargetService' + DS + ''', ADDRESS = ''LOCAL'';
GO
', '') + '--#endregion',
@ALterQue = '--#region ALTER QUE
' + Util.dbo.STringConcat('ALTER QUEUE SQLExecutionQueue' + DS + '
WITH ACTIVATION
( STATUS = ON,
PROCEDURE_NAME = SE.SQLExecutionActivationProc' + DS + ',
MAX_QUEUE_READERS = 2
,EXECUTE AS SELF
--,EXECUTE AS 'CALLER'
);
GO
', '') + '--#endregion',
@Trigger1 = '--#region Initialize Handles
' + 'DECLARE ' + Util.dbo.STringConcat('@InitDlgHandle' + DS + ' UNIQUEIDENTIFIER', ', ') + '
' + Util.dbo.STringConcat('BEGIN DIALOG @InitDlgHandle' + DS + '
FROM SERVICE [//Util/SQLExecution/InitiatorService]
TO SERVICE N''//Util/SQLExecution/TargetService' + DS + '''
ON CONTRACT [//Util/SQLExecution/Contract]
WITH
ENCRYPTION = OFF;
', '
') + '--#endregion',
@Trigger2 = '--#region Send messages
GO
' + Util.dbo.STringConcat('SEND ON CONVERSATION @InitDlgHandle' + DS + '
MESSAGE TYPE
[//Util/SQLExecution/RequestMessage]
(@RequestMsg);
', '
') + '--#endregion',
@StoredProc = '--#region Activation Procedures
' + Util.dbo.STringConcat('DROP PROCEDURE SE.SQLExecutionActivationProc' + DS + '
GO
CREATE PROCEDURE SE.SQLExecutionActivationProc' + DS + '
-- WITH EXECUTE AS 'CALLER'
AS
DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER,
@RecvReqMsg XML,
@RecvReqMsgName SYSNAME,
@ExecutionId INT
WHILE (1 = 1)
BEGIN
SET @ExecutionId = NULL
WAITFOR
( RECEIVE TOP(1)
@RecvReqDlgHandle = conversation_handle,
@RecvReqMsg = message_body,
@RecvReqMsgName = message_type_name
FROM SQLExecutionQueue' + DS + '
)--, TIMEOUT 100;
IF (@@ROWCOUNT = 0)
BREAK;
IF @RecvReqMsgName = N''//Util/SQLExecution/RequestMessage''
SELECT @ExecutionId = T.item.value(''@ExecutionId'', ''int'')
FROM @RecvReqMsg.nodes(''/RequestMsg'') AS T (item)
ELSE
IF @RecvReqMsgName = N''http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog''
END CONVERSATION @RecvReqDlgHandle;
ELSE
IF @RecvReqMsgName = N''http://schemas.microsoft.com/SQL/ServiceBroker/Error''
END CONVERSATION @RecvReqDlgHandle;
IF @ExecutionId > 0
EXEC SE.SQLExecutionExecProc @ExecutionId = @ExecutionId, @QueName = ''SQLExecutionQueue' + DS + '''
END
GO
', '') + '--#endregion
'
FROM dbo.GetNumbers(1, 32)
CROSS APPLY (SELECT '_' + CAST( digit AS VARCHAR) AS DS) d
EXEC Util.dbo.PrintLargeText
@Drop
PRINT '--------- CREATE ---------'
EXEC Util.dbo.PrintLargeText
@Create
PRINT '--------- Trigger Initialize ---------'
EXEC Util.dbo.PrintLargeText
@Trigger1
PRINT '--------- Trigger Loop ---------'
EXEC Util.dbo.PrintLargeText
@Trigger2
PRINT '--------- Stored Procedures ---------'
EXEC Util.dbo.PrintLargeText
@StoredProc
PRINT '--------- ALTER QUE ---------'
EXEC Util.dbo.PrintLargeText
@ALterQue