create database test
go

use test
go

alter database test set honor_broker_priority on
go

select name, is_honor_broker_priority_on from sys.databases;
go

-- local service, remote service, and contract ALL is implied
CREATE BROKER PRIORITY foo FOR CONVERSATION SET ( PRIORITY_LEVEL = 4 ) 
go

-- can't have more than one default
CREATE BROKER PRIORITY foo2 FOR CONVERSATION SET ( PRIORITY_LEVEL = 6 )
go

-- but you can change the default
ALTER BROKER PRIORITY foo FOR CONVERSATION SET ( PRIORITY_LEVEL = 6 )
go

-- metadata
select * from sys.conversation_priorities;

-- create queues and services
CREATE QUEUE receiveq
go

CREATE SERVICE receiver
	ON QUEUE receiveq
	([DEFAULT])
go

-- RECEIVER CREATED

CREATE QUEUE senderq 
go

CREATE SERVICE sender
	ON QUEUE senderq
go

-- SENDER CREATED

DECLARE @h UNIQUEIDENTIFIER;
BEGIN TRY
    BEGIN TRANSACTION;
	BEGIN DIALOG CONVERSATION @h
	FROM SERVICE sender
	TO SERVICE 'receiver'
	WITH ENCRYPTION = OFF;

	SEND ON CONVERSATION @h
	('Hello World');
    COMMIT;
END TRY
BEGIN CATCH
	PRINT ERROR_MESSAGE();
    ROLLBACK;
END CATCH
go

-- MESSAGE SENT

-- priority appears at the endpoint level
select priority, * from sys.conversation_endpoints;
-- and the message level
select * from receiveq
go

DECLARE @h UNIQUEIDENTIFIER;
DECLARE @m VARCHAR(MAX);
BEGIN TRAN;
RECEIVE TOP(1) @h=conversation_handle, 
		@m=CAST(message_body as VARCHAR(MAX)) FROM receiveq;
PRINT @m;
SEND ON CONVERSATION @h ('Goodbye')
END CONVERSATION @h;
COMMIT;
go

-- MESSAGE RECEIVED

-- same on this side, priority appears
select * from senderq

DECLARE @h UNIQUEIDENTIFIER;
DECLARE @m VARCHAR(MAX);
BEGIN TRAN;
RECEIVE TOP(1) @h=conversation_handle, 
		@m=CAST(message_body as VARCHAR(MAX)) FROM senderq;
PRINT @m;
END CONVERSATION @h;
COMMIT;
go

-- this one will hang in sys.transmission_queue 
-- because it needs database master key

DECLARE @h UNIQUEIDENTIFIER;
BEGIN TRY
    BEGIN TRANSACTION;
	BEGIN DIALOG CONVERSATION @h
	FROM SERVICE sender
	TO SERVICE 'receiver'
	WITH ENCRYPTION = ON;

	SEND ON CONVERSATION @h
	('Hello World');
    COMMIT;
END TRY
BEGIN CATCH
	PRINT ERROR_MESSAGE();
    ROLLBACK;
END CATCH
go

-- priority here too
select priority, * from sys.transmission_queue