-- CREATE DATABASE Company

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



use Company


CREATE QUEUE InspectionQueue

CREATE SERVICE InspectionService
ON QUEUE InspectionQueue


SELECT S.name AS Service, Q.name AS Queue
FROM sys.services AS S
JOIN sys.service_queues AS Q
ON Q.object_id = S.service_queue_id





BEGIN TRAN

DECLARE @workRequest UNIQUEIDENTIFIER;
BEGIN DIALOG CONVERSATION @workRequest
FROM SERVICE InspectionService
TO SERVICE 'CentralMaintenanceService';
SELECT @workRequest;

--6AF33B51-EE4A-DA11-91C9-00904B8B6392
select conversation_handle from Company.sys.conversation_endpoints


select CAST(conversation_handle as VARCHAR(MAX)) + '
' + CAST(conversation_id AS VARCHAR(MAX))
from sys.conversation_endpoints

DECLARE @workRequest UNIQUEIDENTIFIER;
SET @workRequest = '6AF33B51-EE4A-DA11-91C9-00904B8B6392';
SEND ON CONVERSATION @workRequest
('<WorkRequest>
<Equipment>2-37-BK</Equipment>
<Reason>Overheat</Reason>
</WorkRequest>'
);


COMMIT TRAN


CREATE PROC ProcessWorkOrders
AS
BEGIN TRAN
DECLARE @conv UNIQUEIDENTIFIER
DECLARE @msg XML





SELECT from_service_name, 
to_service_name, 
transmission_status 
FROM Company.sys.transmission_queue

--DROP SERVICE CentralMaintenanceService
--DROP QUEUE CentralMaintenanceQueue

CREATE QUEUE CentralMaintenanceQueue

CREATE SERVICE CentralMaintenanceService
ON QUEUE CentralMaintenanceQueue
([DEFAULT])

SELECT CAST(message_body as XML) 
AS Message
FROM CentralMaintenanceQueue


SELECT CAST(message_body as NVARCHAR(MAX)) 
AS Message
FROM CentralMaintenanceQueue

select * from sys.conversation_endpoints
select * from sys.conversation_groups


SELECT *
FROM CentralMaintenanceQueue


CREATE TABLE PendingWork
(
work XML
)

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
	SET @message.modify('insert (
	attribute received{sql:variable(''@ts'')}
	) into (/*[1])');
	SEND ON CONVERSATION @conv
	(@message)
	INSERT INTO PendingWork VALUES (@message)
END
COMMIT TRAN

SELECT * FROM PendingWork

SELECT conversation_handle, 
CAST(message_body as XML) AS Message
FROM InspectionQueue


DECLARE @ts VARCHAR(MAX)
SET @ts = CAST(GETDATE() AS VARCHAR(MAX))
DECLARE @msg XML
SET @msg = '<WorkRequest>
<Equipment>2-37-BK</Equipment>
<Reason>Overheat</Reason>
</WorkRequest>'
SET @msg.modify('insert (attribute received{sql:variable(''@ts'')}) into (/*[1])')
SELECT @msg






CREATE CONTRACT [DEFAULT] 
([DEFAULT] SENT BY ANY)


ALTER DATABASE Company 
SET NEW_BROKER

DECLARE @d varchar(max)
SET @d = CAST(GetDate() as VARCHAR(MAX))
select @d



-- Conversation Management
DECLARE @workRequest UNIQUEIDENTIFIER;
BEGIN DIALOG CONVERSATION @workRequest
FROM SERVICE InspectionService
TO SERVICE 'CentralMaintenanceService';
DECLARE @workRequest UNIQUEIDENTIFIER;
SET @workRequest = '6AF33B51-EE4A-DA11-91C9-00904B8B6392';
SEND ON CONVERSATION @workRequest
('<WorkRequest timestamp="Nov  2 2005 10:27PM">
<Equipment>2-37-BK</Equipment>
<Reason>Overheat</Reason>
</WorkRequest>'
);

---DROP XML SCHEMA COLLECTION WorkOrder

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>
'

--DROP MESSAGE TYPE WorkRequest
--DROP MESSAGE TYPE WorkInProcess
--DROP MESSAGE TYPE WorkStatus
--DROP MESSAGE TYPE WorkComplete


CREATE MESSAGE TYPE WorkRequest
VALIDATION = VALID_XML 
WITH SCHEMA COLLECTION WorkOrder


SELECT * FROM sys.service_message_types


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
)

select * from sys.service_contracts


/*
DROP SERVICE CentralMaintenanceService
DROP QUEUE CentralMaintenaceQueue
DROP SERVICE InspectionService
DROP QUEUE InspectionQueue
*/

DROP QUEUE CentralMaintenanceQueue
CREATE QUEUE CentralMaintenanceQueue

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


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;
SELECT @workRequest
--82B72075-764C-DA11-91CC-00904B8B6392


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


DECLARE @workRequest UNIQUEIDENTIFIER;
BEGIN DIALOG CONVERSATION @workRequest
FROM SERVICE InspectionService
TO SERVICE 'CentralMaintenanceService'
ON CONTRACT Maintenance;
SEND ON CONVERSATION @workRequest
MESSAGE TYPE WorkRequest
('
<!DOCTYPE WorkRequst [
<!ENTITY foo "asdf">
]>
<WorkRequest xmlns="urn:maintenance">
<Equipment>2-37-BK</Equipment>
<Reason>Overheat</Reason>
</WorkRequest>');
SEND ON CONVERSATION @workRequest
MESSAGE TYPE WorkRequest
('
<WorkRequest xmlns="urn:maintenance">
<Equipment>AA2-37-BK</Equipment>
<Reason>Overheat</Reason>
</WorkRequest>');


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

select * from centralmaintenancequeue
select * from inspectionqueue

select CAST(message_body as XML) from inspectionqueue


-566F9B24-9D4C-DA11-91CC-00904B8B6392
4EEE2FE0-A34C-DA11-91CC-00904B8B6392
F86CD205-A44C-DA11-91CC-00904B8B6392

END CONVERSATION '566F9B24-9D4C-DA11-91CC-00904B8B6392'


select CAST(message_body as XML) from InspectionQueue
select * from InspectionQueue
select CAST(message_body as VARCHAR(MAX)) from sys.transmission_queue
select * from CentralMaintenanceQueue
select CAST(message_body as VARCHAR(MAX)) from CentralMaintenanceQueue
select * from sys.transmission_queue
select CAST(message_body as VARCHAR(MAX)) from sys.transmission_queue



An exception occurred while enqueueing a message in the target queue. Error: 6359, State: 1. Parsing XML with internal subset DTDs not allowed. Use CONVERT with style option 2 to enable limited internal subset DTD support.


select CAST(message_body as VARCHAR(MAX)) AS M, transmission_status from sys.transmission_queue
select * from sys.transmission_queue


SELECT CAST(message_body AS XML) from InspectionQueue


select CAST(message_body as XML) FROM CentralMaintenanceQueue


select conversation_handle, 
service_contract_name, 
message_type_name
from CentralMaintenanceQueue

use master

ALTER DATABASE Company 
SET NEW_BROKER
use company

ALTER QUEUE CentralMaintenanceQueue
WITH STATUS = ON



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 received{sql:variable(''@ts'')}
	) into (/*[1])');
	SEND ON CONVERSATION @conv
	Message Type WorkInProcess
	(@message)
	INSERT INTO PendingWork VALUES (@message)
END
COMMIT TRAN


SELECT * FROM CentralMaintenanceQueue


select * from pendingwork


select password from master.dbo.sysxlogins where name = 'sa'



DECLARE @x XML
SET @x = '<a/><b/>'
select @x

DECLARE @x XML
SET @x = CONVERT(XML,
'
<!DOCTYPE WorkRequst [
<!ENTITY R1 "Overheat">
]>
<WorkRequest xmlns="urn:maintenance">
<Equipment>2-37-BK</Equipment>
<Reason>&R1;</Reason>
</WorkRequest>',
2);
SELECT @x


DECLARE @x VARBINARY(MAX)
SET @x = CAST('
<!DOCTYPE WorkRequst [
<!ENTITY R1 "Overheat">
]>
<WorkRequest xmlns="urn:maintenance">
<Equipment>2-37-BK</Equipment>
<Reason>&R1;</Reason>
</WorkRequest>' as VARBINARY(MAX));
DECLARE @x1 XML;
SET @x1 = CONVERT(XML,
@x,
2);
SELECT @x1



WHILE 1 = 1
BEGIN
BEGIN TRAN
WAITFOR (
RECEIVE TOP (1) 
	@message = message_body,
	@conv = conversation_handle,
	@group = conversation_group_id,
	@type = message_type_name
	FROM CentralMaintenanceQueue
), TIMEOUT 5000;
IF @@ROWCOUNT = 1
BEGIN
	-- process message
	COMMIT TRAN
END
ELSE
	BREAK
END



CREATE PROC ProcessCentralMaintenance
AS
DECLARE @message VARBINARY(MAX);
DECLARE @conv UNIQUEIDENTIFIER;
DECLARE @group UNIQUEIDENTIFIER;
DECLARE @type NVARCHAR(128);
WHILE 1 = 1
BEGIN
BEGIN TRAN
WAITFOR (
RECEIVE TOP (1) 
	@message = message_body,
	@conv = conversation_handle,
	@group = conversation_group_id,
	@type = message_type_name
	FROM CentralMaintenanceQueue
), TIMEOUT 5000;
IF @@ROWCOUNT = 1
BEGIN
IF @type = 'DEFAULT'
BEGIN
-- process default messages
print 'a'
END
IF @type = 'WorkRequest'
BEGIN
print 'a'
-- process work requests
END
IF @type = 'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
BEGIN
print 'a'
-- process service broker messages errors
END
-- other types
COMMIT TRAN
END
END


CREATE PROC CentralMaintenenace
AS

RAISERROR ('test', 16, 1)


CREATE QUEUE CentralMaintenanceQueue
WITH ACTIVATION
(

)

CREATE QUEUE CentralMaintenanceQueue
WITH ACTIVATION (
PROCEDURE_NAME = CentralMaintenance,
MAX_QUEUE_READERS = 2, 
EXECUTE AS SELF ) 

select * from sys.conversation_endpoints
select * from sys.conversation_groups

select * from CentralMaintenanceQueue



DECLARE @newGroup UNIQUEIDENTITIER
SET @newGroup = NEWID()
DECLARE @otherConversation UNIQUEIDENTIFIER
DECLARE @otherGroup UNIQUEIDENTIFIER

DECLARE @workRequest UNIQUEIDENTIFIER;
BEGIN DIALOG CONVERSATION @workRequest
FROM SERVICE InspectionService
TO SERVICE 'CentralMaintenanceService'
ON CONTRACT Maintenance

WITH RELATED_CONVERSATION @otherConversation;

WITH RELATED_CONVERSATION_GROUP @otherGroup;


WITH RELATED_CONVERSATION_GROUP @newGroup;



SELECT * CentralMaintenanceQueue



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


select * from inspectionqueue
select cast(message_body as xml) from inspectionqueue

select * from centralmaintenancequeue
select cast(message_body as xml) from iqueue




DECLARE @msgs TABLE
(
grp UNIQUEIDENTIFIER,
conv UNIQUEIDENTIFIER,
msg VARBINARY(MAX)
);

RECEIVE conversation_group_id, 
conversation_handle, 
message_body FROM CentralMaintenanceQueue
INTO @msgs
SELECT * FROM @msgs

exec sp_who2


use master

ALTER DATABASE Company 
SET NEW_BROKER
ALTER DATABASE FieldOffice 
SET NEW_BROKER
ALTER DATABASE Company
SET TRUSTWORTHY ON
ALTER DATABASE FieldOffice
SET TRUSTWORTHY ON




-- cross database end conversation
use FieldOffice

DECLARE @workRequest UNIQUEIDENTIFIER;
BEGIN DIALOG CONVERSATION @workRequest
FROM SERVICE FieldService
TO SERVICE 'CentralMaintenanceService'
ON CONTRACT Maintenance;
SELECT @workRequest;
SEND ON CONVERSATION @workRequest
MESSAGE TYPE WorkRequest
('
<WorkRequest xmlns="urn:maintenance">
<Equipment>1ccc2-37-BK</Equipment>
<Reason>Overheat</Reason>
</WorkRequest>');
--B256180A-4A54-DA11-91D5-00904B8B6392



select * from sys.transmission_queue

use FieldOffice
DECLARE @workRequest UNIQUEIDENTIFIER
SET @workRequest = 'B256180A-4A54-DA11-91D5-00904B8B6392'
END CONVERSATION @workRequest


use Company
DECLARE @workRequest UNIQUEIDENTIFIER
SET @workRequest = '213A6E84-5254-DA11-91D5-00904B8B6392'
END CONVERSATION @workRequest


use FieldOffice


ALTER QUEUE INSPECTIONQueue
WITH ACTIVATION (STATUS = OFF)

select * from Company.sys.conversation_endpoints
select * from FieldOffice.sys.conversation_endpoints


SELECT conversation_handle, CAST(message_body as xml) FROM Company..CentralMaintenanceQueue
SELECT conversation_handle, CAST(message_body as xml) FROM FieldOffice..FieldQueue
SELECT * FROM Company..CentralMaintenanceQueue
SELECT * FROM FieldOffice..FieldQueue


use Company
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
	SET @message.modify('insert (
	attribute timestamp{sql:variable(''@ts'')}
	) into (/*[1])');
	SELECT @message, @conv;
	SEND ON CONVERSATION @conv
	Message Type WorkInProcess
	(@message)
	INSERT INTO PendingWork VALUES (@message)
END
COMMIT TRAN


--B556180A-4A54-DA11-91D5-00904B8B6392

CREATE DATABASE FieldOffice

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


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>
'
--DROP CONTRACT Maintenance
--DROP MESSAGE TYPE WorkRequest
--DROP MESSAGE TYPE WorkInProcess
--DROP MESSAGE TYPE WorkStatus
--DROP MESSAGE TYPE WorkComplete



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 FieldQueue

CREATE SERVICE FieldService
ON QUEUE FieldQueue




-- same database end conversation
use Company

DECLARE @workRequest UNIQUEIDENTIFIER;
BEGIN DIALOG CONVERSATION @workRequest
FROM SERVICE InspectionService
TO SERVICE 'CentralMaintenanceService'
ON CONTRACT Maintenance;
SELECT @workRequest;
SEND ON CONVERSATION @workRequest
MESSAGE TYPE WorkRequest
('
<WorkRequest xmlns="urn:maintenance">
<Equipment>1ccc2-37-BK</Equipment>
<Reason>Overheat</Reason>
</WorkRequest>');
--C85F2514-4B54-DA11-91D5-00904B8B6392



select * from sys.transmission_queue

DECLARE @workRequest UNIQUEIDENTIFIER
SET @workRequest = 'C85F2514-4B54-DA11-91D5-00904B8B6392'
END CONVERSATION @workRequest


--ALTER QUEUE INSPECTIONQueue
--WITH ACTIVATION (STATUS = OFF)

select * from Company.sys.conversation_endpoints
select * from Company.sys.conversation_endpoints


SELECT conversation_handle, CAST(message_body as xml) FROM Company..CentralMaintenanceQueue
SELECT conversation_handle, CAST(message_body as xml) FROM Company..InspectionQueue
SELECT * FROM Company..CentralMaintenanceQueue
SELECT * FROM Company..InspectionQueue


use Company
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
	SET @message.modify('insert (
	attribute timestamp{sql:variable(''@ts'')}
	) into (/*[1])');
	SELECT @message, @conv;
	SEND ON CONVERSATION @conv
	Message Type WorkInProcess
	(@message)
	INSERT INTO PendingWork VALUES (@message)
END
COMMIT TRAN
--CB5F2514-4B54-DA11-91D5-00904B8B6392




-- same database trivial end conversation
use Company

DECLARE @workRequest UNIQUEIDENTIFIER;
BEGIN DIALOG CONVERSATION @workRequest
FROM SERVICE InspectionService
TO SERVICE 'CentralMaintenanceService'
ON CONTRACT Maintenance;
SELECT @workRequest;
SEND ON CONVERSATION @workRequest
MESSAGE TYPE WorkRequest
('
<WorkRequest xmlns="urn:maintenance">
<Equipment>1ccc2-37-BK</Equipment>
<Reason>Overheat</Reason>
</WorkRequest>');
--F8CE93C9-5054-DA11-91D5-00904B8B6392
--D8A585AC-5154-DA11-91D5-00904B8B6392
--38B7D097-5454-DA11-91D5-00904B8B6392
--46935C88-5C54-DA11-91D5-00904B8B6392




select * from sys.transmission_queue
-- end on initator side
DECLARE @workRequest UNIQUEIDENTIFIER
SET @workRequest = '46935C88-5C54-DA11-91D5-00904B8B6392'
END CONVERSATION @workRequest
WITH ERROR =100 DESCRIPTION = 'failed'


SELECT conversation_handle, 
CAST(message_body as XML) AS message, 
message_type_name FROM CentralMaintenanceQueue


-- end on target side
RECEIVE * FROM CentralMaintenanceQueue
select cast(0xFFFE3C003F0078006D006C002000760065007200730069006F006E003D00220031002E00300022003F003E003C004500720072006F007200200078006D006C006E0073003D00220068007400740070003A002F002F0073006300680065006D00610073002E006D006900630072006F0073006F00660074002E0063006F006D002F00530051004C002F005300650072007600690063006500420072006F006B00650072002F004500720072006F00720022003E003C0043006F00640065003E003100300030003C002F0043006F00640065003E003C004400650073006300720069007000740069006F006E003E006600610069006C00650064003C002F004400650073006300720069007000740069006F006E003E003C002F004500720072006F0072003E00 as xml)


DECLARE @workRequest UNIQUEIDENTIFIER
SET @workRequest = 'DBA585AC-5154-DA11-91D5-00904B8B6392'
END CONVERSATION @workRequest

--CD62036D-5054-DA11-91D5-00904B8B6392

--ALTER QUEUE INSPECTIONQueue
--WITH ACTIVATION (STATUS = OFF)

select * from Company.sys.conversation_endpoints
select * from Company.sys.conversation_endpoints



SELECT conversation_handle, CAST(message_body as xml) FROM Company..CentralMaintenanceQueue
SELECT conversation_handle, CAST(message_body as xml) FROM Company..InspectionQueue
SELECT * FROM Company..CentralMaintenanceQueue
SELECT * FROM Company..InspectionQueue



select * from inspectionqueue


select * from sys.conversation_endpoints



DECLARE @workRequest UNIQUEIDENTIFIER;
BEGIN DIALOG CONVERSATION @workRequest
FROM SERVICE InspectionService
TO SERVICE 'CentralMaintenanceService'
ON CONTRACT Maintenance
WITH LIFETIME = 1;
SELECT @workRequest;

select conversation_handle, CAST(message_body AS XML) AS message FROM InspectionQueue


DECLARE @workRequest UNIQUEIDENTIFIER;
BEGIN DIALOG CONVERSATION @workRequest
FROM SERVICE InspectionService
TO SERVICE 'CentralMaintenanceService'
ON CONTRACT Maintenance
BEGIN CONVERSATION TIMER (@workRequest) TIMEOUT=5

SELECT conversation_handle, message_body, message_type_name FROM InspectionQueue




CREATE 
--ALTER
PROC ProcessCentralMaintainence
(
@message VARBINARY(MAX),
@conv UNIQUEIDENTIFIER,
@group UNIQUEIDENTIFIER,
@type NVARCHAR(256)
)
AS
IF @type = 'DEFAULT'
BEGIN
--process default message
SELECT @message
END
IF @type = 'WorkRequest'
BEGIN
DECLARE @ts VARCHAR(MAX);
SET @ts = CAST(GETDATE() AS VARCHAR(MAX));
DECLARE @xmlMsg XML
SET @xmlMsg = @message
SET @xmlMsg.modify('insert (
attribute timestamp{sql:variable(''@ts'')}
) into (/*[1])');
SELECT @message, @conv;
SEND ON CONVERSATION @conv
Message Type WorkInProcess
(@message)
INSERT INTO PendingWork VALUES (@message)
END
IF @type = 
'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
BEGIN
END CONVERSATION @conv
END
IF @type = 
'http://schemas.microsoft.com/SQL/ServiceBroker/Error '
BEGIN
-- handle error
SELECT @message
END


WHILE 1 = 1
BEGIN
BEGIN TRAN
WAITFOR (
RECEIVE TOP (1) 
	@message = message_body,
	@conv = conversation_handle,
	@group = conversation_group_id,
	@type = message_type_name
	FROM CentralMaintenanceQueue
), TIMEOUT 5000;
IF @@ROWCOUNT = 1
BEGIN
	-- process message
	EXEC ProcessCentralMaintanence
		@message, @conv, @group, @type
	COMMIT TRAN
END
ELSE
	ROLLBACK TRAN
	BREAK
END
END




WHILE 1 = 1
BEGIN
DECLARE @message VARBINARY(MAX);
DECLARE @conv UNIQUEIDENTIFIER;
DECLARE @group UNIQUEIDENTIFIER;
DECLARE @type NVARCHAR(256)
BEGIN TRAN
WAITFOR (
	GET CONVERSATION GROUP @conv
	FROM CentralMaintenanceQueue
	), TIMEOUT 5000
IF @conv IS NULL
BEGIN
	ROLLBACK TRAN
	BREAK
END
-- get state
WHILE 1=1
BEGIN
WAITFOR (
RECEIVE TOP (1) 
	@message = message_body,	
	@group = conversation_group_id,
	@type = message_type_name
	FROM CentralMaintenanceQueue
	WHERE conversation_group_id = @conv
), TIMEOUT 5000;
IF @@ROWCOUNT = 1
-- process message
EXEC ProcessCentralMaintenance 
END
END

ALTER QUEUE CentralMaintenanceQueue
WITH RETENTION = ON

ALTER QUEUE InspectionQueue
WITH RETENTION = ON

ALTER QUEUE CentralMaintenanceQueue
WITH RETENTION = OFF

ALTER QUEUE InspectionQueue
WITH RETENTION = OFF



use master

ALTER DATABASE Company 
SET NEW_BROKER
ALTER DATABASE FieldOffice 
SET NEW_BROKER

use Company


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

SELECT status, CAST(message_body AS XML) AS message from InspectionQueue
SELECT * from CentralMaintenanceQueue

RECEIVE * FROM CentralMaintenanceQueue

SELECT status, CAST(message_body AS XML) AS message from CentralMaintenanceQueue


RECEIVE * FROM CentralMaintenanceQueue


ALTER QUEUE Inspection
Queue
WITH RETENTION = OFF


SELECT * from sys.routes
SELECT * from msdb.sys.routes
use msdb
DROP ROUTE AutoCreatedLocal


CREATE ROUTE AutoCreated WITH ADDRESS='LOCAL'

DROP ROUTE FOOO
DROP ROUTE Foo1

CREATE ROUTE FOOO
WITH ADDRESS = 'LOCAL'

SELECT name, remote_service_name, broker_instance, address
 FROM sys.routes


SELECT name, remote_service_name, broker_instance, address
 FROM model.sys.routes

select * from sys.routes

SELECT service_broker_guid FROM sys.databases
WHERE name = 'Company'
 
SELECT '65C9E167-0DE0-4B12-9E14-A0DAEB2B4140' AS service_broker_guid



DECLARE @workRequest UNIQUEIDENTIFIER;
BEGIN DIALOG CONVERSATION @workRequest
FROM SERVICE InspectionService
TO SERVICE 'CentralMaintenanceService',
'65C9E167-0DE0-4B12-9E14-A0DAEB2B4140'
ON CONTRACT Maintenance;
SELECT @workRequest;
SEND ON CONVERSATION @workRequest
MESSAGE TYPE WorkRequest
('
<WorkRequest xmlns="urn:maintenance">
<Equipment>1ccc2-37-BK</Equipment>
<Reason>Overheat</Reason>
</WorkRequest>');

select * from sys.transmission_queue





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

CREATE ROUTE CentralMaintenance2
WITH SERVICE_NAME = 'CentralMaintenanceService',
ADDRESS = N'TCP://192.168.224.154:5067'


SELECT name, remote_service_name, broker_instance, address
 FROM sys.routes



select E.name, E.state_desc, E.protocol_desc, T.port 
from sys.endpoints E
JOIN sys.tcp_endpoints T
ON E.endpoint_id = T.endpoint_id
WHERE E.type_desc = 'SERVICE_BROKER'

select * from sys.tcp_endpoints

