PageRenderTime 63ms CodeModel.GetById 38ms app.highlight 20ms RepoModel.GetById 0ms app.codeStats 0ms

/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