SQL Server 2008 - Modulo 11 - Curso 6232

--SERVICE BROKER

-- Configurando banco de dados

--O Service Broker ajuda os desenvolvedores de bancos de dados na criação de

--aplicativos confiáveis e dimensionáveis. Como o Service Broker faz parte do

--Mecanismo de Banco de Dados, a administração desses aplicativos faz parte da

--administração de rotina do banco de dados.

--O Service Broker fornece enfileiramento e mensagens confiáveis para o SQL Server.

--O Service Broker é usado por aplicativos que utilizam uma única instância do SQL Server e que distribuem o trabalho entre várias instâncias.

--Dentro de uma única instância do SQL Server, o Service Broker fornece um modelo

--de programação assíncrono avançado. Os aplicativos de banco de dados geralmente

--usam programação assíncrona para diminuir o tempo de resposta interativo e aumentar o throughput de aplicativo global.

--O Service Broker também fornece mensagens confiáveis entre instâncias do SQL Server.

--O Service Broker ajuda os desenvolvedores a compor aplicativos de componentes

--independentes denominados serviços. Os aplicativos que exigem a funcionalidade

--exposta nesses serviços usam mensagens para interagir com os serviços.

--O Service Broker usa TCP/IP para trocar mensagens entre instâncias.

--O Service Broker contém recursos para ajudar a impedir o acesso não autorizado

--da rede e para criptografar mensagens enviadas pela rede.

--construcao de aplicacoes assincronas orientada a banco de dados

ALTER DATABASE AdventureWorks SET ENABLE_BROKER

GO

USE AdventureWorks

GO

CREATE MASTER KEY

ENCRYPTION BY PASSWORD = 'Pa$$w0rd'

GO

-- Cria as filas (queue)

--Em tempo de design, os aplicativos do Service Broker especificam os seguintes

--objetos:

--Tipos de mensagem — Defina os nomes das mensagens trocadas entre aplicativos.

--Se desejar, forneça validação para as mensagens.

--Contratos — Especifique a direção e o tipo de mensagem em uma determinada

--conversação.

--Filas — Armazene mensagens. O mecanismo de armazenamento permite a comunicação

--assíncrona entre os serviços. As filas do Service Broker fornecem benefícios

--adicionais, como o bloqueio automático de mensagens no mesmo grupo de conversação.

--Serviços — São pontos de extremidade endereçáveis para conversações. As mensagens

--do Service Broker são enviadas de um serviço para outro. Um serviço especifica uma

--fila para conter as mensagens e determina os contratos para os quais o serviço pode

--ser o destino. Um contrato fornece um serviço com um conjunto bem definido de tipos

--de mensagem.

--Um aplicativo do Service Broker usa os objetos do SQL Server na lista anterior para

--realizar uma conversação. Qualquer programa que possa executar instruções

--Transact-SQL no SQL Server pode usar Service Broker. Os aplicativos podem ser

--procedimentos armazenados escritos em Transact-SQL ou em uma linguagem compatível

--com CLR ou podem ser programas externos que se conectam a uma instância do

--SQL Server.

use AdventureWorks

go

CREATE SERVICE [serviceCMDRequisicao] ON QUEUE [qCDMProdutoID]

(

                [contractContaDoMaterial]

)

CREATE SERVICE [serviceCDMResposta] ON QUEUE [qCDMResultado]

Go

--Cria os contratos

--Contratos definem o nome de uma tarefa comercial específica e listam os tipos de

--mensagem usados naquela tarefa. Os contratos do Service Broker definem duas funções

--de serviço diferentes: o iniciador e o destino. O iniciador de uma conversação

--começa

-- a conversação enviando uma mensagem ao destino. O contrato que a conversação usa

-- define qual função de serviço pode enviar um determinado tipo de mensagem.

--Para cada tarefa que o serviço executa, crie um contrato que inclui os tipos de

--mensagem para cada etapa na tarefa. Para cada tipo de mensagem, especifique se o

--tipo da mensagem é enviado do iniciador para o destino, do destino para o iniciador

--ou nas duas direções.

--Um contrato não especifica mensagem de solicitação ou o número de mensagens de um

--tipo específico que possa ser enviado. O Service Broker requer que o iniciador envie

--a primeira mensagem em uma conversação de caixa de diálogo. Depois da primeira

--mensagem, não há requisitos de solicitação.

--Mais de um contrato pode usar os mesmos tipos de mensagem. Por exemplo, uma

--mensagem

--que consiste em um documento XML que contém um número de peça ou quantidade pode

--ser

--útil em uma tarefa que aceita um pedido de um cliente, uma tarefa que gerencia o

--estoque e uma tarefa que solicita a remessa. Cada tarefa corresponde a um contrato

--distinto, mas todos os três contratos podem usar o mesmo tipo de mensagem.

USE AdventureWorks

GO

CREATE CONTRACT [contractContaDoMaterial]

(

                [mtEnviaContaDoMaterialProduto] SENT BY initiator,

                [mtRecebeContaMaterial] SENT BY target

)

--Cria os serviços

--Em tempo de design, os aplicativos do Service Broker especificam os seguintes

--objetos:

--Tipos de mensagem — Defina os nomes das mensagens trocadas entre aplicativos.

--Se desejar, forneça validação para as mensagens.

--Contratos — Especifique a direção e o tipo de mensagem em uma determinada

--conversação.

--Filas — Armazene mensagens. O mecanismo de armazenamento permite a comunicação

--assíncrona entre os serviços. As filas do Service Broker fornecem benefícios

--adicionais, como o bloqueio automático de mensagens no mesmo grupo de conversação.

--Serviços — São pontos de extremidade endereçáveis para conversações. As mensagens

--do Service Broker são enviadas de um serviço para outro. Um serviço especifica uma

--fila para conter as mensagens e determina os contratos para os quais o serviço pode

--ser o destino. Um contrato fornece um serviço com um conjunto bem definido de tipos

--de mensagem.

--Um aplicativo do Service Broker usa os objetos do SQL Server na lista anterior para

--realizar uma conversação. Qualquer programa que possa executar instruções

--Transact-SQL no SQL Server pode usar Service Broker. Os aplicativos podem ser

--procedimentos armazenados escritos em Transact-SQL ou em uma linguagem compatível

--com CLR ou podem ser programas externos que se conectam a uma instância do

--SQL Server.

use AdventureWorks

go

CREATE SERVICE [serviceCMDRequisicao] ON QUEUE [qCDMProdutoID]

(

                [contractContaDoMaterial]

)

CREATE SERVICE [serviceCDMResposta] ON QUEUE [qCDMResultado]

go

--Cria os tipos de mensagens

select * from [qCDMProdutoID]

select * from [qCDMResultado]

--Criando conversations

--Todas as mensagens enviadas pelo Service Broker fazem parte de uma conversação.

--Um diálogo é uma conversação entre dois serviços. Um diálogo é um fluxo bidirecional

--confiável e persistente de mensagens entre dois serviços.

--Os diálogos fornecem entrega de mensagens EOIO (exatamente-uma vez-na-ordem).

--Os diálogos usam o identificador de conversação e os números de seqüência contidos em cada mensagem para identificar as mensagens relacionadas e entregar mensagens na ordem correta. Um diálogo é um fluxo confiável e persistente de mensagens entre dois serviços.

--Uma conversação de diálogo tem dois participantes. O iniciador inicia a conversação.

--O destino aceita uma conversação iniciada pelo iniciador. Se um participante inicia

--a conversa, isso determina as mensagens que ele pode enviar, conforme o especificado

--no contrato para a conversação.

use AdventureWorks

go

DECLARE @Dialog UNIQUEIDENTIFIER

BEGIN DIALOG conversation @Dialog

from service serviceCMDRequisicao

to service 'serviceCDMResposta'

on contract contractContaDoMaterial

select @Dialog

--Enviando mensagens

--passo 1

select * from [qCDMProdutoID]

select * from [qCDMResultado]

--passo 2 - criacao do conteudo da mensagem

DECLARE @mensagem xml

SET @mensagem = N'<ProductID>2</ProductID>';

--ENVIA UMA MENSAGEM NO DIALOGO

SEND ON CONVERSATION 'A0702CCC-2D7A-DF11-82DD-0003FF8EC8FE'

MESSAGE TYPE mtEnviaContaDoMaterialProduto

(@mensagem)

--verificando novamente

select * from [qCDMProdutoID]

select * from [qCDMResultado]

--verificando conteudo da mensagem

--VERIFICANDO AS MENSAGENS NA FILA

SELECT CAST(message_body as xml) from [qCDMProdutoID]

go

--Recebendo mensagens

receive top(1) * from

[qCDMProdutoID]

--verificando novamente

select * from [qCDMProdutoID]

select * from [qCDMResultado]

--Verifica as mensagens

select * from [qCDMProdutoID]

select * from [qCDMResultado]

--Cria os tipos de mensagens

--Mensagens são as informações trocadas entre os aplicativos que usam o

--Service Broker.

--Cada mensagem é parte de uma conversação. Uma mensagem tem um tipo específico que

--é determinado pelo aplicativo que a envia. Cada mensagem tem uma identidade de

--conversação exclusiva, bem como um número de seqüência dentro da conversação.

--Ao receber mensagens, o Service Broker usa a identidade de conversação e o número de seqüência da mensagem para impor a ordem das mensagens.

--O conteúdo da mensagem é determinado pelo aplicativo. Quando uma mensagem é recebida,

-- o Service Broker valida o conteúdo dela para assegurar que ele seja válido para o

-- tipo de mensagem. Independentemente do tipo de mensagem, o SQL Server armazena o

-- conteúdo dela como o tipo varbinary(max). Assim, uma mensagem pode conter qualquer

-- dado que possa ser convertido em varbinary(max).

--Em geral, um aplicativo processa o conteúdo de uma mensagem com base no contrato e

--no tipo de mensagem.

USE AdventureWorks

GO

CREATE MESSAGE TYPE [mtEnviaContaDoMaterialProduto] VALIDATION = WELL_FORMED_XML

CREATE MESSAGE TYPE [mtRecebeContaMaterial] VALIDATION = WELL_FORMED_XML

--PRATICA

--Exemplo 1 - Criação

-- Criando 2 bancos de dados para demonstração do Service Broker

USE master

GO

CREATE DATABASE BookStore

GO

CREATE DATABASE BookDistribution

GO

-- Habilitando o Service Broker

ALTER DATABASE BookStore SET ENABLE_BROKER

GO

-- The TRUSTWORTHY database property is used to indicate

-- whether the instance of SQL Server trusts the database

-- and the contents within it.

ALTER DATABASE BookStore SET TRUSTWORTHY ON

GO

ALTER DATABASE BookDistribution SET ENABLE_BROKER

GO

ALTER DATABASE BookDistribution SET TRUSTWORTHY ON

GO

-- Desabilitando o Service Broker

--ALTER DATABASE BookStore SET DISABLE_BROKER

--GO

--ALTER DATABASE BookStore SET TRUSTWORTHY OFF

--GO

--

--ALTER DATABASE BookDistribution SET DISABLE_BROKER

--GO

--ALTER DATABASE BookDistribution SET TRUSTWORTHY OFF

--GO

-- Verificando se o Service Broker está habilitado

SELECT [name], is_broker_enabled FROM sys.databases

WHERE [name] in ('BookStore', 'BookDistribution')

-- Criando a Master Key para o dois bancos, BookStore e BookDistribution

-- A Master Key é utilizada para criptografar as mensagens

USE BookStore

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'D4C86597'

GO

USE BookDistribution

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '50255686DDC5'

GO

-- Criando Message Types

Use BookStore

GO

-- Para ENVIO do pedido

CREATE MESSAGE TYPE [//SackConsulting/SendBookOrder]

VALIDATION = WELL_FORMED_XML

GO

-- Para CONFIRMAR o envio do pedido

CREATE MESSAGE TYPE [//SackConsulting/BookOrderReceived]

VALIDATION = WELL_FORMED_XML

GO

-- Message Types para o banco BookDistribution

USE BookDistribution

GO

CREATE MESSAGE TYPE [//SackConsulting/SendBookOrder]

VALIDATION = WELL_FORMED_XML

GO

CREATE MESSAGE TYPE [//SackConsulting/BookOrderReceived]

VALIDATION = WELL_FORMED_XML

GO

-- Criando Contratos

Use BookStore

GO

CREATE CONTRACT

                [//SackConsulting/BookOrderContract]

                ( [//SackConsulting/SendBookOrder]

                               SENT BY INITIATOR,

                [//SackConsulting/BookOrderReceived]

                               SENT BY TARGET

)

GO

USE BookDistribution

GO

CREATE CONTRACT

                [//SackConsulting/BookOrderContract]

                ( [//SackConsulting/SendBookOrder]

                               SENT BY INITIATOR,

                [//SackConsulting/BookOrderReceived]

                               SENT BY TARGET

)

GO

-- Criando Filas

Use BookStore

GO

CREATE QUEUE BookStoreQueue

WITH STATUS = ON

GO

USE BookDistribution

GO

CREATE QUEUE BookDistributionQueue

WITH STATUS = ON

GO

-- Criando Serviços

Use BookStore

GO

CREATE SERVICE [//SackConsulting/BookOrderService]

                ON QUEUE dbo.BookStoreQueue

                ([//SackConsulting/BookOrderContract])

USE BookDistribution

GO

CREATE SERVICE [//SackConsulting/BookDistributionService]

                ON QUEUE dbo.BookDistributionQueue

                ([//SackConsulting/BookOrderContract])

GO

-- SERVICE BROKER configurado com sucesso!

--TESTE 01

----------------------------------------------------------------------

-- Depois das MESSAGES, QUEUES, CONTRACTS e SERVICES serem criados,

-- podemos testar a funcionamento do Service Broker

----------------------------------------------------------------------

Use BookStore

GO

DECLARE @Conv_Handler uniqueidentifier

DECLARE @OrderMsg xml;

BEGIN DIALOG CONVERSATION @Conv_Handler

FROM SERVICE [//SackConsulting/BookOrderService]

TO SERVICE '//SackConsulting/BookDistributionService'

ON CONTRACT [//SackConsulting/BookOrderContract];

SET @OrderMsg =

'<order id="3439" customer="22" orderdate="7/15/2005">

<LineItem ItemNumber="1" ISBN="1-59059-592-0" Quantity="1" />

</order>';

SEND ON CONVERSATION @Conv_Handler

MESSAGE TYPE [//SackConsulting/SendBookOrder]

(@OrderMsg);

-- Verificando a fila

USE BookDistribution

GO

SELECT  message_type_name

                               ,CAST(message_body as xml) [message]

                               ,queuing_order

                               ,conversation_handle

                               ,conversation_group_id

FROM dbo.BookDistributionQueue

-- Criando a tabela

USE BookDistribution

GO

CREATE TABLE dbo.BookOrderReceived

(

                BookOrderReceivedID                 int IDENTITY (1,1)            NOT NULL,

                conversation_handle                    uniqueidentifier              NOT NULL,

                conversation_group_id               uniqueidentifier              NOT NULL,

                message_body                                               xml                                                                       NOT NULL

)

GO

-- Declarando variáveis para armazenar os dados da mensagem

DECLARE @Conv_Handler uniqueidentifier

DECLARE @Conv_Group uniqueidentifier

DECLARE @OrderMsg xml

DECLARE @TextResponseMsg varchar(8000)

DECLARE @ResponseMsg xml

DECLARE @OrderID int;

-- Retirando a mensagem da file e armazenando os valores nas variáveis

-- Usamos o comando RECEIVE para ler e "RETIRAR" os registros de uma fila

RECEIVE TOP(1) @OrderMsg = message_body,

                @Conv_Handler = conversation_handle,

                @Conv_Group = conversation_group_id

FROM dbo.BookDistributionQueue;

-- Inserindo os valores das variáveis na tabela

INSERT dbo.BookOrderReceived (conversation_handle, conversation_group_id, message_body)

VALUES (@Conv_Handler,@Conv_Group, @OrderMsg)

-- Usando XQuery com a mensagem recebida para extrair o ID do pedido, para

-- validar a mensagem recebida

SELECT @OrderID = @OrderMsg.value('(/order/@id)[1]', 'int')

SELECT @TextResponseMsg = '<orderreceived id= "' + CAST(@OrderID as varchar(10)) + '"/>';

SELECT @ResponseMsg = CAST(@TextResponseMsg as xml);

-- Enviando a confirmação para o initiator, usando um conversation handle existente

SEND ON CONVERSATION @Conv_Handler

MESSAGE TYPE [//SackConsulting/BookOrderReceived]

(@ResponseMsg );

USE BookStore

GO

-- Criando tabela para receber as confirmações de pedido

CREATE TABLE dbo.BookOrderConfirmation

(

                BookOrderConfirmationID                         int                                                                         IDENTITY (1,1)   NOT NULL,

                conversation_handle                                   uniqueidentifier              NOT NULL,

                DateReceived                                                  datetime                                            NOT NULL                          DEFAULT GETDATE(),

                message_body                                                               xml                                                                       NOT NULL

)

GO

-- Enviando a confirmação

DECLARE @Conv_Handler uniqueidentifier

DECLARE @Conv_Group uniqueidentifier

DECLARE @OrderMsg xml

DECLARE @TextResponseMsg varchar(8000);

RECEIVE TOP(1) @Conv_Handler = conversation_handle,

                @OrderMsg = message_body

FROM dbo.BookStoreQueue

INSERT dbo.BookOrderConfirmation

(conversation_handle, message_body)

VALUES (@Conv_Handler, @OrderMsg)

END CONVERSATION @Conv_Handler;

GO

-- Terminando a conversation

USE BookDistribution

GO

DECLARE @Conv_Handler uniqueidentifier

DECLARE @Conv_Group uniqueidentifier

DECLARE @OrderMsg xml

DECLARE @message_type_name nvarchar(256);

RECEIVE TOP(1) @Conv_Handler = conversation_handle,

                @OrderMsg = message_body,

                @message_type_name = message_type_name

FROM dbo.BookDistributionQueue

-- Ambos (initiator e target) devem terminar a conversation

IF @message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'

BEGIN

                END CONVERSATION @Conv_Handler;

END

SELECT state_desc, conversation_handle

FROM sys.conversation_endpoints

SELECT * FROM BookDistribution.dbo.BookOrderReceived

SELECT * FROM BookStore.dbo.BookOrderConfirmation

--TESTE 02

----------------------------------------------------------------------

-- Depois das MESSAGES, QUEUES, CONTRACTS e SERVICES serem criados,

-- podemos testar a funcionamento do Service Broker

----------------------------------------------------------------------

Use BookStore

GO

DECLARE @Conv_Handler uniqueidentifier

DECLARE @OrderMsg xml;

BEGIN DIALOG CONVERSATION @Conv_Handler

FROM SERVICE [//SackConsulting/BookOrderService]

TO SERVICE '//SackConsulting/BookDistributionService'

ON CONTRACT [//SackConsulting/BookOrderContract];

SET @OrderMsg =

'<order id="2000" customer="33" orderdate="6/10/2008">

<LineItem ItemNumber="1" ISBN="1-59059-592-X" Quantity="9" />

</order>';

SEND ON CONVERSATION @Conv_Handler

MESSAGE TYPE [//SackConsulting/SendBookOrder]

(@OrderMsg);

-- Verificando a fila

USE BookDistribution

GO

SELECT  message_type_name

                               ,CAST(message_body as xml) [message]

                               ,queuing_order

                               ,conversation_handle

                               ,conversation_group_id

FROM dbo.BookDistributionQueue

-- Declarando variáveis para armazenar os dados da mensagem

DECLARE @Conv_Handler uniqueidentifier

DECLARE @Conv_Group uniqueidentifier

DECLARE @OrderMsg xml

DECLARE @TextResponseMsg varchar(8000)

DECLARE @ResponseMsg xml

DECLARE @OrderID int;

-- Retirando a mensagem da file e armazenando os valores nas variáveis

-- Usamos o comando RECEIVE para ler e "RETIRAR" os registros de uma fila

RECEIVE TOP(1) @OrderMsg = message_body,

                @Conv_Handler = conversation_handle,

                @Conv_Group = conversation_group_id

FROM dbo.BookDistributionQueue;

-- Inserindo os valores das variáveis na tabela

INSERT dbo.BookOrderReceived (conversation_handle, conversation_group_id, message_body)

VALUES (@Conv_Handler,@Conv_Group, @OrderMsg)

-- Usando XQuery com a mensagem recebida para extrair o ID do pedido, para

-- validar a mensagem recebida

SELECT @OrderID = @OrderMsg.value('(/order/@id)[1]', 'int')

SELECT @TextResponseMsg = '<orderreceived id= "' + CAST(@OrderID as varchar(10)) + '"/>';

SELECT @ResponseMsg = CAST(@TextResponseMsg as xml);

-- Enviando a confirmação para o initiator, usando um conversation handle existente

SEND ON CONVERSATION @Conv_Handler

MESSAGE TYPE [//SackConsulting/BookOrderReceived]

(@ResponseMsg );

-- Enviando a confirmação

USE BookStore

GO

DECLARE @Conv_Handler uniqueidentifier

DECLARE @Conv_Group uniqueidentifier

DECLARE @OrderMsg xml

DECLARE @TextResponseMsg varchar(8000);

RECEIVE TOP(1) @Conv_Handler = conversation_handle,

                @OrderMsg = message_body

FROM dbo.BookStoreQueue

INSERT dbo.BookOrderConfirmation

(conversation_handle, message_body)

VALUES (@Conv_Handler, @OrderMsg)

END CONVERSATION @Conv_Handler;

GO

-- Terminando a conversation

USE BookDistribution

GO

DECLARE @Conv_Handler uniqueidentifier

DECLARE @Conv_Group uniqueidentifier

DECLARE @OrderMsg xml

DECLARE @message_type_name nvarchar(256);

RECEIVE TOP(1) @Conv_Handler = conversation_handle,

                @OrderMsg = message_body,

                @message_type_name = message_type_name

FROM dbo.BookDistributionQueue

-- Ambos (initiator e target) devem terminar a conversation

IF @message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'

BEGIN

                END CONVERSATION @Conv_Handler;

END

SELECT state_desc, conversation_handle

FROM sys.conversation_endpoints

SELECT * FROM BookDistribution.dbo.BookOrderReceived

SELECT * FROM BookStore.dbo.BookOrderConfirmation

--TESTE03

----------------------------------------------------------------------

-- Depois das MESSAGES, QUEUES, CONTRACTS e SERVICES serem criados,

-- podemos testar a funcionamento do Service Broker

----------------------------------------------------------------------

Use BookStore

GO

DECLARE @Conv_Handler uniqueidentifier

DECLARE @OrderMsg xml;

BEGIN DIALOG CONVERSATION @Conv_Handler

FROM SERVICE [//SackConsulting/BookOrderService]

TO SERVICE '//SackConsulting/BookDistributionService'

ON CONTRACT [//SackConsulting/BookOrderContract];

SET @OrderMsg =

'<order id="3000" customer="44" orderdate="6/11/2008">

<LineItem ItemNumber="1" ISBN="1-99999-999-9" Quantity="91" />

</order>';

SEND ON CONVERSATION @Conv_Handler

MESSAGE TYPE [//SackConsulting/SendBookOrder]

(@OrderMsg);

-- Verificando a fila

USE BookDistribution

GO

SELECT  message_type_name

                               ,CAST(message_body as xml) [message]

                               ,queuing_order

                               ,conversation_handle

                               ,conversation_group_id

FROM dbo.BookDistributionQueue

-- Declarando variáveis para armazenar os dados da mensagem

DECLARE @Conv_Handler uniqueidentifier

DECLARE @Conv_Group uniqueidentifier

DECLARE @OrderMsg xml

DECLARE @TextResponseMsg varchar(8000)

DECLARE @ResponseMsg xml

DECLARE @OrderID int;

-- Retirando a mensagem da file e armazenando os valores nas variáveis

-- Usamos o comando RECEIVE para ler e "RETIRAR" os registros de uma fila

RECEIVE TOP(1) @OrderMsg = message_body,

                @Conv_Handler = conversation_handle,

                @Conv_Group = conversation_group_id

FROM dbo.BookDistributionQueue;

-- Inserindo os valores das variáveis na tabela

INSERT dbo.BookOrderReceived (conversation_handle, conversation_group_id, message_body)

VALUES (@Conv_Handler,@Conv_Group, @OrderMsg)

-- Usando XQuery com a mensagem recebida para extrair o ID do pedido, para

-- validar a mensagem recebida

SELECT @OrderID = @OrderMsg.value('(/order/@id)[1]', 'int')

SELECT @TextResponseMsg = '<orderreceived id= "' + CAST(@OrderID as varchar(10)) + '"/>';

SELECT @ResponseMsg = CAST(@TextResponseMsg as xml);

-- Enviando a confirmação para o initiator, usando um conversation handle existente

SEND ON CONVERSATION @Conv_Handler

MESSAGE TYPE [//SackConsulting/BookOrderReceived]

(@ResponseMsg );

-- Enviando a confirmação

USE BookStore

GO

DECLARE @Conv_Handler uniqueidentifier

DECLARE @Conv_Group uniqueidentifier

DECLARE @OrderMsg xml

DECLARE @TextResponseMsg varchar(8000);

RECEIVE TOP(1) @Conv_Handler = conversation_handle,

                @OrderMsg = message_body

FROM dbo.BookStoreQueue

INSERT dbo.BookOrderConfirmation

(conversation_handle, message_body)

VALUES (@Conv_Handler, @OrderMsg)

END CONVERSATION @Conv_Handler;

GO

-- Terminando a conversation

USE BookDistribution

GO

DECLARE @Conv_Handler uniqueidentifier

DECLARE @Conv_Group uniqueidentifier

DECLARE @OrderMsg xml

DECLARE @message_type_name nvarchar(256);

RECEIVE TOP(1) @Conv_Handler = conversation_handle,

                @OrderMsg = message_body,

                @message_type_name = message_type_name

FROM dbo.BookDistributionQueue

-- Ambos (initiator e target) devem terminar a conversation

IF @message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'

BEGIN

                END CONVERSATION @Conv_Handler;

END

SELECT state_desc, conversation_handle

FROM sys.conversation_endpoints

SELECT * FROM BookDistribution.dbo.BookOrderReceived

SELECT * FROM BookStore.dbo.BookOrderConfirmation

--Criando a SP

USE BookDistribution

GO

CREATE PROCEDURE dbo.usp_SB_ReceiveOrders

AS

DECLARE @Conv_Handler uniqueidentifier

DECLARE @Conv_Group uniqueidentifier

DECLARE @OrderMsg xml

DECLARE @TextResponseMsg varchar(8000)

DECLARE @ResponseMsg xml

DECLARE @Message_Type_Name nvarchar(256);

DECLARE @OrderID int;

-- XACT_ABORT automatically rolls back the transaction when a run-time error occurs

SET XACT_ABORT ON

BEGIN TRAN;

                RECEIVE TOP(1)               @OrderMsg = message_body,

                                                                              @Conv_Handler = conversation_handle,

                                                                              @Conv_Group = conversation_group_id,

                                                                              @Message_Type_Name = message_type_name

                FROM dbo.BookDistributionQueue;

                IF @Message_Type_Name = '//SackConsulting/SendBookOrder'

                BEGIN

                               INSERT dbo.BookOrderReceived (conversation_handle, conversation_group_id, message_body)

                               VALUES (@Conv_Handler,@Conv_Group, @OrderMsg)

                               SELECT @OrderID = @OrderMsg.value('(/order/@id)[1]', 'int' )

                               SELECT @TextResponseMsg = '<orderreceived id= "' + CAST(@OrderID as varchar(10)) + '"/>';

                              

                               SELECT @ResponseMsg = CAST(@TextResponseMsg as xml);

                               SEND ON CONVERSATION @Conv_Handler

                               MESSAGE TYPE [//SackConsulting/BookOrderReceived]

                               (@ResponseMsg );

                END

                IF @Message_Type_Name = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'

                BEGIN

                               END CONVERSATION @Conv_Handler;

                END

COMMIT TRAN

GO

-- To bind our stored procedure to an existing queue, the ALTER QUEUE command is used:

ALTER QUEUE dbo.BookDistributionQueue

WITH ACTIVATION (STATUS = ON,

                PROCEDURE_NAME = dbo.usp_SB_ReceiveOrders,

                MAX_QUEUE_READERS = 2,

                EXECUTE AS SELF)

-- Testando a SP 01

Use BookStore

GO

DECLARE @Conv_Handler uniqueidentifier

DECLARE @OrderMsg xml;

BEGIN DIALOG CONVERSATION @conv_handler

FROM SERVICE [//SackConsulting/BookOrderService]

TO SERVICE '//SackConsulting/BookDistributionService'

ON CONTRACT [//SackConsulting/BookOrderContract];

SET @OrderMsg =

'<order id="3490" customer="29" orderdate="7/22/2005">

<LineItem ItemNumber="1" ISBN="1-59059-592-0" Quantity="2" />

</order>';

SEND ON CONVERSATION @Conv_Handler

MESSAGE TYPE [//SackConsulting/SendBookOrder]

(@OrderMsg);

SELECT conversation_handle, CAST(message_body as xml) message

FROM dbo.BookStoreQueue

SELECT * FROM BookDistribution.dbo.BookOrderReceived

SELECT * FROM BookStore.dbo.BookOrderConfirmation

-- Enviando a confirmação

USE BookStore

GO

DECLARE @Conv_Handler uniqueidentifier

DECLARE @Conv_Group uniqueidentifier

DECLARE @OrderMsg xml

DECLARE @TextResponseMsg varchar(8000);

RECEIVE TOP(1) @Conv_Handler = conversation_handle,

                @OrderMsg = message_body

FROM dbo.BookStoreQueue

INSERT dbo.BookOrderConfirmation

(conversation_handle, message_body)

VALUES (@Conv_Handler, @OrderMsg)

END CONVERSATION @Conv_Handler;

GO

SELECT * FROM BookDistribution.dbo.BookOrderReceived

SELECT * FROM BookStore.dbo.BookOrderConfirmation

-- Criando Job de Confirmação

-- Job para envio da confirmação

DECLARE @Received INT

DECLARE @Confirmation INT

SELECT @Received = COUNT(*) FROM BookDistribution.dbo.BookOrderReceived

SELECT @Confirmation = COUNT(*) FROM BookStore.dbo.BookOrderConfirmation

IF @Received != @Confirmation

BEGIN

                DECLARE @Conv_Handler uniqueidentifier

                DECLARE @Conv_Group uniqueidentifier

                DECLARE @OrderMsg xml

                DECLARE @TextResponseMsg varchar(8000);

                RECEIVE TOP(1) @Conv_Handler = conversation_handle,

                               @OrderMsg = message_body

                FROM dbo.BookStoreQueue

                INSERT dbo.BookOrderConfirmation

                (conversation_handle, message_body)

                VALUES (@Conv_Handler, @OrderMsg)

                END CONVERSATION @Conv_Handler;

END

-- Configurar para executar a cada minuto

-- Occurs every day every 1 minute(s) between 00:00:00 and 23:59:59. Schedule

-- will be used starting on 27/06/2008.

--Testando a SP 2

Use BookStore

GO

-- Pedido 01

DECLARE @Conv_Handler uniqueidentifier

DECLARE @OrderMsg xml;

BEGIN DIALOG CONVERSATION @conv_handler

FROM SERVICE [//SackConsulting/BookOrderService]

TO SERVICE '//SackConsulting/BookDistributionService'

ON CONTRACT [//SackConsulting/BookOrderContract];

SET @OrderMsg =

'<order id="1111" customer="91" orderdate="7/22/2005">

<LineItem ItemNumber="1" ISBN="1-59059-592-0" Quantity="2" />

</order>';

SEND ON CONVERSATION @Conv_Handler

MESSAGE TYPE [//SackConsulting/SendBookOrder]

(@OrderMsg);

-- Pedido 02

BEGIN DIALOG CONVERSATION @conv_handler

FROM SERVICE [//SackConsulting/BookOrderService]

TO SERVICE '//SackConsulting/BookDistributionService'

ON CONTRACT [//SackConsulting/BookOrderContract];

SET @OrderMsg =

'<order id="2222" customer="92" orderdate="7/22/2005">

<LineItem ItemNumber="1" ISBN="1-59059-592-0" Quantity="2" />

</order>';

SEND ON CONVERSATION @Conv_Handler

MESSAGE TYPE [//SackConsulting/SendBookOrder]

(@OrderMsg);

--Job Script

USE [msdb]

GO

/****** Object:  Job [Service Broker Confirmation]    Script Date: 06/27/2008 14:33:44 ******/

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 06/27/2008 14:33:44 ******/

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Service Broker Confirmation',

                               @enabled=1,

                               @notify_level_eventlog=0,

                               @notify_level_email=0,

                               @notify_level_netsend=0,

                               @notify_level_page=0,

                               @delete_level=0,

                               @description=N'Service Broker Confirmation',

                               @category_name=N'[Uncategorized (Local)]',

                               @owner_login_name=N'sa', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object:  Step [Service Broker Confirmation]    Script Date: 06/27/2008 14:33:44 ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Service Broker Confirmation',

                               @step_id=1,

                               @cmdexec_success_code=0,

                               @on_success_action=1,

                               @on_success_step_id=0,

                               @on_fail_action=2,

                               @on_fail_step_id=0,

                               @retry_attempts=0,

                               @retry_interval=0,

                               @os_run_priority=0, @subsystem=N'TSQL',

                               @command=N'-- Job para envio da confirmação

DECLARE @Received INT

DECLARE @Confirmation INT

SELECT @Received = COUNT(*) FROM BookDistribution.dbo.BookOrderReceived

SELECT @Confirmation = COUNT(*) FROM BookStore.dbo.BookOrderConfirmation

IF @Received != @Confirmation

BEGIN

                DECLARE @Conv_Handler uniqueidentifier

                DECLARE @Conv_Group uniqueidentifier

                DECLARE @OrderMsg xml

                DECLARE @TextResponseMsg varchar(8000);

                RECEIVE TOP(1) @Conv_Handler = conversation_handle,

                               @OrderMsg = message_body

                FROM dbo.BookStoreQueue

                INSERT dbo.BookOrderConfirmation

                (conversation_handle, message_body)

                VALUES (@Conv_Handler, @OrderMsg)

                END CONVERSATION @Conv_Handler;

END',

                               @database_name=N'BookStore',

                               @flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Service Broker Confirmation',

                               @enabled=1,

                               @freq_type=4,

                               @freq_interval=1,

                               @freq_subday_type=4,

                               @freq_subday_interval=1,

                               @freq_relative_interval=0,

                               @freq_recurrence_factor=0,

                               @active_start_date=20080627,

                               @active_end_date=99991231,

                               @active_start_time=0,

                               @active_end_time=235959

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

                IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave: