Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Need help on T-SQL

Author  Topic 

wkm1925
Posting Yak Master

207 Posts
Posted - 2010-12-25 : 08:32:13
I've code to setup 2 database integration (same db server) using SQL Service Broker as follow,
localsb_db1


-- 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 = 'M@fc2010';
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
-- -----------------------------------------------
-- Configuring Service Broker
-- -----------------------------------------------


-- 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]
	);


localsb_db2


-- 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 = 'M@fc2010';
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
-- -----------------------------------------------
-- Configuring Service Broker
-- -----------------------------------------------


-- 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]
	);


My T-SQL to send message from localsb_db1 to localsb_db2 as follow,

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>My name is Bruce Willis</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)



Let's say, I want to involve 3 database integration, which is
localsb_db3


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

use localsb_db3;
GO

-- Create Master Key

CREATE MASTER KEY 
ENCRYPTION BY PASSWORD = 'M@fc2010';
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 QUEUE3
	), 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
-- -----------------------------------------------
-- Configuring Service Broker
-- -----------------------------------------------


-- 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 QUEUE3
    WITH STATUS=ON,
    ACTIVATION (
        PROCEDURE_NAME = OnReceiveMessage,
        MAX_QUEUE_READERS = 5,
		Execute AS 'dbo') ;

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


How My T-SQL to send message from localsb_db1 to localsb_db2 to localsb_db3 looks like?

I'm stuck
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site