-- creating localsb_db1 as the first database
create database localsb_db1
	WITH TRUSTWORTHY ON;
GO

use localsb_db1;
GO

-- Create Master Key

CREATE MASTER KEY 
ENCRYPTION BY PASSWORD = 'hellopassword';
GO


-- Creating Database table to be used for logging data
CREATE TABLE [dbo].[t_log](
	[logid] [int] IDENTITY(1,1) NOT NULL,
	[logdata] [varchar](max) COLLATE Latin1_General_CI_AI NULL,
	[msgdata] [xml] NULL,
 CONSTRAINT [PK_t_log] PRIMARY KEY CLUSTERED 
(
	[logid] ASC
) ON [PRIMARY]
) ON [PRIMARY]

GO




-- Stored Procedure that will handle the receiving of Messages
-- This was created from the SQL Server 2005 Templates

CREATE PROCEDURE [dbo].[OnReceiveMessage]

AS   
   





declare @message_type int
   declare @dialog uniqueidentifier,
	@ErrorSave INT,
    @ErrorDesc NVARCHAR(100),
	@message_body XML;




while (1 = 1)
begin
	begin transaction

-- Receive the next available message from the queue
	
	WAITFOR (
		RECEIVE top(1) -- just handle one message at a time
			@message_type=message_type_id, --the type of message 

received
			@message_body=message_body,      -- the message 

contents
			@dialog = conversation_handle    -- the identifier 

of the dialog this message was received on
			FROM QUEUE1
	), TIMEOUT 3000  -- if the queue is empty for three second, give 

UPDATE and go away

-- If we didn't get anything, bail out
	if (@@ROWCOUNT = 0)
		BEGIN
			Rollback Transaction
			BREAK
		END 

-- Check for errors in Receive
	SET @ErrorSave = @@ERROR ;

	IF (@ErrorSave <> 0)
	BEGIN
		ROLLBACK TRANSACTION ;
		SET @ErrorDesc = N'An error has occurred.' ;
		END CONVERSATION @dialog 
		WITH ERROR = @ErrorSave DESCRIPTION = @ErrorDesc ;

		INSERT INTO t_log VALUES(@ErrorDesc,NULL)
	END
	ELSE


-- Check for the End Dialog message.
	If (@message_type <> 2) -- End dialog message
		BEGIN
	
	-- Log Message in Database
		INSERT INTO t_log VALUES('Successfully 

Received',@message_body);
	-- Send the message back to the sender.
			SET @message_body = '<msg> i got the message 

</msg>';
			SEND ON CONVERSATION @dialog  -- send it back on the 

dialog we received the message on
				MESSAGE TYPE SendMessageType -- Must always 

supply a message type
				(@message_body);  -- the message contents 

are XML
			END CONVERSATION @dialog

		END
	ELSE
		BEGIN

			-- End Conversation and Notify other side of the 

conversation
			END CONVERSATION @dialog

		END
	

--  Commit the transaction.  At any point before this, we could roll 
--  back - the received message would be back on the queue and the response
--  wouldn't be sent.
	commit transaction
end
GO

-- Create a Type for Send Message type
CREATE MESSAGE TYPE SendMessageType
	VALIDATION = WELL_FORMED_XML;

-- Create a Type for Receive Message Type
CREATE MESSAGE TYPE ReceiveMessageType
	VALIDATION = WELL_FORMED_XML;

-- Create Contract to be used
-- you can use SENT BY ANY and use a single message instead
-- however for the sake of showing the contract functionality
-- i created two messsage types that can be sent by each

CREATE CONTRACT [MainContract]
(
	[SendMessageType] SENT BY INITIATOR,
	[ReceiveMessageType] SENT BY TARGET
);

-- Create a QUEUE

CREATE QUEUE QUEUE1
    WITH STATUS=ON,
    ACTIVATION (
        PROCEDURE_NAME = OnReceiveMessage,
        MAX_QUEUE_READERS = 5,
		Execute AS dbo') ;

-- Create a Service to be used
CREATE SERVICE SERVICE1
	ON QUEUE [QUEUE1]
	(
	[MainContract]
	);

-- creating localsb_db1 as the first database
create database localsb_db2
	WITH TRUSTWORTHY ON;
GO

use localsb_db2;
GO

-- Create Master Key

CREATE MASTER KEY 
ENCRYPTION BY PASSWORD = 'hellopassword';
GO


-- Creating Database table to be used for logging data
CREATE TABLE [dbo].[t_log](
	[logid] [int] IDENTITY(1,1) NOT NULL,
	[logdata] [varchar](max) COLLATE Latin1_General_CI_AI NULL,
	[msgdata] [xml] NULL,
 CONSTRAINT [PK_t_log] PRIMARY KEY CLUSTERED 
(
	[logid] ASC
) ON [PRIMARY]
) ON [PRIMARY]

GO


-- Stored Procedure that will handle the receiving of Messages

CREATE PROCEDURE [dbo].[OnReceiveMessage]

AS   
   





declare @message_type int
   declare @dialog uniqueidentifier,
	@ErrorSave INT,
    @ErrorDesc NVARCHAR(100),
	@message_body XML;




while (1 = 1)
begin
	begin transaction

-- Receive the next available message from the queue
	
	WAITFOR (
		RECEIVE top(1) -- just handle one message at a time
			@message_type=message_type_id, --the type of message 

received
			@message_body=message_body,      -- the message 

contents
			@dialog = conversation_handle    -- the identifier 

of the dialog this message was received on
			FROM QUEUE2
	), TIMEOUT 3000  -- if the queue is empty for three second, give 

UPDATE and go away

-- If we didn't get anything, bail out
	if (@@ROWCOUNT = 0)
		BEGIN
			Rollback Transaction
			BREAK
		END 

-- Check for errors in Receive
	SET @ErrorSave = @@ERROR ;

	IF (@ErrorSave <> 0)
	BEGIN
		ROLLBACK TRANSACTION ;
		SET @ErrorDesc = N'An error has occurred.' ;
		END CONVERSATION @dialog 
		WITH ERROR = @ErrorSave DESCRIPTION = @ErrorDesc ;

		INSERT INTO t_log VALUES(@ErrorDesc,NULL)
	END
	ELSE


-- Check for the End Dialog message.
	If (@message_type <> 2) -- End dialog message
		BEGIN
	
	-- Log Message in Database
		INSERT INTO t_log VALUES('Successfully 

Received',@message_body);
	-- Send the message back to the sender.
			SET @message_body = '<msg> i got the message 

</msg>';
			SEND ON CONVERSATION @dialog  -- send it back on the 

dialog we received the message on
				MESSAGE TYPE ReceiveMessageType -- Must 

always supply a message type
				(@message_body);  -- the message contents 

are XML
		END
	ELSE
		BEGIN

			-- End Conversation and Notify other side of the 

conversation
			END CONVERSATION @dialog

		END
	

--  Commit the transaction.  At any point before this, we could roll 
--  back - the received message would be back on the queue and the response
--  wouldn't be sent.
	commit transaction
end
	
GO

-- Create a Type for Send Message type
CREATE MESSAGE TYPE SendMessageType
	VALIDATION = WELL_FORMED_XML;

-- Create a Type for Receive Message Type
CREATE MESSAGE TYPE ReceiveMessageType
	VALIDATION = WELL_FORMED_XML;

-- Create Contract to be used
-- you can use SENT BY ANY and use a single message instead
-- however for the sake of showing the contract functionality
-- i created two messsage types that can be sent by each
CREATE CONTRACT [MainContract]
(
	[SendMessageType] SENT BY INITIATOR,
	[ReceiveMessageType] SENT BY TARGET
);

-- Create a QUEUE

CREATE QUEUE QUEUE2
    WITH STATUS=ON,
    ACTIVATION (
        PROCEDURE_NAME = OnReceiveMessage,
        MAX_QUEUE_READERS = 5,
		Execute AS 'dbo') ;

-- Create a Service to be used
CREATE SERVICE SERVICE2
	ON QUEUE [QUEUE2]
	(
	[MainContract]
	);

Use localsb_db1
GO



-- Send a Message from service1 on localsb_db1 to 
-- service2 on localsb_db2.


DECLARE @dialog_handle uniqueidentifier,
		@msg XML

BEGIN DIALOG CONVERSATION @dialog_handle
   FROM SERVICE [SERVICE1]
   TO SERVICE 'SERVICE2'
   ON CONTRACT [MainContract] ;

set @msg = '<name> This is Little Nick!!!</name>';


-- Now Sending a message note we are using
-- SendMessageType since it is defined in the contract
-- that it is to be sent only by initiater

SEND ON CONVERSATION @dialog_handle
	MESSAGE TYPE SendMessageType (@msg)