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)