Close

ESB Portal Fault tabel uitbreiden

Stel dat je bij een exception extra velden wilt loggen en je wilt niet overal in BizTalk de code voor het inschieten van een exception aanpassen, dan kun je bij het inserten van de faultmessage via xpath extra velden uit het foutbericht selecteren. In dit geval gaat het om MessageType, BusinessTransactionId en BusinessEntityId. Na toevoegen van de velden in tabel Fault, kun je usp_Insert_Message als volgt aanpassen. De integrale code van de stored procedure is opgenomen. Let op dat MessageData als datatype nvarchar (unicode) wordt doorgegeven. Dit zorgt ervoor dat de stored procedure om kan gaan met speciale tekens (diakrieten) in de xml.

/**************************************************************************** Name: usp_insert_Message.sql Description:  Inserts a message into the Message table. *****************************************************************************/
CREATE PROCEDURE [dbo].[usp_insert_Message] (
@MessageID   uniqueidentifier  ,
@FaultID   uniqueidentifier  ,
@NativeMessageID varchar(256)  ,
@ContentType  varchar(128)  ,
@MessageName  varchar(50)  ,
@InterchangeID  varchar(256)  ,
@RoutingUrl  varchar(50)  ,
@MessageData  nvarchar(max)       ,
@Debug    bit = 1
)

AS

SET NOCOUNT ON;

— Bootstrap
DECLARE @DebugPrefix    varchar(10);
DECLARE @ErrorCode      int;
DECLARE @docHandle  int;

SET @ErrorCode = 0;
SET @DebugPrefix = ‘>>> DEBUG: ‘;

IF (@ContentType = ‘text/plain’ AND LEFT(@MessageData,1) = ‘<‘)
BEGIN
SET @ContentType = ‘text/xml’
END

— Test harness
/*
DECLARE @Debug           bit;
DECLARE @NativeMessageID varchar(128)
DECLARE @MessageID   uniqueidentifier
DECLARE @FaultID   uniqueidentifier
DECLARE @ContentType  varchar(128)
DECLARE @MessageName  varchar(50)
DECLARE @InterchangeID  varchar(50)
DECLARE @RoutingUrl   varchar(50)
DECLARE @MessageData  varchar(max)
DECLARE @InsertedDate  datetime
DECLARE @InsertedBy   varchar(50)
DECLARE @ModifiedDate  datetime
DECLARE @ModifiedBy   varchar(50)

SELECT   @MessageID = NEWID(),@FaultID = NEWID()
,@ContentType   ,@MessageName  ,@InterchangeID   ,@RoutingUrl   ,@MessageData   ,@InsertedDate
,@InsertedBy   ,@ModifiedDate   ,@ModifiedBy  ,@Debug = 1;
*/

— Print parameters
/*
IF (@Debug > 0)
BEGIN
PRINT @DebugPrefix + ‘@MessageID = ‘ + CAST(@MessageID AS varchar(38));
PRINT @DebugPrefix + ‘@FaultID = ‘ + CAST(@FaultID AS varchar(38));
PRINT @DebugPrefix + ‘@ContentType = ‘ + CAST(@ContentType AS varchar(500));
PRINT @DebugPrefix + ‘@MessageName = ‘ + CAST(@MessageName AS varchar(500));  PRINT @DebugPrefix + ‘@InterchangeID = ‘ + CAST(@InterchangeID AS varchar(38));
END;
*/

DECLARE @messageType nvarchar(50)
DECLARE @businessEntityID nvarchar(200)
DECLARE @businessTransactionID varchar(38)
DECLARE @sourceSystemCode varchar(50)
DECLARE @administrationID varchar(50)
DECLARE @messageDateTime datetime
DECLARE @xmlDocument XML
DECLARE @validXML as bit

SET XACT_ABORT OFF

— create xml document
BEGIN TRY
EXEC sp_xml_preparedocument @docHandle OUTPUT, @MessageData;
EXEC sp_xml_removedocument @docHandle;

SELECT @xmlDocument = @MessageData   SET @validXML = 1  END TRY  BEGIN CATCH   SET @validXML = 0
END

CATCH
— check the type of message
IF (@validXML = 1)
BEGIN
WITH XMLNAMESPACES ( ‘http://schemas.rfsholding.com/rid/GenericMessage/v1.0’ as nsGen   )
SELECT @messageType = @xmlDocument.value(‘/nsGen:GenericMessage[1]/Header[1]/MessageType
[1]’, ‘nvarchar(50)’)    , @businessEntityID = @xmlDocument.value(‘/nsGen:GenericMessage[1]/Header
[1]/BusinessEntityID[1]’, ‘nvarchar(200)’)    , @businessTransactionID = @xmlDocument.value
(‘/nsGen:GenericMessage[1]/Header[1]/BusinessTransactionID[1]’, ‘varchar(38)’)    ,
@sourceSystemCode = @xmlDocument.value(‘/nsGen:GenericMessage[1]/Header[1]/SourceSystemCode
[1]’, ‘varchar(50)’)    , @administrationID = @xmlDocument.value(‘/nsGen:GenericMessage[1]/Header
[1]/AdministrationID[1]’, ‘varchar(50)’)    , @messageDateTime = @xmlDocument.value
(‘/nsGen:GenericMessage[1]/Header[1]/MessageDateTime[1]’, ‘datetime’);
END
IF (@messageType = ‘ApplicationResponse’)
BEGIN
— The application response is send to the ESB Portal by the AX mediator to extract the list
— of AX errors from the message and store them in the FaultDetail table. The app response
— itself is not inserted in the Message table.

WITH XMLNAMESPACES
( ‘http://schemas.rfsholding.com/rid/GenericMessage/v1.0’ as nsGen
, ‘http://schemas.rfsholding.com/rid/ApplicationResponse/v1.0’ as rid
, ‘http://schemas.rfsholding.com/rid/CommonBasicComponents/v1.0’ as cbc
, ‘http://schemas.rfsholding.com/rid/ApplicationResponseAggregateComponents/v1.0’ as cac )

INSERT INTO dbo.FaultDetail (FaultID, ExceptionCode, ExceptionMessage)
SELECT @FaultID, StatusReasonCode, StatusReason
FROM
(
SELECT Statuses.StatusNode.value(‘./cbc:StatusReasonCode[1]’, ‘nvarchar(20)’) AS StatusReasonCode, Statuses.StatusNode.value(‘./cbc:StatusReason[1]’, ‘nvarchar(max)’) AS StatusReason, Statuses.StatusNode.value(‘./cbc:SequenceID[1]’, ‘int’) AS SequenceID
FROM  @xmlDocument.nodes(‘/nsGen:GenericMessage/Body/rid:ApplicationResponse/cac:DocumentResponse/cac:Response/cac:Status’)   AS Statuses(StatusNode)     ) AS errors
WHERE StatusReasonCode IS NOT NULL
ORDER BY SequenceID
END

ELSE

BEGIN
–Insert the error report
INSERT INTO dbo.Message(MessageID, FaultID, NativeMessageID, ContentType, MessageName, InterchangeID, RoutingUrl, MessageType, SourceSystemCode, MessageDateTime, AdministrationID, BusinessTransactionID, BusinessEntityID)
VALUES (               @MessageID,@FaultID,@NativeMessageID,@ContentType,@MessageName,@InterchangeID,@RoutingUrl,@messageType,@sourceSystemCode,@messageDateTime,@administrationID,@businessTransactionID,@businessEntityID)

–Insert the MessageData
INSERT INTO dbo.MessageData(MessageID, MessageData)
VALUES (@MessageID, @MessageData)

–Update the Fault with the MessageType of the first message
UPDATE dbo.Fault
SET MessageType = @messageType
WHERE FaultID = @FaultID   AND MessageType IS NULL

–Update the Fault with the businessTransactionID of the first message
UPDATE dbo.Fault
SET BusinessTransactionID = @businessTransactionID
WHERE FaultID = @FaultID   AND BusinessTransactionID IS NULL

–Update the Fault with the businessEntityID of the first message
UPDATE dbo.Fault
SET BusinessEntityID = @businessEntityID
WHERE FaultID = @FaultID   AND BusinessEntityID IS NULL  END      RETURN 0;

— Handles errors.
ERROR_HANDLER:
IF (@ErrorCode <> 0)
BEGIN
IF (@Debug > 0)
BEGIN
PRINT @DebugPrefix + ‘Error Code = ‘ + CAST(@ErrorCode AS varchar(10)) + ‘ occurred.’;
RETURN @ErrorCode
END

— Don’t return any evidence of what specific error occurred, i.e.
— we want to ward off potential hackers.
RETURN 1;
END;

SET NOCOUNT OFF;

GO