Have you ever needed to find out why a particular transaction failed to integrate with a Scribe based integration?
The obvious answer is to check the execution log. However, this is not as easy as seems when the only information you have in hand is the transaction number.
The first time I ran into this issue I was very surprised to realize that there is very limited search functionality within the execution log. As you can see in the screenshot below there are many failed transactions. This can be nearly impossible to isolate and obtain the error message for a particular transaction.
You could right click on each transition to open the xml message and grab the transaction number – that will take forever!
So what can you do?
The answer is to run a SQL query.
The execution log entries are stored in a table called SCRIBE.EXECUTIONLOG which is located in the ScribeInternal database. You will also need to join the SCRIBE.TRANSACTIONERRORS table since this will give you the actual error message that you can use in your troubleshooting. You will also need to enter the actual name of the integration dts file.
So here is the query that you are going to need to run against the ScribeInternal database. In the example I am using below ‘25404’ is the invoice number. You can actually query on any information you know that would be contained in the XML message.
select te.ERRORMESSAGE,ex.FATALERRORMESSAGE,* from SCRIBE.EXECUTIONLOG as ex
inner join SCRIBE.TRANSACTIONERRORS as te
on ex.EXECID = te.EXECID
WHERE JOBSPECNAME LIKE ‘%InvoiceToERP-SOP.dts’
and MESSAGE LIKE ‘%25404%’
After running the query you are now able to see the error message and know why the transaction failed.
If you need a copy of the xml for further troubleshooting you can use other information such as the starttime and endtime to find the transaction in the execution log.
I know that I use this query regularly so I hope that you find it useful as well. Should you need more information or tips on how to work with Scribe, please contact us at BDO Solutions.