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