Pete Wirfs

SQL to find jobs with oversized reports

Discussion created by Pete Wirfs on Feb 10, 2016
Latest reply on Apr 11, 2016 by RonGates608562
For sharing purposes.

We discovered that if you send a really large report into UC4 that exceeds the MAX_REPORT_SIZE in the UC_HOSTCHAR_DEFAULT variable, it doesn't tell you but it will silently discard the middle part of the report.  It keeps a couple of the last pages so you will still see your control totals at the bottom.  Rather clever, actually, but I sure would have liked to have known this was happening.

This led us to need to know what reports this has been happening to.  Here is the SQL statement I used to find those reports;

select oh_name as job_name      --, ah_timestamp1 as activation_time      , ah_timestamp2 as start_time      --, ah_runtime as runtime      , CONVERT(varchar, DATEADD(ms, ah_runtime * 1000, 0), 114) as runtime      , count(*) from uc4.dbo.rt    , uc4.dbo.ah    , uc4.dbo.oh where ah_timestamp1 > cast('20160201 00:00:00:000' as DATETIME) -- how far back in time and   ah_oh_idnr = oh_idnr and   rt_ah_idnr = ah_idnr and   oh_name <> 'APPUTILP' -- target agent group by oh_name, ah_timestamp1, ah_timestamp2, ah_runtime having count(*) > 1500 -- MAX_REPORT_SIZE in UC_HOSTCHAR_DEFAULT order by 2;

Outcomes