/Kilimanjaro_RTM/FastDataPush/Scripts/3 - Target setup.sql
SQL | 310 lines | 191 code | 38 blank | 81 comment | 1 complexity | e51913141f18337df0a6a073c762e6c0 MD5 | raw file
1-------------------------------------------------------------------- 2-- Script for fast data push sample. 3-- 4-- This file is part of the Microsoft SQL Server Code Samples. 5-- Copyright (C) Microsoft Corporation. All Rights reserved. 6-- This source code is intended only as a supplement to Microsoft 7-- Development Tools and/or on-line documentation. See these other 8-- materials for detailed information regarding Microsoft code samples. 9-- 10-- THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 11-- ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO 12-- THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A 13-- PARTICULAR PURPOSE. 14-------------------------------------------------------------------- 15 16---------------------------------------------------- 17-- Target setup for fast data push. 18-- Before running, customize the configuration-dependent 19-- routing to the initiator service. 20---------------------------------------------------- 21 22-------------------------------------------------------------------- 23-- Poison message processing: 24-- 25-- Although this should not occur here because messages are not 26-- processed in any significant way, there are times that 27-- an application might find itself temporarily unable to process 28-- a message. The temptation is then to roll back the receive 29-- transaction and try again. The danger of doing this is that 30-- 5 consecutive roll backs on a queue will disable it. 31-- If a queue does become disabled, possibly due to a doomed 32-- transaction, a BROKER_QUEUE_DISABLED event notification can 33-- be used as a recovery mechanism. You can also use the TRY-CATCH 34-- construct to process transaction errors as shown below. 35-------------------------------------------------------------------- 36 37USE data_push_database; 38GO 39 40-- Activated store proc for the target to receive messages. 41CREATE PROCEDURE target_queue_activated_procedure 42AS 43BEGIN 44 SET NOCOUNT ON; 45 46 -- Variable table for received messages. 47 DECLARE @receive_table TABLE( 48 queuing_order BIGINT, 49 conversation_handle UNIQUEIDENTIFIER, 50 message_type_name SYSNAME, 51 message_body VARCHAR(MAX)); 52 53 -- Cursor for received message table. 54 DECLARE message_cursor CURSOR LOCAL FORWARD_ONLY READ_ONLY 55 FOR SELECT 56 conversation_handle, 57 message_type_name, 58 message_body 59 FROM @receive_table ORDER BY queuing_order; 60 61 DECLARE @conversation_handle UNIQUEIDENTIFIER, 62 @message_type_name SYSNAME, 63 @message_body VARCHAR(MAX); 64 65 -- Count processed messages. 66 DECLARE @message_counter BIGINT; 67 SET @message_counter = 0; 68 69 -- Error variables. 70 DECLARE @error_number INT; 71 DECLARE @error_message VARCHAR(4000); 72 DECLARE @error_severity INT; 73 DECLARE @error_state INT; 74 DECLARE @error_procedure SYSNAME; 75 DECLARE @error_line INT; 76 77 -- Get target parameters. 78 DECLARE @message_processing_time CHAR(12); 79 SET @message_processing_time = (SELECT message_processing_time FROM data_push_parameters); 80 DECLARE @max_messages_per_receive BIGINT; 81 SET @max_messages_per_receive = (SELECT max_messages_per_receive FROM data_push_parameters); 82 83 -- Receive messages for available conversation groups. 84 BEGIN TRY 85 WHILE (1=1) 86 BEGIN 87 BEGIN TRANSACTION; 88 89 -- Receive max available messages into the table. 90 -- Wait 5 seconds for messages. 91 WAITFOR ( 92 RECEIVE TOP(@max_messages_per_receive) 93 queuing_order, 94 conversation_handle, 95 message_type_name, 96 message_body 97 FROM target_queue 98 INTO @receive_table 99 ), TIMEOUT 5000; 100 101 IF @@ROWCOUNT = 0 102 BEGIN 103 COMMIT; 104 BREAK; 105 END 106 107 -- Process the messages. 108 OPEN message_cursor; 109 WHILE (1=1) 110 BEGIN 111 FETCH NEXT FROM message_cursor 112 INTO @conversation_handle, 113 @message_type_name, 114 @message_body; 115 116 IF (@@FETCH_STATUS != 0) BREAK; 117 118 -- Process a message. 119 -- If an exception occurs, catch and attempt to recover. 120 BEGIN TRY 121 IF @message_type_name = 'data_push_message' 122 BEGIN 123 -- Process the message for the specified amount of time. 124 WAITFOR DELAY @message_processing_time; 125 SET @message_counter = @message_counter + 1; 126 END 127 ELSE IF @message_type_name = 'end_of_stream' 128 BEGIN 129 -- Initiator is signaling end of message stream: end the dialog. 130 END CONVERSATION @conversation_handle; 131 END 132 ELSE IF @message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/Error' 133 BEGIN 134 -- If the message_type_name indicates that the message is an error, 135 -- record the error and end the conversation. 136 WITH XMLNAMESPACES ('http://schemas.microsoft.com/SQL/ServiceBroker/Error' AS ssb) 137 SELECT 138 @error_number = CAST(@message_body AS XML).value('(//ssb:Error/ssb:Code)[1]', 'INT'), 139 @error_message = CAST(@message_body AS XML).value('(//ssb:Error/ssb:Description)[1]', 'VARCHAR(4000)'); 140 INSERT INTO target_processing_errors VALUES(@conversation_handle, @error_number, 141 @error_message, NULL, NULL, NULL, NULL, 0); 142 END CONVERSATION @conversation_handle; 143 END 144 END TRY 145 BEGIN CATCH 146 SET @error_number = ERROR_NUMBER(); 147 SET @error_message = ERROR_MESSAGE(); 148 SET @error_severity = ERROR_SEVERITY(); 149 SET @error_state = ERROR_STATE(); 150 SET @error_procedure = ERROR_PROCEDURE(); 151 SET @error_line = ERROR_LINE(); 152 153 IF XACT_STATE() = -1 154 BEGIN 155 -- The transaction is doomed. Only rollback possible. 156 -- This could disable the queue if done 5 times consecutively! 157 ROLLBACK TRANSACTION; 158 159 -- Record the error. 160 BEGIN TRANSACTION; 161 INSERT INTO target_processing_errors VALUES(NULL, @error_number, @error_message, 162 @error_severity, @error_state, @error_procedure, @error_line, 1); 163 COMMIT; 164 165 -- For this level of error, it is best to exit the proc 166 -- and give the queue monitor control. 167 -- Breaking to the outer catch will accomplish this. 168 RAISERROR ('Message processing error', 16, 1); 169 END 170 ELSE IF XACT_STATE() = 1 171 BEGIN 172 -- Record error and continue processing messages. 173 -- Failing message could also be put aside for later processing here. 174 -- Otherwise it will be discarded. 175 INSERT INTO target_processing_errors VALUES(NULL, @error_number, @error_message, 176 @error_severity, @error_state, @error_procedure, @error_line, 0); 177 END 178 END CATCH 179 END 180 CLOSE message_cursor; 181 DELETE @receive_table; 182 COMMIT; 183 END 184 END TRY 185 BEGIN CATCH 186 187 -- Process the error and exit the proc to give the queue monitor control 188 SET @error_number = ERROR_NUMBER(); 189 SET @error_message = ERROR_MESSAGE(); 190 SET @error_severity = ERROR_SEVERITY(); 191 SET @error_state = ERROR_STATE(); 192 SET @error_procedure = ERROR_PROCEDURE(); 193 SET @error_line = ERROR_LINE(); 194 195 IF XACT_STATE() = -1 196 BEGIN 197 -- The transaction is doomed. Only rollback possible. 198 -- This could disable the queue if done 5 times consecutively! 199 ROLLBACK TRANSACTION; 200 201 -- Record the error. 202 BEGIN TRANSACTION; 203 INSERT INTO target_processing_errors VALUES(NULL, @error_number, @error_message, 204 @error_severity, @error_state, @error_procedure, @error_line, 1); 205 COMMIT; 206 END 207 ELSE IF XACT_STATE() = 1 208 BEGIN 209 -- Record error and commit transaction. 210 -- Here you could also save anything else you want before exiting. 211 INSERT INTO target_processing_errors VALUES(NULL, @error_number, @error_message, 212 @error_severity, @error_state, @error_procedure, @error_line, 0); 213 COMMIT; 214 END 215 END CATCH 216 217 -- Increment processed message counter. 218 BEGIN TRANSACTION; 219 DECLARE @counter BIGINT; 220 SET @counter = (SELECT TOP(1) counter FROM target_message_counter); 221 SET @counter = @counter + @message_counter; 222 UPDATE target_message_counter SET counter = @counter; 223 COMMIT; 224END; 225GO 226 227-- Get number of activated target procedures parameter. 228DECLARE @number_target_procedures INT; 229SET @number_target_procedures = (SELECT number_target_procedures FROM data_push_parameters); 230 231-- Create the target queue with specified number of activated procedures. 232DECLARE @query VARCHAR(500); 233DECLARE @string VARCHAR(50); 234SET @string = (SELECT CAST( @number_target_procedures AS VARCHAR(50))); 235SET @query = 'CREATE QUEUE target_queue 236 WITH ACTIVATION ( 237 STATUS = ON, 238 MAX_QUEUE_READERS = ' + @string + ', 239 PROCEDURE_NAME = target_queue_activated_procedure, 240 EXECUTE AS OWNER)'; 241EXEC (@query); 242GO 243 244-- Create target service. 245CREATE SERVICE target_service ON QUEUE target_queue (data_push_contract); 246GO 247 248-- Any user can send on the service. 249GRANT SEND ON SERVICE::target_service TO PUBLIC; 250GO 251 252-- Table to count processed messages. 253IF EXISTS (SELECT name FROM sys.tables WHERE name = 'target_message_counter') 254 DROP TABLE message_counter; 255GO 256 257CREATE TABLE target_message_counter (counter BIGINT NOT NULL); 258GO 259 260INSERT INTO target_message_counter VALUES (0); 261GO 262 263-- Table to store processing errors. 264IF EXISTS (SELECT name FROM sys.tables WHERE name = 'target_processing_errors') 265 DROP TABLE target_processing_errors; 266GO 267 268CREATE TABLE target_processing_errors (error_conversation UNIQUEIDENTIFIER, error_number INT, 269 error_message VARCHAR(4000), error_severity INT, error_state INT, error_procedure SYSNAME NULL, 270 error_line INT, doomed_transaction TINYINT) 271GO 272 273--------------------------------------------------------------------- 274-- Get size of a message queue. 275-- Method used is faster than SQL count operator. 276--------------------------------------------------------------------- 277CREATE PROCEDURE usp_get_queue_size ( @queue_name VARCHAR(50) ) 278AS 279BEGIN 280 SELECT p.rows 281 FROM sys.objects AS o 282 JOIN sys.partitions AS p ON p.object_id = o.object_id 283 JOIN sys.objects AS q ON o.parent_object_id = q.object_id 284 WHERE q.name = @queue_name 285 AND p.index_id = 1; 286END; 287GO 288 289--------------------------------------------------------------------- 290-- Routing. 291-- Skip the following if services are in the same database instance. 292--------------------------------------------------------------------- 293 294-- Create a route to the initiator service. 295CREATE ROUTE initiator_route 296 WITH SERVICE_NAME = 'initiator_service', 297 ADDRESS = 'tcp://initiator_host:4022'; 298GO 299 300-- In msdb, create an incoming route to the target service. 301USE msdb; 302GO 303 304CREATE ROUTE target_route 305 WITH SERVICE_NAME = 'target_service', 306 ADDRESS = 'local'; 307GO 308 309USE data_push_database; 310GO