Use master Alter Database Automart Set Enable_Broker Use Automart --create reply and request message types Create Message Type [//AWDB/1DBSample/RequestMessage] Validation = Well_Formed_xml GO Create Message Type [//AWDB/1DBSample/ReplyMessage] Validation=well_formed_xml Go --create a contract with the message types Create Contract [//AWDB/1DBSample/SampleContract] ([//AWDB/1DBSample/RequestMessage] Sent by initiator, [//AWDB/1DBSample/ReplyMessage] sent by Target) Go --create queue for messages Create Queue TargetQueue1Db --create a service for target and initiator Create service [//AWDB/DBSample/TargetService] On Queue TargetQueue1Db ([//AWDB/1DBSample/SampleContract]) Create Queue InitiatorQueue1DB Create Service [//AWDB/1DBSample/InitiatorService] On Queue InitiatorQueue1DB Declare @initDigHandle UniqueIdentifier Declare @requestMessage nvarchar(100); Begin Transaction Begin Dialog @initDigHandle From Service [//AWDB/1DBSample/InitiatorService] To Service N'//AWDB/DBSample/TargetService' on Contract [//AWDB/1DBSample/SampleContract] With Encryption=off Select @requestMessage=N'Message for Target Service '; Send on Conversation @initDigHandle Message type [//AWDB/1DBSample/RequestMessage] (@RequestMessage) Select @requestMessage As SentRequestMsg; Commit Transaction Declare @RecvReqDigHandle uniqueidentifier Declare @RecvReqMsg Nvarchar(100) Declare @recvReqMsgName sysname Begin Tran Waitfor ( Receive top(1) @RecvReqDigHandle=conversation_handle, @RecvReqMsg=message_body, @recvReqMsgName=message_type_name From TargetQueue1DB ), TimeOut 1000 Select @RecvReqMsg As RecievedRequestMsg if @recvReqMsgName=N'//AWDB/1DBSample/RequestMessage' Begin Declare @replyMsg NVarchar(100) Select @replyMsg=N'Message for initiator service '; Send on Conversation @RecvReqDigHandle Message type [//AWDB/1DBSample/ReplyMessage] (@replymsg) End Conversation @RecvReqDigHandle End Select @replyMsg as SentReplyMsg Commit transaction GO Declare @RecvReqDigHandle uniqueidentifier Declare @RecvReqMsg Nvarchar(100) Begin Transaction Waitfor ( Receive top(1) @RecvReqDigHandle=conversation_handle, @RecvReqMsg=message_body From InitiatorQueue1DB ), Timeout 1000 End Conversation @RecvReqDigHandle Select @RecvReqMsg as RecievedReplyMsg Commit Transaction use VenueTracker exec sp_help Use Automart --there are over 200 of these in sql server Select name from sys.Tables Select * from sys.Databases Select * from sys.Tables Select * from sys.all_columns where object_id=357576312 and Is_nullable=1 Select * from sys.backup_devices Select * from sys.database_files Select * from sys.xml_schema_collections Select * from sys.conversation
Tuesday, July 31, 2012
Service Broker and system tables
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment