/Kilimanjaro_RTM/FastDataPush/Scripts/3 - Target setup.sql
SQL | 310 lines | 191 code | 38 blank | 81 comment | 1 complexity | e51913141f18337df0a6a073c762e6c0 MD5 | raw file
- --------------------------------------------------------------------
- -- 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