Service Broker--between databases of same instance

Databases: Adata and Bdata


USE Adata
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD='password'
GO
ALTER DATABASE Adata
SET ENABLE_BROKER
GO
/* in this simple example, you can set TRUSTWORTHY OFF.
TRUSTWORTHY is used fof Database modules (for example, user-defined functions or stored procedures) that use an impersonation context can access resources outside the database.
*/
ALTER DATABASE Adata
SET TRUSTWORTHY ON
GO
CREATE MESSAGE TYPE [initMessage] VALIDATION=NONE;
GO
CREATE MESSAGE TYPE [respMessage] VALIDATION=NONE;
GO
CREATE CONTRACT mContract
( [initMessage] sent by Initiator,
[respMessage] sent by target)
go
CREATE QUEUE mQueue WITH STATUS=ON,RETENTION=ON
GO
CREATE SERVICE mService ON QUEUE mQueue
(mContract);
GO



USE Bdata
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD='password'
GO
ALTER DATABASE Bdata
SET ENABLE_BROKER
GO
ALTER DATABASE Bdata
SET TRUSTWORTHY OFF
GO
CREATE MESSAGE TYPE [initMessage] VALIDATION=NONE;
GO
CREATE MESSAGE TYPE [respMessage] VALIDATION=NONE;
GO
CREATE CONTRACT mContract
( [initMessage] sent by Initiator,
[respMessage] sent by target)
go
CREATE QUEUE pQueue WITH STATUS=ON,RETENTION=ON
GO
CREATE SERVICE pService ON QUEUE pQueue
(mContract);
GO



USE Adata
GO
SET NOCOUNT ON
DECLARE @id uniqueidentifier
BEGIN TRAN
BEGIN DIALOG @id
FROM SERVICE mService
TO SERVICE 'pService'
ON CONTRACT [mContract]
WITH LIFETIME=600;
SEND ON CONVERSATION @id
MESSAGE TYPE initMessage (N'service is good.')
COMMIT
GO


USE Bdata
GO
SET NOCOUNT ON
DECLARE @id uniqueidentifier;
DECLARE @body nvarchar(max);
DECLARE @type sysname;
BEGIN TRAN
WAITFOR
(RECEIVE TOP (1)
@type=message_type_name,
@id=conversation_handle,
@body=message_body
FROM [pQueue]
),TIMEOUT 60;
PRINT @body;
SEND ON CONVERSATION @id
MESSAGE TYPE [respMessage] (N'what matters?');
END CONVERSATION @id;
COMMIT
GO

/*
SELECT * FROM Bdata.dbo.pQueue
SELECT * FROM Adata.dbo.mQueue
*/

/* you don't need to set up the route and route binding. It is local service. */