CREATE MASTER KEY  encryption by password='P@ssw0rd'

Create proc test_procedure
as


ALTER DATABASE Test10 SET ENABLE_BROKER

-- =============================================
-- Create Queue with Activation Template
-- =============================================

CREATE QUEUE test_queue
   WITH ACTIVATION (
      PROCEDURE_NAME = test_procedure ,
      MAX_QUEUE_READERS = 5, 
      EXECUTE AS SELF ) 

CREATE CERTIFICATE test_SB
Encryption by password = 'P@ssw0rd'
with subject ='test'

select cast(message_body as xml) from test_queue
select * from sys.transmission_queue
select * from sys.routes
select * from sys.dm_broker_connections

--The target service name could not be found. Ensure that the service name is specified correctly and/or the routing information has been supplied.
--The Service Broker protocol transport is disabled or not configured.

-- =============================================
-- Basic Create Initiator Service Template
-- =============================================

CREATE SERVICE [test_service] 
ON QUEUE test_queue

alter database test10 SET TRUSTWORTHY ON

-- =============================================
-- Basic Create Route Template
--

-- Drop route test_route

CREATE ROUTE test_route 
WITH 
     SERVICE_NAME = N'pluto_service',
     BROKER_INSTANCE = 
	N'FA15B68C-D2CE-4C2A-8AF9-FF86C5993369' , 
     ADDRESS = N'TCP://192.168.1.151:4022'

create 
--drop
proc testSend
as

DECLARE @conversationHandle uniqueidentifier;
BEGIN TRANSACTION;

-- Begin a dialog to the ISBN Lookup Service

BEGIN DIALOG CONVERSATION  @conversationHandle
    FROM SERVICE    [test_service]
    TO SERVICE      N'pluto_service',
N'FA15B68C-D2CE-4C2A-8AF9-FF86C5993369'
    ON CONTRACT     [DEFAULT]
    WITH LIFETIME = 600,
	ENCRYPTION = OFF;

-- Send a message on the dialog
SEND ON CONVERSATION @conversationHandle 
	MESSAGE TYPE [DEFAULT] 
(N'<ISBNLookupRequest>
<ISBN>0-9726888-1-1</ISBN>
</ISBNLookupRequest>');

COMMIT;

testSend



--The Service Broker protocol transport is disabled or not configured.
select * from sys.databases


use test10

use master

CREATE CERTIFICATE test_SB
from file= 'C:\temp\test_sb.cer'

Create user foo without login
