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.

Can Alter (current) Database?

I have a *.sql script that creates database tables, and I need to modify the database to enable the service broker. In addition, the actual name of the database is not known in advance - it is set per instance of the application.

I know I need to do:

ALTER DATABASE dbname SET ENABLE_BROKER

But I must avoid including the name of the database in the script. I did wonder if this would work:

DECLARE @.DB varchar(50)

SELECT @.DB = DB_NAME()

ALTER DATABASE @.DB SET ENABLE_BROKER

But I just get a syntax error. Presumably this also rules out setting the database name as a parameter to the script (SqlParameter stuff)

The only option I can think of is dynamically creating the statement, either in T-SQL or in the calling .NET environment.

Any thoughts?

Ruth

Hi,

I guess you have to create a dynamic statement to make it work, something like:

DECLARE @.DB varchar(50)

SELECT @.DB = DB_NAME()

DECLARE @.SQLString VARCHAR(200)
SET @.SQLString = ' ALTER DATABASE ' + @.DB + 'SET ENABLE_BROKER'
EXEC(@.SqlString)

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks, that looks good. You can of course replace @.DB with DB_NAME() in the SET.

Ruth

|||

Dynamic SQL is the only solution I know of. Make sure you protect yourself against SQL injection problems (e.g. use QUOTENAME on the database name). Also, ALTER DATABASE requires exclusive lock on the database, see http://blogs.msdn.com/remusrusanu/archive/2006/01/30/519685.aspx

HTH,
~ Remus

|||Remus,

I assumed that the value returned from DB_NAME() would be acceptable in SQL. Probably a bad assumption, really!

So, I did have:
SET @.AlterStmt = 'ALTER DATABASE ' + DB_NAME() + ' SET ENABLE_BROKER'

but I should really have this?
SET @.AlterStmt = 'ALTER DATABASE ' + QUOTENAME(DB_NAME()) + ' SET ENABLE_BROKER'

Thinking about injection, what is the best method to use when creating SQL on the fly. I don't want to use SqlParameters for everything as they obfuscate the code significantly. Is there an System.Data.SqlClient equivalent of QUOTENAME() ?

Ruth
|||

DB_NAME() is fine, it doesn't need to be passed into QUOTENAME. When I formulated the reply, Jens' post simply wasn't there and I didn't see it and I assumed the database name comes in as an argument.

You should worry about SQL Injection if the database name comes from an external (potentialy untrusted) source like a web form text field.

There is no equivalent to QUOTENAME, I usually use the simplest String.Replace method, like this:

string quotedDbName = "[" + dbnameVariable.Replace("]","]]") + "]";

HTH,
~ Remus