IncomingObjects.tt

<#@ template debug="false" hostspecific="false" language="C#" #>
<#@ output extension=".sql" #>
<#@ SqlModelDirective processor="SqlModelDirectiveProcessor" #>
<#@ import namespace="System.Text" #>
<#@ import namespace="System.Collections.Generic" #>
/*
 *
 *    For information about these objects, see: *.tt file.
 *
 */
<#	
	List<String> objects = GetObjects();
	foreach (var obj in objects)
	{
#>
GO
/* ServiceBroker -> ReadIncomingQueue */
CREATE PROCEDURE [SB].[ReadIncomingQueue_<#= obj #>]
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @conversation_handle UNIQUEIDENTIFIER,
			@service_name SYSNAME,
			@service_contract_name SYSNAME,
			@message_body XML,
			@message_type_name SYSNAME,
			@test_message_type_name sysname = '//ANY/ANY/Test_MessageType',
			@real_message_type_name sysname = '//<#= obj.Replace("_", "/") #>_MessageType';
			
	WAITFOR DELAY '00:00:00:100';
		
	WHILE (1 = 1)
	BEGIN
		BEGIN TRANSACTION;

		BEGIN TRY
			WAITFOR (
						RECEIVE TOP (1) 
								@conversation_handle = [conversation_handle],
								@message_body = CAST([message_body] AS XML),
								@message_type_name = [message_type_name],
								@service_name = [service_name],
								@service_contract_name = [service_contract_name]
						FROM	[SB].[IncomingQueue_<#= obj #>]
					), TIMEOUT 1000;
				
			IF ( @@ROWCOUNT = 0 AND @@TRANCOUNT > 0 )
			BEGIN
				ROLLBACK TRANSACTION;
				BREAK;
			END;

			IF @message_type_name = @real_message_type_name
			BEGIN
				/********************************/
				/***    message processing    ***/

				EXEC [SB].[ProcessIncomingMessage_<#= obj #>] 
					@message_body;

				/***    message processing    ***/
				/********************************/
					
				-- If we reach this point than no errors happens during processing, so send success reply. Sender should initiate "end conversation".
				SEND ON CONVERSATION @conversation_handle
					MESSAGE TYPE @message_type_name(CAST('<success />' AS XML));
			END
			ELSE IF @message_type_name = @test_message_type_name
			BEGIN

				/*
				-- Throw exception to test how error handling working.
				RAISERROR ('Testing error handling...', 16, 1 );
				*/
				
				/*
				This is test message to test. Nothing to do. Just send reply.
				
				Or we can send test email:
				EXEC [SB].[SendEmailWithServiceBrokerMessageDetails]
					'Test message received',
					@service_name,
					@service_contract_name,
					@message_type_name,
					@message_body;
				*/
					
				-- If we reach this point than no errors happens during processing, so send success reply. Sender should initiate "end conversation".
				SEND ON CONVERSATION @conversation_handle
					MESSAGE TYPE @message_type_name(CAST('<success />' AS XML));  
			END
			ELSE IF @message_type_name = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
			BEGIN
				-- Sender initiate "end conversation with error". Log the error and finish conversation as well.
				EXEC [SB].[SendEmailWithServiceBrokerMessageDetails]
					'Error message received',
					@service_name,
					@service_contract_name,
					@message_type_name,
					@message_body;

				END CONVERSATION @conversation_handle;
			END
			ELSE IF @message_type_name = N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
			BEGIN
				-- Sender initiate "end conversation", so we finish conversation as well.
				END CONVERSATION @conversation_handle;
			END;
			
			COMMIT TRANSACTION;
		END TRY
		BEGIN CATCH
			/*
				Test XACT_STATE for 0, 1, or -1.
					If = -1		The transaction is uncommittable and should be rolled back.
					If = 1		The transaction is committable.
					If = 0		There is no active user transaction for the current request.
			*/

			DECLARE	@error_number INT = ERROR_NUMBER(), @error_message NVARCHAR(MAX) = ERROR_MESSAGE();
			IF ( XACT_STATE() ) = -1
				ROLLBACK TRANSACTION;

			IF ( XACT_STATE() ) = 1
			BEGIN
				
				-- OptionA:	Rollback transaction & send email if error happens during processing. 
				--			Message will stay in incoming queue. If rollback happens 5 times in a row the incoming queue will be disabled.
				ROLLBACK TRANSACTION;
				EXEC [SB].[SendEmailWithServiceBrokerMessageDetails]
					'Message cannot be processed',
					@service_name,
					@service_contract_name,
					@message_type_name,
					@message_body,
					@error_number,
					@error_message;
				
				/*
				-- OptionB:	Notify sender that message cannot be processed.
				--			Conversation will be closed and sender should decide what to do with message (send again or nothing).
				END CONVERSATION @conversation_handle WITH ERROR = @error_number DESCRIPTION = @error_message;
				COMMIT;
				*/
			END;

			BREAK;
		END CATCH;
	END;
END;


GO
CREATE QUEUE [SB].[IncomingQueue_<#= obj #>]
	WITH STATUS = ON,
		ACTIVATION (
			STATUS = ON,
			PROCEDURE_NAME = [SB].[ReadIncomingQueue_<#= obj #>],
			MAX_QUEUE_READERS = 1,
			EXECUTE AS N'dbo'
			),
		POISON_MESSAGE_HANDLING(STATUS = ON);


GO
CREATE SERVICE [//<#= obj.Replace("_", "/") #>_IncomingService] AUTHORIZATION [dbo] 
ON QUEUE [SB].[IncomingQueue_<#= obj #>] ([//<#= obj.Replace("_", "/") #>_Contract]);


GO
GRANT SEND ON SERVICE::[//<#= obj.Replace("_", "/") #>_IncomingService] TO [public];


<#
	}
#>

<#+
	public List<String> GetObjects()
	{
		List<String> objects = new List<String>();

        // Put message name here 
        // examlpe: SRC_DEST_MSGTYPE
		objects.Add("Source_Destination_MessageType");
		objects.Add("Source_Destination_MessageType");

	    return objects;
	}
#>