No (or missing) Data on Tracking Number Report – Stuck File

Posted in and tagged as , , .

First, check to see if the task ran on the .166.  In task scheduler, look at last run date for the “UPS Tracking Import” task.  If it failed, this might be a different issue altogether.

If it didn’t fail, check for errors in the log, especially something like this one:

“file: C:\EOS\Client\Trophy Nut\TNOS Shipment Details\Shipment-WORLDSHIP1-20171211153122276.xml error: Unexpected end of file while parsing Name has occurred. Line 124193, position 14.”

use TrophyNutOrders

select *
from Log l
where l.Message like ‘%file: C:\EOS\Client\Trophy Nut\TNOS Shipment Details\Shipment-WORLDSHIP%’
order by l.Id desc

This would indicate a file is stuck, especially if today’s file is in the archive and this file has an older date.  Usually we don’t catch this issue right away and some time goes by with the stuck file being retried daily and new files being archived as if successfully processed, but not really ever completing the entire process.

There might also be records in the UPSDump table when that should usually be empty after records are processed each morning.  (It gets truncated in [UPSDump_Export] stored procedure.)

Run the select in [UPSDump_Export] (don’t run the INSERT portion!) to see if records are stuck in UPSDump.  That’s another clue an error occurred while processing a worldship file and did not complete the process.  Looks like this, but may have changed after writing up this KB article:

SELECT
–CouncilID
CAST(SUBSTRING(t.REF1, 2, 3) AS INT) AS CouncilID
–SUID
, CASE SUBSTRING(t.REF1, 1, 1)–first character tells what type of tracking number it is. Council, Service or Region
WHEN ‘c’ THEN NULL
WHEN ‘s’ THEN CAST(SUBSTRING(t.REF1, 5, 6) AS INT)
WHEN ‘r’ THEN NULL
END AS SUID
–RegionID
, CASE SUBSTRING(t.REF1, 1, 1)–first character tells what type of tracking number it is. Council, Service or Region
WHEN ‘c’ THEN NULL
WHEN ‘s’ THEN NULL
WHEN ‘r’ THEN CAST(SUBSTRING(t.REF1, 5, 4) AS INT)
END AS RegionID
–OrderID is not always present, could be null
, CASE SUBSTRING(t.REF1, 1, 1)–first character tells what type of tracking number it is. Council, Service or Region
WHEN ‘c’ THEN
CASE ISNUMERIC(SUBSTRING(t.REF1, 5, (LEN(t.REF1)-4)))
WHEN 1 THEN
CAST(SUBSTRING(t.REF1, 5, (LEN(t.REF1)-4)) AS INT)
ELSE NULL
END
WHEN ‘s’ THEN
CASE ISNUMERIC(SUBSTRING(t.REF1, 11, (LEN(t.REF1)-10)))
WHEN 1 THEN
CAST(SUBSTRING(t.REF1, 11, (LEN(t.REF1)-10)) AS INT)
ELSE NULL
END

WHEN ‘r’ THEN
CASE ISNUMERIC(SUBSTRING(t.REF1, 9, (LEN(t.REF1)-8)))
WHEN 1 THEN
CAST(SUBSTRING(t.REF1, 9, (LEN(t.REF1)-8)) AS INT)
ELSE NULL
END
END AS OrderID
, (SELECT ActiveYear from SystemSettings where SystemSettingsID = 1) as ActiveYear
, t.TrackingNumber
, t.VoidIndicator
, t.TrackingService
FROM UPSDump t
WHERE SUBSTRING(t.REF1, 1, 1) IN (‘c’,’s’,’r’) –only import rows with these prefixes
AND ISNUMERIC(SUBSTRING(t.REF1, 2, LEN(t.REF1)-1)) = 1 –remaining portion of ref1 needs to be numeric
ORDER BY UPSDumpID

Now go out to the .166 server and browse to the folder containing the possibly stuck file.

It would be something like:
C:\EOS\Client\Trophy Nut\TNOS Shipment Details\Shipment-WORLDSHIP*.xml

You can save off then delete this file from this directory and rerun the task immediately.  Check to see that Task Scheduler says it succeeded.

Then recheck the Tracking Number report, more data should appear on it.

 

As for what to do with the saved off/deleted file… The one I dealt with yesterday was from mid-December 2017 and it’s now July 2018, so it was so old we just deleted it and did not worry about it.  But if it’s a newer file, might need to consult Mark to see if it needs fixed and reprocessed.  The file from yesterday was an incomplete “cut off” xml file.  It might be possible to fix the file by ending it at the last proper place to end it, xml-wise, then replace it and reprocess it.  But check to see if that’s needed.

0