USE SqlExecDb
GO
SET NOCOUNT ON
DECLARE @UpperBound TINYINT = 32
SELECT 'DROP SERVICE [//SqlExecDb/SQLExecution/TargetService' + DS + ']
GO
DROP QUEUE SQLExecutionQueue' + DS + ';
GO
DROP ROUTE SQLExecutionTargetRoute' + DS + '
GO
' AS DropService
FROM dbo.GetNumbers(1, @UpperBound)
CROSS APPLY (SELECT '_' + CAST( digit AS VARCHAR) AS DS) d
SELECT 'CREATE QUEUE SQLExecutionQueue' + DS + ';
GO
CREATE SERVICE [//SqlExecDb/SQLExecution/TargetService' + DS + '] ON QUEUE SQLExecutionQueue' + DS + ' ([//SqlExecDb/SQLExecution/Contract]);
GO
CREATE ROUTE SQLExecutionTargetRoute' + DS + ' WITH SERVICE_NAME = ''//SqlExecDb/SQLExecution/TargetService' + DS + ''', ADDRESS = ''LOCAL'';
GO
' AS CreateQue
FROM dbo.GetNumbers(1, @UpperBound)
CROSS APPLY (SELECT '_' + CAST( digit AS VARCHAR) AS DS) d
SELECT 'ALTER QUEUE SQLExecutionQueue' + DS + '
WITH ACTIVATION
( STATUS = ON,
PROCEDURE_NAME = SE.SQLExecutionActivationProc' + DS + ',
MAX_QUEUE_READERS = 2
,EXECUTE AS SELF);
GO
' AS AlterQue
FROM dbo.GetNumbers(1, @UpperBound)
CROSS APPLY (SELECT '_' + CAST( digit AS VARCHAR) AS DS) d
SELECT '@InitDlgHandle' + DS + ' UNIQUEIDENTIFIER, ' AS TriggerDeclareDialogVariable
FROM dbo.GetNumbers(1, @UpperBound)
CROSS APPLY (SELECT '_' + CAST( digit AS VARCHAR) AS DS) d
SELECT 'BEGIN DIALOG @InitDlgHandle' + DS + '
FROM SERVICE [//SqlExecDb/SQLExecution/InitiatorService]
TO SERVICE N''//SqlExecDb/SQLExecution/TargetService' + DS + '''
ON CONTRACT [//SqlExecDb/SQLExecution/Contract]
WITH
ENCRYPTION = OFF;
' AS TriggerDeclareDialog
FROM dbo.GetNumbers(1, @UpperBound)
CROSS APPLY (SELECT '_' + CAST( digit AS VARCHAR) AS DS) d
SELECT 'SEND ON CONVERSATION @InitDlgHandle' + DS + '
MESSAGE TYPE
[//SqlExecDb/SQLExecution/RequestMessage]
(@RequestMsg);
' AS TriggerSendConversation
FROM dbo.GetNumbers(1, @UpperBound)
CROSS APPLY (SELECT '_' + CAST( digit AS VARCHAR) AS DS) d
SELECT 'DROP PROCEDURE SE.SQLExecutionActivationProc' + DS + '
GO
CREATE PROCEDURE SE.SQLExecutionActivationProc' + DS + '
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''//SqlExecDb/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
' AS StoredProcedure
FROM dbo.GetNumbers(1, @UpperBound)
CROSS APPLY (SELECT '_' + CAST( digit AS VARCHAR) AS DS) d