Can I have a simple technical example of how to start?

I find difficulty in getting started with the service broker, and didnwt find a simple example.

I understand service broker is used for usynchronous jobs.

For example- I want to have a service that will perform SQL commands.

What are the necesary commands to create the queue,

to add the comand "Update MyTable Set MyField=1" to it,

and to get the command and perform it in another sesion?

Hi Geri!

Enclosed you'll find a complete T-SQL script that shows you how you can use Service Broker for asynchronous jobs. When you look through the script, you'll encounter the stored procedure "ProcessRequestMessages". This is the place where you can put your needed T-SQL code into.

If you have further questions just ask.

HTH

Klaus Aschenbrenner

http://www.csharp.at

http://www.csharp.at/blog

Code Snippet

USE master;

IF EXISTS (SELECT * FROM sys.databases WHERE name = 'Chapter3_HelloWorldSvc')
BEGIN
PRINT 'Dropping database ''Chapter3_HelloWorldSvc''';
DROP DATABASE Chapter3_HelloWorldSvc;
END
GO

CREATE DATABASE Chapter3_HelloWorldSvc
GO

USE Chapter3_HelloWorldSvc
GO

--*********************************************
--* Create the message type "RequestMessage"
--*********************************************
CREATE MESSAGE TYPE
[http://ssb.csharp.at/SSB_Book/c03/RequestMessage]
VALIDATION = NONE
GO

--*********************************************
--* Create the message type "ResponseMessage"
--*********************************************
CREATE MESSAGE TYPE
[http://ssb.csharp.at/SSB_Book/c03/ResponseMessage]
VALIDATION = NONE
GO

--*********************************************
--* Show the created message types
--*********************************************
SELECT * FROM sys.service_message_types
GO

--************************************************
--* Changing the validation of the message types
--************************************************
ALTER MESSAGE TYPE [http://ssb.csharp.at/SSB_Book/c03/RequestMessage]
VALIDATION = WELL_FORMED_XML
GO

ALTER MESSAGE TYPE [http://ssb.csharp.at/SSB_Book/c03/ResponseMessage]
VALIDATION = WELL_FORMED_XML
GO

--************************************************
--* Create the contract "HelloWorldContract"
--************************************************
CREATE CONTRACT [http://ssb.csharp.at/SSB_Book/c03/HelloWorldContract]
(
[http://ssb.csharp.at/SSB_Book/c03/RequestMessage] SENT BY INITIATOR,
[http://ssb.csharp.at/SSB_Book/c03/ResponseMessage] SENT BY TARGET
)
GO

--*************************************************************
--* Getting some information about the newly created contract
--*************************************************************
SELECT
sc.name AS 'Contract',
mt.name AS 'Message type',
cm.is_sent_by_initiator,
cm.is_sent_by_target,
mt.validation
FROM sys.service_contract_message_usages cm
INNER JOIN sys.service_message_types mt ON cm.message_type_id = mt.message_type_id
INNER JOIN sys.service_contracts sc ON sc.service_contract_id = cm.service_contract_id
GO

--********************************************************
--* Create the queues "InitiatorQueue" and "TargetQueue"
--********************************************************
CREATE QUEUE InitiatorQueue
WITH STATUS = ON
GO

CREATE QUEUE TargetQueue
WITH STATUS = ON
GO

--*************************************************************
--* Getting some information about the newly created queues
--*************************************************************
SELECT * FROM sys.service_queues
GO

--************************************************************
--* Create the queues "InitiatorService" and "TargetService"
--************************************************************
CREATE SERVICE InitiatorService
ON QUEUE InitiatorQueue
(
[http://ssb.csharp.at/SSB_Book/c03/HelloWorldContract]
)
GO

CREATE SERVICE TargetService
ON QUEUE TargetQueue
(
[http://ssb.csharp.at/SSB_Book/c03/HelloWorldContract]
)
GO

--*************************************************************
--* Getting some information about the newly created services
--*************************************************************
SELECT
sv.name AS 'Service',
sc.name AS 'Contract'
FROM sys.services sv
INNER JOIN sys.service_contract_usages scu ON scu.service_id = sv.service_id
INNER JOIN sys.service_contracts sc ON sc.service_contract_id = scu.service_contract_id
GO

--********************************************************************
--* Sending a message from the InitiatorService to the TargetService
--********************************************************************
BEGIN TRY
BEGIN TRANSACTION;
DECLARE @.ch UNIQUEIDENTIFIER
DECLARE @.msg NVARCHAR(MAX);

BEGIN DIALOG CONVERSATION @.ch
FROM SERVICE [InitiatorService]
TO SERVICE 'TargetService'
ON CONTRACT [http://ssb.csharp.at/SSB_Book/c03/HelloWorldContract]
WITH ENCRYPTION = OFF;

SET @.msg =
'<HelloWorldRequest>
Klaus Aschenbrenner
</HelloWorldRequest>';

SEND ON CONVERSATION @.ch MESSAGE TYPE
[http://ssb.csharp.at/SSB_Book/c03/RequestMessage]
(@.msg);
COMMIT
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
GO

--********************************************************************
--* View the sent message on the queue "TargetQueue"
--********************************************************************
SELECT * FROM TargetQueue
GO

--********************************************************************
--* View the created conversation endpoints
--********************************************************************
SELECT * FROM sys.conversation_endpoints
GO

--********************************************************************
--* Retrieve the sent message from the queue "TargetQueue"
--********************************************************************
DECLARE @.cg UNIQUEIDENTIFIER
DECLARE @.ch UNIQUEIDENTIFIER
DECLARE @.messagetypename NVARCHAR(256)
DECLARE @.messagebody XML;

BEGIN TRY
BEGIN TRANSACTION;

RECEIVE TOP(1)
@.cg = conversation_group_id,
@.ch = conversation_handle,
@.messagetypename = message_type_name,
@.messagebody = CAST(message_body AS XML)
FROM TargetQueue

PRINT 'Conversation group: ' + CAST(@.cg AS NVARCHAR(MAX))
PRINT 'Conversation handle: ' + CAST(@.ch AS NVARCHAR(MAX))
PRINT 'Message type: ' + @.messagetypename
PRINT 'Message body: ' + CAST(@.messagebody AS NVARCHAR(MAX))

COMMIT
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
GO

--***********************************************************************************
--* Retrieve the sent message from the queue "TargetQueue" with a WAITFOR statement
--***********************************************************************************
DECLARE @.cg UNIQUEIDENTIFIER
DECLARE @.ch UNIQUEIDENTIFIER
DECLARE @.messagetypename NVARCHAR(256)
DECLARE @.messagebody XML;

BEGIN TRY
BEGIN TRANSACTION;

WAITFOR (
RECEIVE TOP (1)
@.cg = conversation_group_id,
@.ch = conversation_handle,
@.messagetypename = message_type_name,
@.messagebody = CAST(message_body AS XML)
FROM TargetQueue
), TIMEOUT 60000

IF (@.@.ROWCOUNT > 0)
BEGIN
PRINT 'Conversation group: ' + CAST(@.cg AS NVARCHAR(MAX))
PRINT 'Conversation handle: ' + CAST(@.ch AS NVARCHAR(MAX))
PRINT 'Message type: ' + @.messagetypename
PRINT 'Message body: ' + CAST(@.messagebody AS NVARCHAR(MAX))
END

COMMIT
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
GO

--**************************************************
--* Create a table to store the processed messages
--**************************************************
CREATE TABLE ProcessedMessages
(
ID UNIQUEIDENTIFIER NOT NULL,
MessageBody XML NOT NULL,
ServiceName NVARCHAR(MAX) NOT NULL
)
GO

--*******************************************************************
--* Send a response message back to the service "InitiatorService"
--*******************************************************************
DECLARE @.ch UNIQUEIDENTIFIER
DECLARE @.messagetypename NVARCHAR(256)
DECLARE @.messagebody XML
DECLARE @.responsemessage XML;

BEGIN TRY
BEGIN TRANSACTION
WAITFOR (
RECEIVE TOP (1)
@.ch = conversation_handle,
@.messagetypename = message_type_name,
@.messagebody = CAST(message_body AS XML)
FROM TargetQueue
), TIMEOUT 60000

IF (@.@.ROWCOUNT > 0)
BEGIN
IF (@.messagetypename = 'http://ssb.csharp.at/SSB_Book/c03/RequestMessage')
BEGIN
-- Store the received request message in a table
INSERT INTO ProcessedMessages (ID, MessageBody, ServiceName)
VALUES (NEWID(), @.messagebody, 'TargetService')

-- Construct the response message
SET @.responsemessage =
'<HelloWorldResponse>' +
@.messagebody.value('/HelloWorldRequest[1]', 'NVARCHAR(MAX)') +
'</HelloWorldResponse>';

-- Send the response message back to the initiating service
SEND ON CONVERSATION @.ch MESSAGE TYPE
[http://ssb.csharp.at/SSB_Book/c03/ResponseMessage]
(@.responsemessage);

-- End the conversation on the target's side
END CONVERSATION @.ch;
END
END
COMMIT
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
GO

--********************************************************************
--* View the processed message in the table "ProcessedMessages"
--********************************************************************
SELECT * FROM ProcessedMessages
GO

SELECT * FROM InitiatorQueue
GO

--*******************************************************************
--* Service program for the service "InitiatorService"
--*******************************************************************
DECLARE @.ch UNIQUEIDENTIFIER
DECLARE @.messagetypename NVARCHAR(256)
DECLARE @.messagebody XML;

BEGIN TRY
BEGIN TRANSACTION
WAITFOR (
RECEIVE TOP (1)
@.ch = conversation_handle,
@.messagetypename = message_type_name,
@.messagebody = CAST(message_body AS XML)
FROM InitiatorQueue
), TIMEOUT 60000

IF (@.@.ROWCOUNT > 0)
BEGIN
IF (@.messagetypename = 'http://ssb.csharp.at/SSB_Book/c03/ResponseMessage')
BEGIN
-- Store the received response) message in a table
INSERT INTO ProcessedMessages (ID, MessageBody, ServiceName)
VALUES (NEWID(), @.messagebody, 'InitiatorService')
END

IF (@.messagetypename = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
BEGIN
-- End the conversation on the initiator's side
END CONVERSATION @.ch;
END
END
COMMIT
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
GO

--************************************************************************
--* A stored procedure used for internal activation on the target queue
--************************************************************************
CREATE PROCEDURE ProcessRequestMessages
AS
DECLARE @.ch UNIQUEIDENTIFIER
DECLARE @.messagetypename NVARCHAR(256)
DECLARE @.messagebody XML
DECLARE @.responsemessage XML;

BEGIN TRY
BEGIN TRANSACTION
WAITFOR (
RECEIVE TOP (1)
@.ch = conversation_handle,
@.messagetypename = message_type_name,
@.messagebody = CAST(message_body AS XML)
FROM TargetQueue
), TIMEOUT 60000

IF (@.@.ROWCOUNT > 0)
BEGIN
IF (@.messagetypename = 'http://ssb.csharp.at/SSB_Book/c03/RequestMessage')
BEGIN
-- Store the received request message in a table
INSERT INTO ProcessedMessages (ID, MessageBody, ServiceName) VALUES (NEWID(), @.messagebody, 'TargetService')

-- Construct the response message
SET @.responsemessage =
'<HelloWorldResponse>' +
@.messagebody.value('/HelloWorldRequest[1]', 'NVARCHAR(MAX)') +
'</HelloWorldResponse>';

-- Send the response message back to the initiating service
SEND ON CONVERSATION @.ch
MESSAGE TYPE [http://ssb.csharp.at/SSB_Book/c03/ResponseMessage]
(@.responsemessage);

-- End the conversation on the target's side
END CONVERSATION @.ch;
END
END
COMMIT
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
GO

--*****************************************************************
--* Enabling internal activation on the queue "TargetQueue"
--*****************************************************************
ALTER QUEUE TargetQueue
WITH ACTIVATION
(
STATUS = ON,
PROCEDURE_NAME = [ProcessRequestMessages],
MAX_QUEUE_READERS = 1,
EXECUTE AS SELF
)
GO

SELECT * FROM ProcessedMessages
GO

|||

I will try it as soon as possible.