CREATE PROC test_procedure
as
use master

CREATE MASTER KEY  encryption by password='P@ssw0rd'
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'P@ssw0rd'
CLOSE MASTER KEY

--DROP Certificate test_SB

CREATE CERTIFICATE test_SB
--Encryption by password = 'P@ssw0rd'
with subject ='test',
START_DATE='05/05/2005'
CREATE LOGIN test_login with password = 'P@ssw0rd'

CREATE USER test_login




backup certificate test_SB
to FILE = 'c:\temp\test_sb.cer';

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

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

-- =============================================
-- Basic Create Service Broker Endpoint Template
-- =============================================
--DROP ENDPOINT test_endpoint
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'P@ssw0rd'
CLOSE MASTER KEY

select * from sys.certificates


CREATE ENDPOINT test_endpoint
STATE = started 
AS TCP (
   LISTENER_PORT = 4022
   )
FOR SERVICE_BROKER (
   AUTHENTICATION = CERTIFICATE test_SB,
	ENCRYPTION=DISABLED
   )


ALTER ENDPOINT test_endpoint
state=started


SELECT name, service_broker_guid FROM sys.databases

--DROP SERVICE pluto_service
-- =============================================
-- Basic Create Target Service Template
-- =============================================

CREATE SERVICE [pluto_service] 
   ON QUEUE test_queue
   ([DEFAULT] )

select * from test_queue
select * from sys.endpoints
select * from sys.services

select * from sys.transmission_queue

select * from sys.databases where name = 'test11'
select * from sys.service_broker_endpoints

use msdb
select * from sys.dm_broker_connections

CREATE ROUTE test_route 
WITH 
     SERVICE_NAME = N'pluto_service',
     BROKER_INSTANCE = 
	N'FA15B68C-D2CE-4C2A-8AF9-FF86C5993369' , 
     ADDRESS = N'LOCAL'


select * from sys.endpoints
select * from sys.services
alter database test11 SET TRUSTWORTHY ON
