Service Broker

[KARAYA]

Buenas tardes compañeros...

alguien ha trabajado con Service Broker que me pueda colaborar un poco..

me han asignado realizar un desarrollo para almacenar un log desde difererentes sistemas, y la idea es desarrollar un ws y este a la vez con un service broker y que este se encargue del proceso de almacenamiento....

ya tengo un ejemplo,.... les envio el script... pero el campo "state_desc" de la vista "SELECT * FROM sys.conversation_endpoints" siempre queda en "CONVERSING".. que será lo que estoy haceindo mal.. muchas gracias por su ayuda....




CREATE TABLE tbl_LogConsulta

(
ID INT IDENTITY(1,1),
Fecha SMALLDATETIME,
Usuario varchar(50),
Sistema varchar(100),
IDCliente bigint

);


--CREATE MESSAGE TYPE [RecordSale] VALIDATION = NONE;
ALTER MESSAGE TYPE [RecordLog] VALIDATION = NONE;

--CREATE CONTRACT [SalesContract]
CREATE CONTRACT [LogContract]
(
[RecordLog] SENT BY INITIATOR
);

--CREATE QUEUE [SalesQueue];
CREATE QUEUE [LogQueue];

--CREATE SERVICE [SalesService] ON QUEUE [SalesQueue]([SalesContract]);
CREATE SERVICE [LogService] ON QUEUE [LogQueue]([LogContract]);

--CREATE QUEUE [RecordSalesQueue];
CREATE QUEUE [RecordLogQueue];

--CREATE SERVICE [RecordSalesService] ON QUEUE [RecordSalesQueue];
CREATE SERVICE [RecordLogService] ON QUEUE [RecordLogQueue];

----ALTER QUEUE [SalesQueue] WITH ACTIVATION
ALTER QUEUE [LogQueue] WITH ACTIVATION
(
STATUS = ON,
MAX_QUEUE_READERS = 5,
PROCEDURE_NAME = usp_RecordLogMessage,
EXECUTE AS OWNER

);


ALTER PROCEDURE usp_RecordLogMessage

AS

BEGIN

SET NOCOUNT ON;
DECLARE @Handle UNIQUEIDENTIFIER;
DECLARE @MessageType SYSNAME;
DECLARE @Message XML
DECLARE @Fecha SMALLDATETIME
DECLARE @Usuario VARCHAR(50)
DECLARE @Sistema VARCHAR(100)
DECLARE @IDCliente BIGINT;



RECEIVE TOP (1)
@Handle = conversation_handle,
@MessageType = message_type_name,
@Message = message_body
FROM [LogQueue];


IF(@Handle IS NOT NULL AND @Message IS NOT NULL)
BEGIN
SELECT @Fecha = CAST(CAST(@Message.query('/Params/Fecha/text()') AS NVARCHAR(MAX)) AS DATETIME)
SELECT @Usuario = CAST(CAST(@Message.query('/Params/Usuario/text()') AS NVARCHAR(MAX)) AS VARCHAR(50))
SELECT @Sistema = CAST(CAST(@Message.query('/Params/Sistema/text()') AS NVARCHAR(MAX)) AS VARCHAR(100))
SELECT @IDCliente = CAST(CAST(@Message.query('/Params/IDCliente/text()') AS NVARCHAR(MAX)) AS BIGINT)


INSERT INTO tbl_LogConsulta(Fecha ,Usuario ,Sistema, IDCliente )
VALUES(@Fecha,@Usuario,@Sistema,@IDCliente);
END

END

GO











ALTER PROCEDURE [dbo].[usp_SendLogInfo]
(

--@Fecha SMALLDATETIME,
@Usuario VARCHAR(50),
@Sistema VARCHAR(100),
@IDCliente BIGINT

)

AS

BEGIN

DECLARE @MessageBody XML
CREATE TABLE #ProcParams
(
Fecha SMALLDATETIME,
Usuario VARCHAR(50),
Sistema VARCHAR(100),
IDCliente BIGINT

)
INSERT INTO #ProcParams(Fecha,Usuario,Sistema,IDCliente)
VALUES(getdate(), @Usuario, @Sistema, @IDCliente)

SELECT @MessageBody = (SELECT * FROM #ProcParams FOR XML PATH ('Params'), TYPE);

DECLARE @Handle UNIQUEIDENTIFIER,
@ErrorSave INT,
@ErrorDesc NVARCHAR(100) ;

BEGIN TRANSACTION ;

BEGIN DIALOG @Handle
FROM SERVICE [RecordLogService]
TO SERVICE 'LogService'
ON CONTRACT [LogContract]
WITH ENCRYPTION = OFF, LIFETIME = 600;

SEND ON CONVERSATION @Handle
MESSAGE TYPE [RecordLog](@MessageBody);

SET @ErrorSave = @@ERROR ;

IF (@ErrorSave <> 0)
BEGIN
ROLLBACK TRANSACTION ;
SET @ErrorDesc = N'An error has occurred.' ;
END CONVERSATION @Handle
WITH ERROR = @ErrorSave DESCRIPTION = @ErrorDesc ;
END
ELSE

--END CONVERSATION @Handle WITH CLEANUP ;

COMMIT TRANSACTION ;

END


EXECUTE dbtemporal..usp_SendLogInfo 'DPINEDA ARRE','SACAS', 212122