--------------------------------------------------------------------
-- Script for fast data push sample.
--
-- This file is part of the Microsoft SQL Server Code Samples.
-- Copyright (C) Microsoft Corporation. All Rights reserved.
-- This source code is intended only as a supplement to Microsoft
-- Development Tools and/or on-line documentation. See these other
-- materials for detailed information regarding Microsoft code samples.
--
-- THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF
-- ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO
-- THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
-- PARTICULAR PURPOSE.
--------------------------------------------------------------------

----------------------------------------------------
-- Target setup for fast data push.
-- Before running, customize the configuration-dependent
-- routing to the initiator service.
----------------------------------------------------

--------------------------------------------------------------------
-- Poison message processing:
--
-- Although this should not occur here because messages are not
-- processed in any significant way, there are times that
-- an application might find itself temporarily unable to process
-- a message. The temptation is then to roll back the receive
-- transaction and try again. The danger of doing this is that 
-- 5 consecutive roll backs on a queue will disable it.
-- If a queue does become disabled, possibly due to a doomed
-- transaction, a BROKER_QUEUE_DISABLED event notification can
-- be used as a recovery mechanism. You can also use the TRY-CATCH
-- construct to process transaction errors as shown below.
--------------------------------------------------------------------

USE data_push_database;
GO

-- Activated store proc for the target to receive messages.
CREATE PROCEDURE target_queue_activated_procedure
AS
BEGIN
    SET NOCOUNT ON;
     
    -- Variable table for received messages.
    DECLARE @receive_table TABLE(
            queuing_order BIGINT,
            conversation_handle UNIQUEIDENTIFIER,
            message_type_name SYSNAME,
            message_body VARCHAR(MAX));
    
    -- Cursor for received message table.
    DECLARE message_cursor CURSOR LOCAL FORWARD_ONLY READ_ONLY
            FOR SELECT
            conversation_handle,
            message_type_name,
            message_body
            FROM @receive_table ORDER BY queuing_order;

    DECLARE @conversation_handle UNIQUEIDENTIFIER,
           @message_type_name SYSNAME,
           @message_body VARCHAR(MAX);
    
    -- Count processed messages.
    DECLARE @message_counter BIGINT;
    SET @message_counter = 0;
    
    -- Error variables.
    DECLARE @error_number INT;
    DECLARE @error_message VARCHAR(4000);
    DECLARE @error_severity INT;
    DECLARE @error_state INT;
    DECLARE @error_procedure SYSNAME;
    DECLARE @error_line INT;

    -- Get target parameters.
    DECLARE @message_processing_time CHAR(12);
    SET @message_processing_time = (SELECT message_processing_time FROM data_push_parameters);
    DECLARE @max_messages_per_receive BIGINT;
    SET @max_messages_per_receive = (SELECT max_messages_per_receive FROM data_push_parameters);

    -- Receive messages for available conversation groups.
    BEGIN TRY
      WHILE (1=1)
      BEGIN
         BEGIN TRANSACTION;
    
         -- Receive max available messages into the table.
         -- Wait 5 seconds for messages.
         WAITFOR (
    		RECEIVE TOP(@max_messages_per_receive)
    		   queuing_order,		   
    		   conversation_handle,
    		   message_type_name,
    		   message_body
    		FROM target_queue
    		INTO @receive_table
         ), TIMEOUT 5000;

         IF @@ROWCOUNT = 0
         BEGIN
              COMMIT;
              BREAK;
         END

         -- Process the messages.
         OPEN message_cursor;
         WHILE (1=1)
         BEGIN
              FETCH NEXT FROM message_cursor
    	                INTO @conversation_handle,
    	                     @message_type_name,
    	                     @message_body;
    
              IF (@@FETCH_STATUS != 0) BREAK;

              -- Process a message.
              -- If an exception occurs, catch and attempt to recover.
              BEGIN TRY
                    IF @message_type_name = 'data_push_message'
                    BEGIN
                       -- Process the message for the specified amount of time.
                       WAITFOR DELAY @message_processing_time;
                       SET @message_counter = @message_counter + 1;
                    END
                    ELSE IF @message_type_name = 'end_of_stream'
                    BEGIN
                         -- Initiator is signaling end of message stream: end the dialog.
                         END CONVERSATION @conversation_handle;
                    END
                    ELSE IF @message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
                    BEGIN
                         -- If the message_type_name indicates that the message is an error,
                         -- record the error and end the conversation.
                         WITH XMLNAMESPACES ('http://schemas.microsoft.com/SQL/ServiceBroker/Error' AS ssb)
                         SELECT
                         @error_number = CAST(@message_body AS XML).value('(//ssb:Error/ssb:Code)[1]', 'INT'),
                         @error_message = CAST(@message_body AS XML).value('(//ssb:Error/ssb:Description)[1]', 'VARCHAR(4000)');
                         INSERT INTO target_processing_errors VALUES(@conversation_handle, @error_number,
                                @error_message, NULL, NULL, NULL, NULL, 0);
        	               END CONVERSATION @conversation_handle;
                    END
              END TRY
              BEGIN CATCH
                   SET @error_number = ERROR_NUMBER();
                   SET @error_message = ERROR_MESSAGE();
                   SET @error_severity = ERROR_SEVERITY();
                   SET @error_state = ERROR_STATE();
                   SET @error_procedure = ERROR_PROCEDURE();
                   SET @error_line = ERROR_LINE();
            
                   IF XACT_STATE() = -1
                   BEGIN
                        -- The transaction is doomed. Only rollback possible.
                        -- This could disable the queue if done 5 times consecutively!
                        ROLLBACK TRANSACTION;
            
                        -- Record the error.
                        BEGIN TRANSACTION;
                        INSERT INTO target_processing_errors VALUES(NULL, @error_number, @error_message,
                               @error_severity, @error_state, @error_procedure, @error_line, 1);
                        COMMIT;

                        -- For this level of error, it is best to exit the proc
                        -- and give the queue monitor control.
                        -- Breaking to the outer catch will accomplish this.
                        RAISERROR ('Message processing error', 16, 1);
                   END
                   ELSE IF XACT_STATE() = 1
                   BEGIN
                        -- Record error and continue processing messages.
                        -- Failing message could also be put aside for later processing here.
                        -- Otherwise it will be discarded.
                        INSERT INTO target_processing_errors VALUES(NULL, @error_number, @error_message,
                               @error_severity, @error_state, @error_procedure, @error_line, 0);
                   END
              END CATCH
         END
         CLOSE message_cursor;
         DELETE @receive_table;
         COMMIT;
      END
    END TRY
    BEGIN CATCH
    
       -- Process the error and exit the proc to give the queue monitor control
       SET @error_number = ERROR_NUMBER();
       SET @error_message = ERROR_MESSAGE();
       SET @error_severity = ERROR_SEVERITY();
       SET @error_state = ERROR_STATE();
       SET @error_procedure = ERROR_PROCEDURE();
       SET @error_line = ERROR_LINE();

       IF XACT_STATE() = -1
       BEGIN
            -- The transaction is doomed. Only rollback possible.
            -- This could disable the queue if done 5 times consecutively!
            ROLLBACK TRANSACTION;

            -- Record the error.
            BEGIN TRANSACTION;
            INSERT INTO target_processing_errors VALUES(NULL, @error_number, @error_message,
                   @error_severity, @error_state, @error_procedure, @error_line, 1);
            COMMIT;
       END
       ELSE IF XACT_STATE() = 1
       BEGIN
            -- Record error and commit transaction.
            -- Here you could also save anything else you want before exiting.
            INSERT INTO target_processing_errors VALUES(NULL, @error_number, @error_message,
                   @error_severity, @error_state, @error_procedure, @error_line, 0);
            COMMIT;
       END
    END CATCH

    -- Increment processed message counter.
    BEGIN TRANSACTION;
    DECLARE @counter BIGINT;
    SET @counter = (SELECT TOP(1) counter FROM target_message_counter);
    SET @counter = @counter + @message_counter;
    UPDATE target_message_counter SET counter = @counter;
    COMMIT;
END;
GO

-- Get number of activated target procedures parameter.
DECLARE @number_target_procedures INT;
SET @number_target_procedures = (SELECT number_target_procedures FROM data_push_parameters);

-- Create the target queue with specified number of activated procedures.
DECLARE @query VARCHAR(500);
DECLARE @string VARCHAR(50);
SET @string = (SELECT CAST( @number_target_procedures AS VARCHAR(50)));
SET @query = 'CREATE QUEUE target_queue
                     WITH ACTIVATION (
                     STATUS = ON,
                     MAX_QUEUE_READERS = ' + @string + ',
                     PROCEDURE_NAME = target_queue_activated_procedure,
                     EXECUTE AS OWNER)';
EXEC (@query);
GO

-- Create target service.
CREATE SERVICE target_service ON QUEUE target_queue (data_push_contract);
GO

-- Any user can send on the service.
GRANT SEND ON SERVICE::target_service TO PUBLIC;
GO

-- Table to count processed messages.
IF EXISTS (SELECT name FROM sys.tables WHERE name = 'target_message_counter')
	DROP TABLE message_counter;
GO

CREATE TABLE target_message_counter (counter BIGINT NOT NULL);
GO

INSERT INTO target_message_counter VALUES (0);
GO

-- Table to store processing errors.
IF EXISTS (SELECT name FROM sys.tables WHERE name = 'target_processing_errors')
	DROP TABLE target_processing_errors;
GO

CREATE TABLE target_processing_errors (error_conversation UNIQUEIDENTIFIER, error_number INT,
       error_message VARCHAR(4000), error_severity INT, error_state INT, error_procedure SYSNAME NULL,
       error_line INT, doomed_transaction TINYINT)
GO

---------------------------------------------------------------------
-- Get size of a message queue.
-- Method used is faster than SQL count operator.
---------------------------------------------------------------------
CREATE PROCEDURE usp_get_queue_size ( @queue_name VARCHAR(50) )
AS
BEGIN
    SELECT p.rows
    FROM sys.objects AS o
    JOIN sys.partitions AS p ON p.object_id = o.object_id
    JOIN sys.objects AS q ON o.parent_object_id = q.object_id
    WHERE q.name = @queue_name
    AND p.index_id = 1;
END;
GO

---------------------------------------------------------------------
-- Routing.
-- Skip the following if services are in the same database instance.
---------------------------------------------------------------------

-- Create a route to the initiator service.
CREATE ROUTE initiator_route
	WITH SERVICE_NAME = 'initiator_service',
	ADDRESS = 'tcp://initiator_host:4022';
GO

-- In msdb, create an incoming route to the target service.
USE msdb;
GO

CREATE ROUTE target_route
	WITH SERVICE_NAME = 'target_service',
	ADDRESS = 'local';
GO

USE data_push_database;
GO