CREATE FUNCTION [dbo].[sb_SelectInitiatorConversationHandle](
	@InitiatorServiceName SYSNAME,
	@TargetServiceName SYSNAME
) 
RETURNS UNIQUEIDENTIFIER AS BEGIN
	DECLARE @ConversationHandle UNIQUEIDENTIFIER;
	SELECT TOP(1) @ConversationHandle = [conversation_handle]
	FROM sys.conversation_endpoints WITH (NOLOCK)
	WHERE
		far_service = @TargetServiceName AND 
		service_id = (
			SELECT service_id 
			FROM sys.services WITH (NOLOCK)
			WHERE name = @InitiatorServiceName
		)
	AND is_initiator = 1;
	RETURN @ConversationHandle;
END;
GO
CREATE FUNCTION [dbo].[sb_SelectTargetConversationHandle](
	@InitiatorServiceName SYSNAME,
	@TargetServiceName SYSNAME
) 
RETURNS UNIQUEIDENTIFIER AS BEGIN
	RETURN (
		SELECT conversation_handle
		FROM sys.conversation_endpoints WITH (NOLOCK)
		WHERE conversation_id = (
			SELECT TOP(1) conversation_id
			FROM sys.conversation_endpoints WITH (NOLOCK)
			WHERE 
				far_service = @TargetServiceName AND service_id = (
					SELECT service_id 
					FROM sys.services WITH (NOLOCK)
					WHERE name = @InitiatorServiceName
				)
			AND is_initiator = 1
		)
		AND is_initiator = 0
	);
END;
GO
ALTER PROCEDURE [dbo].[sb_InitializeConversation](@InitiatorServiceName SYSNAME, @TargetServiceName SYSNAME, @InitiatorConversationHandle UNIQUEIDENTIFIER = '00000000-0000-0000-0000-000000000000' OUTPUT, @TargetConversationHandle UNIQUEIDENTIFIER = '00000000-0000-0000-0000-000000000000' OUTPUT) AS BEGIN
	SET NOCOUNT ON;
	DECLARE @message NVARCHAR(MAX);
	IF (SELECT is_broker_enabled FROM sys.databases WHERE database_id = DB_ID()) = 0 BEGIN
		SELECT @message = 'Service broker is disabled for database ''' + DB_NAME() + '''. Use "ALTER DATABASE ' + QUOTENAME(DB_NAME()) + ' SET ENABLE_BROKER" to enable it.';
		RAISERROR(@message, 11, 1);
		RETURN -1;
	END;
	IF NOT EXISTS (SELECT * FROM sys.services WHERE name = @InitiatorServiceName) BEGIN
		SELECT @message = 'Initiator service ''' + @InitiatorServiceName + ''' does not exist on the local server. Service names are case- and accent-sensitive.';
		RAISERROR(@message, 11, 1);
		RETURN -1;
	END;
	IF NOT EXISTS (SELECT * FROM sys.services WHERE name = @TargetServiceName) BEGIN
		SELECT @message = 'Target service ''' + @TargetServiceName + ''' does not exist on the local server. Service names are case- and accent-sensitive. This procedure cannot be used to create conversations to remote services.';
		RAISERROR(@message, 11, 1);
		RETURN -1;
	END;
 
	DECLARE @queueName SYSNAME;
	SELECT @queueName = dbo.sb_SelectServiceQueue(@TargetServiceName);
 
	DECLARE @xml NVARCHAR(MAX);
	DECLARE @PARAMETERS NVARCHAR(MAX);
	SELECT @xml =	
		'BEGIN TRY' + CHAR(13) + CHAR(10) +
		'  BEGIN' + CHAR(13) + CHAR(10) +
		'    BEGIN DIALOG CONVERSATION @initiatorConversationHandle FROM SERVICE ' + @InitiatorServiceName + ' TO SERVICE ''' + @TargetServiceName + ''' WITH ENCRYPTION = OFF; ' + CHAR(13) + CHAR(10) +
		'    SEND ON CONVERSATION @initiatorConversationHandle(''''); ' + CHAR(13) + CHAR(10) +
		'    DECLARE @dummy TABLE([message] VARBINARY(MAX)); ' + CHAR(13) + CHAR(10) +
		'    SELECT @targetConversationHandle = conversation_handle FROM sys.conversation_endpoints WHERE conversation_id = (SELECT conversation_id FROM sys.conversation_endpoints WHERE conversation_handle = @initiatorConversationHandle) AND is_initiator = 0;' + CHAR(13) + CHAR(10) +
		'    IF @@ROWCOUNT = 0 RAISERROR(''Send from ''''' + @InitiatorServiceName + ''''' to ''''' + @TargetServiceName + ''''' failed. Contract DEFAULT not mapped?'', 11, 0);'  + CHAR(13) + CHAR(10) +
		'    RECEIVE message_body FROM ' + @queueName + ' INTO @dummy WHERE conversation_handle = @targetConversationHandle;' + CHAR(13) + CHAR(10) +
		'    IF @@ROWCOUNT = 0 RAISERROR(''Receive from ''''' + @InitiatorServiceName + ''''' to ''''' + @TargetServiceName + ''''' failed.'', 11, 0);'  + CHAR(13) + CHAR(10) +
		'  END' + CHAR(13) + CHAR(10) +
		'END TRY' + CHAR(13) + CHAR(10) +
		'BEGIN CATCH' + CHAR(13) + CHAR(10) +
		'  BEGIN' + CHAR(13) + CHAR(10) +
		'    DECLARE @message NVARCHAR(MAX) = ERROR_MESSAGE(); ' + CHAR(13) + CHAR(10) +
		'    IF @initiatorConversationHandle IS NOT NULL ' + CHAR(13) + CHAR(10) +
		'      END CONVERSATION @initiatorConversationHandle WITH CLEANUP;' + CHAR(13) + CHAR(10) +
		'    SET @initiatorConversationHandle = NULL;' + CHAR(13) + CHAR(10) +
		'    SET @targetConversationHandle = NULL;' + CHAR(13) + CHAR(10) +
		'    RAISERROR(@message, 11, 0);' + CHAR(13) + CHAR(10) +
		' END' + CHAR(13) + CHAR(10) +
		'END CATCH';
	;
	SELECT @PARAMETERS = N'@initiatorConversationHandle UNIQUEIDENTIFIER OUTPUT, @targetConversationHandle UNIQUEIDENTIFIER OUTPUT';
	--PRINT @xml;
	DECLARE @ReturnTable INT;
	SELECT @ReturnTable = CASE WHEN NOT (@InitiatorConversationHandle IS NULL AND @TargetConversationHandle IS NULL) THEN 1 ELSE 0 END;
	DECLARE @RESULT INT;
	EXEC @RESULT = SP_EXECUTESQL @xml, @PARAMETERS, @initiatorConversationHandle = @initiatorConversationHandle OUTPUT, @targetConversationHandle = @targetConversationHandle OUTPUT;
	IF @RESULT <> 0 OR @InitiatorConversationHandle IS NULL OR @TargetConversationHandle IS NULL RETURN -1;
	IF @ReturnTable = 1
		SELECT @initiatorConversationHandle AS InitiatorConversationHandle, @targetConversationHandle AS TargetConversationHandle;
END;