use master
CREATE LOGIN [DANAL-BASE\SQLID] FROM WINDOWS

CREATE DATABASE FieldOffice
ALTER DATABASE FieldOffice
SET ENABLE_BROKER
USE FieldOffice
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd' 
use master

CREATE ENDPOINT Inspection
STATE = STARTED
AS TCP (LISTENER_PORT = 4031)
FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS)

GRANT CONNECT ON ENDPOINT::Inspection
TO [DANAL-BASE\SQLID]


use FieldOffice
 GRANT SEND ON Service::InspectionService
TO PUBLIC

CREATE ROUTE CentralMaintenance
WITH SERVICE_NAME = 'CentralMaintenanceService',
ADDRESS = N'TCP://192.168.226.138:4030'

use msdb

SELECT service_broker_guid FROM sys.databases WHERE name='FieldOffice'
CREATE ROUTE Inspection
WITH SERVICE_NAME = 'InspectionService',
ADDRESS = N'LOCAL',
BROKER_INSTANCE = '424D4331-B382-49C7-81A0-D2B82D81350F'

use FieldOffice

CREATE ROUTE CentralMaintenance
WITH SERVICE_NAME = 'CentralMaintenanceService',
ADDRESS = N'TCP://192.168.226.138:4030'

CREATE XML SCHEMA COLLECTION WorkOrder
AS
'<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" 
elementFormDefault="qualified" 
targetNamespace="urn:maintenance"
 xmlns="urn:maintenance">
  <xs:element name="WorkRequest">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="Equipment" type="xs:string"/>
        <xs:element name="Reason" type="xs:string"/>
      </xs:sequence>
	  <xs:attribute name="timestamp" type="xs:string" use="required"/>
    </xs:complexType>
  </xs:element>
</xs:schema>
' 

CREATE MESSAGE TYPE WorkInProcess
VALIDATION = VALID_XML WITH SCHEMA COLLECTION WorkOrder

CREATE MESSAGE TYPE WorkRequest
VALIDATION = WELL_FORMED_XML

CREATE MESSAGE TYPE WorkComplete
VALIDATION  = EMPTY

CREATE CONTRACT Maintenance
(
WorkInProcess SENT BY TARGET,
WorkRequest SENT BY INITIATOR,
WorkComplete SENT BY TARGET
)


CREATE QUEUE InspectionQueue

CREATE SERVICE InspectionService
ON QUEUE InspectionQueue


DECLARE @workRequest UNIQUEIDENTIFIER;
BEGIN DIALOG CONVERSATION @workRequest
FROM SERVICE InspectionService
TO SERVICE 'CentralMaintenanceService'
ON CONTRACT Maintenance
WITH ENCRYPTION = OFF;
SEND ON CONVERSATION @workRequest
MESSAGE TYPE WorkRequest
('
<WorkRequest xmlns="urn:maintenance">
<Equipment>1ccc2-37-BK</Equipment>
<Reason>Overheat</Reason>
</WorkRequest>');
SELECT @workRequest;




SEND ON CONVERSATION @workRequest
MESSAGE TYPE WorkRequest
('
<WorkRequest xmlns="urn:maintenance">
<Equipment>2ccc2-37-BK</Equipment>
<Reason>Overheat</Reason>
</WorkRequest>');


SELECT * from sys.transmission_queue
An error occurred while receiving data: '64(The specified network name is no longer available.)'.

select * from sys.routes


select CAST(message_body as xml) from inspectionqueue

use master
ALTER DATABASE FieldOffice
SET NEW_BROKER
use FieldOffice




select * from msdb.sys.routes
select * from sys.databases

REVOKE all to [DANAL-BASE\sqlid2]

SELECT * from sys.endpoints
