use [Inspection Team]

--DROP SERVICE Inspection
--DROP QUEUE [Inspection Queue]
--ALTER DATABASE [Inspection Team] SET NEW_BROKER
ALTER DATABASE [Inspection Team] SET TRUSTWORTHY ON


CREATE QUEUE [Inspection Queue]

CREATE Service Inspection ON QUEUE [Inspection Queue]

--DROP FUNCTION RepairRequest
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd'

CREATE 
--ALTER
FUNCTION RepairRequest(@item NVARCHAR(MAX), @reason NVARCHAR(MAX))
RETURNS VARBINARY(MAX)
BEGIN
DECLARE @request XML
SET @request =
'<m:Repair xmlns:m="urn:maintance" item="">
<Reason/>
</m:Repair>'
SET @request.modify('
declare namespace m="urn:maintance";
replace value of(/m:Repair[1]/@item) with sql:variable("@item")')
SET @request.modify('
declare namespace m="urn:maintance";
insert text{sql:variable("@reason")} as first into /m:Repair[1]/Reason[1]')
RETURN CAST(@request AS VARBINARY(MAX))
END
 
select dbo.RepairRequest(N'123', N'asdf asdf')

BEGIN TRAN
DECLARE @conv UNIQUEIDENTIFIER;
BEGIN DIALOG CONVERSATION @conv
FROM SERVICE Inspection
TO SERVICE 'Maintenance';
SEND ON CONVERSATION @conv
(dbo.RepairRequest(N'123', N'Running Hot'))
COMMIT TRAN

select * from sys.transmission_queue

select CAST(message_body as XML) from sys.transmission_queue


CREATE QUEUE maintenance_queue
CREATE SERVICE maintenance ON QUEUE maintenance_queue
CREATE SERVICE Maintenance ON QUEUE maintenance_queue

select CAST(message_body as XML) from maintenance_queue

select * from maintenance_queue

select * from [inspection queue]
select * from dbo.ServiceBrokerQueue


select * from sys.routes


CREATE ROUTE [Maintenance Route] 
WITH SERVICE_NAME = 'Maintenance',
ADDRESS = N'TCP://192.168.226.135'

ALTER ROUTE [Maintenance Route]
WITH ADDRESS = N'TCP://192.168.226.135:4025'

select * from sys.routes


use [Repair Team]
