SQL Server Service Broker sample

1. Defining data types, protocols, and services (sending and receiving services)

 UseMaster;GOALTER DATABASETarget DatabaseSETEnable_broker;GO--If the above operation is executed for a long time without reaction, there is a suspicion of freezing, try the following statement. ALTER DATABASETarget DatabaseSETNew_broker with ROLLBACKIMMEDIATE;GOALTER DATABASETarget DatabaseSETEnable_broker;GO

--Create the Sayhellomessage message type.--the message type does not do the processing of data validation.CREATEMESSAGE TYPE sayhellomessage VALIDATION=None;GO--Create a contract sayhellocontract--defined, send/Receive side.--This message type is used.CREATEcontract sayhellocontract (sayhellomessage SENT by  any);GO--Create a Send/Receive queueCREATEQUEUE Sayhellosendqueue;CREATEQUEUE Sayhelloreceivequeue;GO--Create initiator service Sayhellosendservice--The service uses the Sayhellosendqueue queue--because the contract name is not specified, other services cannot use the service as the target service. CREATESERVICE Sayhellosendservice onQUEUE Sayhellosendqueue;GO--Create a target service Sayhelloreceiveservice--The service uses the Sayhelloreceivequeue queue--Using Sayhellocontract ConventionsCREATESERVICE Sayhelloreceiveservice onQUEUE Sayhelloreceivequeue ([sayhellocontract]);GO

2. Send a message

--defines the handle of the send.  DECLARE @InitDlgHandle uniqueidentifier; --defines a variable.  DECLARE @MyMessage NVARCHAR( -); --sets the content of the sending message.  SET @MyMessage =N'Hello world!'  --begins a transaction.  BEGIN TRANSACTION; --defines message sending processing.  BEGINDIALOG@InitDlgHandle     fromSERVICE--define the Send service.Sayhellosendservice toSERVICE--define the receive service.N'Sayhelloreceiveservice'     onContract--define the conventions usedsayhellocontract with  --not encrypted.Encryption= OFF; --send a message.SEND onConversation@InitDlgHandleMESSAGE TYPE[Sayhellomessage]        ( @MyMessage );--outputs the received message.PRINT 'I sent a:' + @MyMessage; --commits the transaction.  COMMIT TRANSACTION;

3. Receiving Messages

--The receive handle.  DECLARE @RecvReqDlgHandle uniqueidentifier; --the data received.  DECLARE @RecvReqMsg NVARCHAR( -); --the name of the data type received.  DECLARE @RecvReqMsgNamesysname; --begins a transaction.  BEGIN TRANSACTION; --attempts to receive messages from the Sayhelloreceivequeue queue.  WAITFOR(RECEIVETOP(1)      @RecvReqDlgHandle =Conversation_handle,@RecvReqMsg       =Message_body,@RecvReqMsgName   =Message_type_name fromsayhelloreceivequeue), TIMEOUT +; --if the received message type is named Sayhellomessage  --then proceed with the processing.  IF @RecvReqMsgName =N'Sayhellomessage'  BEGIN    --defines the message to prepare for return.    DECLARE @ReplyMsg NVARCHAR( -); --simple setup.    SELECT @ReplyMsg = '~' + @RecvReqMsg + '~';--debug output.Declare @t nvarchar(Max)PRINT 'I received:' + @RecvReqMsg + '; I will feedback:' + @ReplyMsg; --send a feedback message.    --SEND on conversation @RecvReqDlgHandle    --MESSAGE TYPE    --[Sayhellomessage]    --(@ReplyMsg);    --END conversation @RecvReqDlgHandle;  END; --commits the transaction.  COMMIT TRANSACTION;

SQL Server Service Broker example (GO)