Folks from a fellow team are trying to solve a sporadic problem where for some unknown reason their RosettaNet application sometimes fails submitting invoices to our EDI partner, which is OpenInvoice.
In the meanwhile they asked for help with finding the invoices for which all attempts to send them have failed. Below is a query that I wrote for them.
A few comments about how it works.
Our BizTalk configuration includes ESB Toolkit. If an application fails, the exception is recorded in the EsbExceptionDb database. Next, we use BizTalk Accelerator for RosettaNet (BTARN) for the electronic document exchange with our partners who support RosettaNet. The messages sent and received are stored in the BTARNDATA database. The query is a RIGHT JOIN; it find error records (exceptions) for which there is not any matching record among the successfully sent messages.
In the meanwhile they asked for help with finding the invoices for which all attempts to send them have failed. Below is a query that I wrote for them.
A few comments about how it works.
Our BizTalk configuration includes ESB Toolkit. If an application fails, the exception is recorded in the EsbExceptionDb database. Next, we use BizTalk Accelerator for RosettaNet (BTARN) for the electronic document exchange with our partners who support RosettaNet. The messages sent and received are stored in the BTARNDATA database. The query is a RIGHT JOIN; it find error records (exceptions) for which there is not any matching record among the successfully sent messages.
--the invoices that failed all sending attempts
WITH XMLNAMESPACES ('http://www.api.org/pidXML/v1.0' as pidx)
select failed.*
from (
select SI.Invoice.value('pidx:InvoiceNumber[1]', 'varchar(90)') as SuccessInvoiceNumber
from [BTARNDATA].[dbo].[MessagesFromLOB] lob
CROSS APPLY (select cast(lob.ServiceContent as xml)) as XmlContent(CData)
CROSS APPLY (select try_cast(XmlContent.CData.value('
declare default element namespace "http://schemas.microsoft.com/biztalk/btarn/2004/LOBMessage";
/ServiceContent[1]', 'varchar(max)') as xml)) as XmlInner(Msg)
CROSS APPLY XmlInner.Msg.nodes('/pidx:Invoice/pidx:InvoiceProperties') as SI(Invoice)) succeeded
right join (
select FailedInvoiceNumber, max(DateTime) as LastErrorTime
from (select
FI.Invoice.value('pidx:InvoiceNumber[1]', 'varchar(90)') as FailedInvoiceNumber,
f.DateTime
FROM [EsbExceptionDb].[dbo].[Fault] f
inner join [EsbExceptionDb].[dbo].[Message] m on f.FaultID = m.FaultID
inner join [EsbExceptionDb].[dbo].[MessageData] md on m.MessageID = md.MessageID
CROSS APPLY (select cast(md.MessageData as xml)) as XmlData(Msg)
CROSS APPLY XmlData.Msg.nodes('/pidx:Invoice/pidx:InvoiceProperties') as FI(Invoice)) allfailed
GROUP BY FailedInvoiceNumber) failed on succeeded.SuccessInvoiceNumber=failed.FailedInvoiceNumber
where succeeded.SuccessInvoiceNumber is null
