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
