I swear I’ve had some gremlins or something in my system. This latest issue has me perplexed.
We have a Crystal Report that would group our Resource Groups by Start Date. This has been working well as long as I have been here. Yesterday, we go to run the report and it just continues to load.
It then caused Vantage to hang and Vantage would only run once we killed the report. Running the Crystal Report directly from Crystal Report also hangs Vantage.
Any ideas on what the problem could be? I’m thinking there might be corrupt data in the database? Any other thoughts?
Are you able to write a BAQ duplicating your query in CR? Perhaps see any offending data. What’s your CR query look like? In Developer, go to Database --> Show SQL Query.
It caused Vantage to hang? Meaning you had to restart the server / appservers?
I struggle with writing reports. I doubt I would be able to duplicate it easily.
By Vantage hanging, I mean it was unresponsive until I closed out of Crystal. The same thing happens if we run the Crystal in Vantage. The program will go unresponsive and I have to close it.
I was able to get the CR Query. It is:
SELECT “joboper”.“jobcomplete”, “joboper”.“opcomplete”, “orderhed”.“openorder”, “orderrel”.“partnum”, “jobopdtl”.“resourcegrpid”, “joboper”.“estprodhours”, “joboper”.“startdate”, “jobhead”.“jobnum”, “orderdtl”.“linedesc”, “jobhead”.“partdescription”
FROM (((((“mfgsys803”.“dbo”.“jobhead” “jobhead” INNER JOIN “mfgsys803”.“dbo”.“jobprod” “jobprod” ON “jobhead”.“jobnum”=“jobprod”.“jobnum”) INNER JOIN “mfgsys803”.“dbo”.“joboper” “joboper” ON “jobprod”.“jobnum”=“joboper”.“jobnum”) INNER JOIN “mfgsys803”.“dbo”.“orderrel” “orderrel” ON ((“jobprod”.“ordernum”=“orderrel”.“ordernum”) AND (“jobprod”.“orderline”=“orderrel”.“orderline”)) AND (“jobprod”.“orderrelnum”=“orderrel”.“orderrelnum”)) INNER JOIN “mfgsys803”.“dbo”.“orderdtl” “orderdtl” ON (“orderrel”.“ordernum”=“orderdtl”.“ordernum”) AND (“orderrel”.“orderline”=“orderdtl”.“orderline”)) INNER JOIN “mfgsys803”.“dbo”.“orderhed” “orderhed” ON “orderdtl”.“ordernum”=“orderhed”.“ordernum”) INNER JOIN “mfgsys803”.“dbo”.“jobopdtl” “jobopdtl” ON ((“joboper”.“jobnum”=“jobopdtl”.“jobnum”) AND (“joboper”.“assemblyseq”=“jobopdtl”.“assemblyseq”)) AND (“joboper”.“oprseq”=“jobopdtl”.“oprseq”)
WHERE “jobopdtl”.“resourcegrpid”<>’’ AND “orderhed”.“openorder”=1 AND “joboper”.“opcomplete”=0 AND “joboper”.“jobcomplete”=0 AND (“joboper”.“startdate”>={ts ‘2018-12-28 00:00:00’} AND “joboper”.“startdate”<{ts ‘2019-04-19 00:00:00’})
Yes, based on the current date. Here’s the record selection formula:
{jobopdtl.resourcegrpid} <> “” and
{orderhed.openorder} = 1 and
{joboper.opcomplete} = 0 and
{joboper.jobcomplete} = 0 and
{joboper.startdate} <= today + 49 and
{joboper.startdate} in DateAdd(‘m’, -2, CurrentDate) to DateAdd(‘m’, 4, CurrentDate)
So it definitely sounds like you have some record locking occurring via/from ODBC. Have you cycled your Appservers after this occurrence when Vantage locked up? I’ve seen ODBC locks stick in Progress until you cycle the Appservers.
Also, I’m assuming you have a TEST environment handy. If so, do a Save As on your Crystal Report to save a version for hitting your Test environment. Create a new System DSN in Windows ODBC to point to your TEST environment. Change your Crystal Report’s data source to point to the new ODBC DSN. Now you can blow up TEST instead of Production while troubleshooting.
Your query looks fine and it obviously has worked just fine for years.
Also, make sure your all of your users’ ODBC DSN properties have “Default Isolation Level” set to Read Uncommitted. With this selected, ODBC queries do not lock any records; and since you’re hitting so many tables in your query this setting is important to mitigating locks.
So, I began playing with date ranges in the report and still had some trouble. I then looked at some of the lines of code and figured there’d be no harm in trying to have it ignore certain lines. I told it to ignore the last line
{joboper.startdate} in DateAdd(‘m’, -2, CurrentDate) to DateAdd(‘m’, 4, CurrentDate)
and it worked at the full date range.
Can anyone help me with what that line means? If it is important, is there another way to code it?
Joe, that line of the query is the Where Clause that specifies a dynamic date range. All JobOper records with a Start Date from 2 Months Ago until 4 Months From Today will be included in the result set.
Vic, we’ve restarted the servers multiple times since this has happened. Still the same results. The only thing that seems to allow the report to run is to remove that line of code. When that line of code is in the report, previewing the information in Crystal Report seems to hang with it showing “Accessing Database” at the bottom left corner.
I’ve tried to change the numbers and it still does it. It seems to run fine without it.
If I remove that line, will the other line of code " {joboper.startdate} <= today + 49 and" still select from today until 49 days out or does it need that bottom line of code?
