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