Service Broker publishing-subscription (publish-subscribe) Framework (2)

Service Broker publishing-subscription (publish-subscribe) Framework (2)

For more information about the publishing-subscription framework, visit the previous article about service broker publishing-subscription (publish-subscribe) Framework (1 ).

Application publisher LogicIn the Stored Procedure sp_publisherservice, implement the entry point of the publisherservice service ). When a new message arrives at the publisherqueue queue, the sp_publisherservice stored procedure is automatically activated and starts to process the message. This stored procedure can process the following message types: [http://ssb.csharp.at/ssb_book/c10/publishmessage#: when the stored procedure starts to publish an article message, it needs to receive this message type from authorservice. This message contains the same topic (subject) as the following article message ). [Http://ssb.csharp.at/ssb_book/c10/subscribemessage#: when the stored procedure is about to subscribe to a topic, it needs to receive the message type from the subscriber service provider. The message contains the topic of the subscriber request. [Http://ssb.csharp.at/ssb_book/c10/articlemessage#: when a stored procedure publishes an article message, it receives this message type from the authorservice. [Http://schemas.microsoft.com/ SQL /servicebroker/enddialog.pdf: When the stored procedure is about to close the session opened by publisherservice, the stored procedure will receive this message type from authorservice or the subscriber service. [Http://schemas.microsoft.com/ SQL /servicebroker/errortypes: if the requested topic does not exist, the stored procedure will receive this message type from publisherservice. The following script shows how the sp_publisherservice stored procedure processes these message types.Sp_publisherserviceService handler:Create procedure success @ conversation uniqueidentifier; declare @ message varbinary (max); declare @ messagetypename sysname; begin transaction; waitfor (receive top (1) @ conversation = conversation_handle, @ message = message_body, @ messagetypename = message_type_namefrom publisherqueue), timeout 1000; while (@ conversation is not null) beginif (@ messagetypename = 'HTTP: // SSB. CSHARP. at/ssb_book/C10/publishmessage ') beginExec sp_processpublicationrequest@ Conversation, @ message; endelse if (@ messagetypename = 'HTTP: // SSB. CSHARP. at/ssb_book/C10/subscribemessage') beginExec sp_processsubscriptionrequest@ Conversation, @ message; endelse if (@ messagetypename = 'HTTP: // SSB. CSHARP. at/ssb_book/C10/articlemessage') beginExec sp_sendonpublication@ Conversation, @ message; endelse if (@ messagetypename in (N 'HTTP: // schemas.microsoft.com/ SQL /servicebroker/error ',N 'HTTP: // schemas.microsoft.com/ SQL /servicebroker/enddialog '))Beginend conversation @ conversation; If (exists (select * from publications where publication = @ conversation) beginExec sp_removepublication@ Conversation; endif (exists (select * from subscribers) beginExec sp_removesubscriber@ Conversation; endendelsebegin -- unexpected messageraiserror (n' encoded unexpected Message Type: % s', 16, 1, @ messagetypename); rollback; return; endcommit; select @ conversation = NULL; begin transaction; waitfor (receive top (1) @ conversation = conversation_handle, @ message = message_body, @ messagetypename = message_type_namefrom publisherqueue), timeout 1000; endcommit; endgo in the above stored procedure, first, receive a new one from the publisherqueue queue. The following message type is [http://ssb.csharp.at/ssb_book/c10/publishmessage#, you can call the sp_processpublicationrequest stored procedure to record the received publishing data to the publications table. If the message type is [http://ssb.csharp.at/ssb_book/c10/subscribemessage], The sp_processsubscriptionrequest storage process is called to record received subscription data to the subscriptions table. Finally, if [consumers distribute the message to all matched subscribers. Use the sp_processpublicationrequest and sp_processsubscriptionrequest stored procedures to manage publishing and subscription records. These two stored procedures call other stored procedures respectively to insert the received messages to the publications or subscriptions table. The following shows the sp_processpublicationrequest stored procedure. Because the sp_processsubscriptionrequest stored procedure is similar, this stored procedure is ignored here.Sp_processpublicationrequestStored Procedure script:Create procedure sp_processpublicationrequest @ conversation uniqueidentifier, @ message varbinary (max) asbegindeclare @ request XML; declare @ subject nvarchar (max); select @ request = cast (@ message as XML ); with xmlnamespaces (default 'HTTP: // SSB. CSHARP. at/ssb_book/C10/publishsubscribe ') Select @ subject = @ request. value (n' (// publish/subject) [1] ', n' nvarchar (max )');If (@ subject is not null)BeginExec sp_publishpublication@ Conversation, @ subject, @ message; endelsebeginend conversation @ conversationwith error = 1 Description = n' the publication is missing a subject '; Exec comment @ conversation; endendgo explain the stored procedure calls sp_publishpublication, and input three parameters: @ conversation, @ subject, and @ message. The following is the sp_publishpublication stored procedure.Sp_publishpublicationStored Procedure script:Create procedure sp_publishpublication @ publication uniqueidentifier, @ subject nvarchar (max), @ originalxml xmlasbegininsertPublications(Publication, subject, originalxml) values (@ publication, @ subject, @ originalxml) the publication column in The endgo publications table and the subscriptions column in the subscriptions table store the session ID, you need these session IDs to send the article message to the subscriber. The last stored procedure is sp_sendonpublication. The stored procedure is called after a message is received from authorservice at [http://ssb.csharp.at/ssb_book/c10/articlemessage.Sp_sendonpublicationStored Procedure script:Create procedure publish @ publication publish, @ Article varbinary (max) Publish @ subscribe publish; declare @ cursorsubscriptions cursor; Set @ cursorsubscriptions = cursor local scroll forselect subscriberfrom subscriptions sjoin publications P on S. subject = P. subjectwhere p. publication = @ publication; begin transaction; open @ cursorsubscriptions; fetch next from @ Cu Rsorsubscriptionsinto @ subscribe; while (@ fetch_status = 0) begin if (@ Article is not null) beginsend on conversation @ subscriptionmessage type [comment (@ Article ); endelsebeginsend on conversation @ subscriptionmessage type [http://ssb.csharp.at/ssb_book/c10/articlemessage##endfetch next from @ cursorsubscriptions into @ sublimit; endclose @ cursorsubscrip Tions; deallocate @ cursorsubscriptions; commit; endgo sp_sendonpublication the stored procedure uses a cusor to send the article message received from the authorservice to the matched subscriber. Send on conversation @ subscriptionmessage type [partition (@ Article); by connecting the subject fields of the publications and subscriptions tables, Match: Select subscriberfrom subscriptions sjoin publications P on S. subject = P. subjectwhere p. publication = @ publication; when the message type is received from [http://schemas.microsoft.com/ SQL /servicebroker/enddialog] (from authorservice or subscriber service provider), the corresponding publishing or subscription data needs to be obtained from publication Delete the S or subscriptions table. Use the sp_removepublication or sp_removesubscriptions stored procedure.Sp_removepublicationStored Procedure script:Create procedure sp_removepublication @ publication uniqueidentifierasbegindelete from publicationswhere publication = @ publicationendgo

Note: This article translates apress into one of the chapters in pro SQL Server 2005 service broker. The subsequent content will be published as soon as possible. You are welcome to share and exchange the content. Thank you!