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

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

CREATE ENDPOINT Central
AS TCP (LISTENER_PORT = 4030)
FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS)

ALTER ENDPOINT Central STATE = STARTED

GRANT CONNECT ON ENDPOINT::Central
TO [DANAL-BASE\SQLID2]

use Company

GRANT SEND ON Service::CentralMaintenanceService
TO PUBLIC

CREATE ROUTE Inspection
WITH SERVICE_NAME = 'InspectionService',
ADDRESS = N'TCP://192.168.226.138:4031'


use msdb
SELECT service_broker_guid FROM sys.databases WHERE name='Company'

CREATE ROUTE Central
WITH SERVICE_NAME = 'CentralMaintenanceService',
ADDRESS = N'LOCAL',
BROKER_INSTANCE = '1A245923-86D3-4B52-BCAD-0713E0C4F7FC'
use company
 
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 CentralMaintenanceQueue

CREATE SERVICE CentralMaintenanceService
ON QUEUE CentralMaintenanceQueue
([DEFAULT], Maintenance)


SELECT * from CentralMaintenanceQueue

select * from sysusers

GRANT SEND ON SERVICE::CentralMaintenanceService
TO PUBLIC

select * from centralmaintenancequeue
select * from sys.transmission_queue
select cast(message_body as xml) from CentralMaintenanceQueue



BEGIN TRAN
DECLARE @message XML;
DECLARE @conv UNIQUEIDENTIFIER;
DECLARE @ts VARCHAR(MAX);
SET @ts = CAST(GETDATE() AS VARCHAR(MAX));
RECEIVE TOP (1) 
	@message = CAST(message_body AS XML),
	@conv = conversation_handle
	FROM CentralMaintenanceQueue;
IF @@ROWCOUNT = 1
BEGIN
	SELECT @message
	SET @message.modify('insert (
	attribute timestamp{sql:variable(''@ts'')}
	) into (/*[1])');
	SEND ON CONVERSATION @conv
	Message Type WorkInProcess
	(@message)
END
COMMIT TRAN


SELECT @@trancount
