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