/*
  Script for database 2 for SSB sample, communicating
  between two local databases.

  This script sets up an activation stored procedure and in the proc
  the incoming message is written to a table

  Any questions etc.: nielsb@develop.com

*/

--Step 6

use [loc_ssb2];
go

--create the table to write the message to
create table audit(id int identity, msg xml);
go


--Step 7
--create the proc to use as activation proc
--uncomment from here until go, highlight and run

--create procedure recProc
--as
--
--DECLARE @m xml; --the received message
--DECLARE @h uniqueidentifier; -- conversation handle
--
--begin try
--  begin tran;
--  receive top(1) @m = message_body,
--              @h = conversation_handle
--  from q2
--
--  --insert the message into the audit table
--  insert into audit values(@m);
--
--  --set up the return msg
--  DECLARE @msg xml; --return msg
--  SET @msg = '<world/>';
--  SEND ON CONVERSATION @h
--  MESSAGE TYPE [recmsg]
--  (@msg);
--
--
--  --end the conversation
--  END CONVERSATION @h;
--
--  --end the conversation
--  END CONVERSATION @h
--  with cleanup;
--
--  commit
--end try
--begin catch
--  rollback
--end catch
--
--go

--Step 8
--now it is time to alter the queue so it will
--do autometic activation
--uncomment from here to go, highlight and run
--ALTER QUEUE q2
--with status = ON,
--ACTIVATION 
--(
--  STATUS = ON,
--  procedure_name = recProc,
--  max_queue_readers = 5,
--  execute as self
--)
--
--go

--go back to Step 9 in 2_communicate_db1.sql and send
--a message to the service to make sure the activation works


--Step 12
--after having ended the conversation in th first database
--check that we have some records in the audit table
--uncomment from here to go and execute

--select * from audit;
--
--
----check that there are no more messages in the queue
--select * from q2;
--
----check that the conversations have been cleaned up
--select * from sys.conversation_endpoints;
--
--go
