Skip to Content
0

What could make crystal reports create empty reports, at random

Oct 14, 2016 at 03:34 PM

95

avatar image

So this is the scoop. App written in c++/MFC, using crystal reports. There is a certain report that is mailed out periodically. Sometimes that report ends up with no records, even if records exist. If you print that same report to screen/paper, it prints fine. Ie through

m_Report->PrintToPrinter(nCopies,true,0,0);<br>

But

m_Report->Export(exportOpts);

Creates an empty report. If I get a copy of the customer's database I cannot duplicate. If customer tries the next day, it works? Database i MS SQL Server.

Any ideas?

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

9 Answers

Don Williams
Oct 14, 2016 at 03:35 PM
1

Hi Anders,

What SDK are you using?

Don

Share
10 |10000 characters needed characters left characters exceeded
Don Williams
Jan 31, 2017 at 02:39 PM
1

Hi Anders,

I changed the TAG to CR for VS.

SP 19 was just released, see if that helps.

https://wiki.scn.sap.com/wiki/display/BOBJ/Crystal+Reports%2C+Developer+for+Visual+Studio+Downloads

What data source are you using and who's DB client?

How many reports are being ran at the same time?

It could be Connection Pooling, too many open connections. Watch the DB Server also.

See if anything other logs are capturing the issue.

Don

Share
10 |10000 characters needed characters left characters exceeded
Anders Gustafsson Jan 02, 2017 at 08:36 AM
0

Sorry for the lateness of my reply. I use the NET SDK from C++. I could duplicate it briefly last Friday, but then it started working again :( saw nothing out of the ordinary in the debug log.

I have another clue though. It seems that Crystal renders differently when rendering to screen/paper or to a file. Saw that with another report. Perhaps that is a clue??

Share
10 |10000 characters needed characters left characters exceeded
Anders Gustafsson Jan 31, 2017 at 12:17 PM
0

So. I tried to get some traces today. Compared traces from customer's system (non-working) to mine (working). As I said before, this is intermittent. Retry the next day and it works. No other changes made:

In the non-working logs I see:

|7f6137df-3d9b-b124-d9bb-d383c169da4f|2017 01 31 13:21:00:498|+0200|==| | |Diagnostics| 7524|7564|| |0|0|0|0|CRPE.CRPEBackgroundThread|localhost:7524:7564.602:1|-|-|CRPE.CRPEBackgroundThread|localhost:7524:7564.602:1||||||||||||..\..\src\print\datasource\datasrc.cpp:2877,Begin reading from database,Start Time,"13:21:00"

|cccd4cf1-20ff-7b24-2834-55f766d5ba02|2017 01 31 13:21:00:498|+0200|==| | |Diagnostics| 7524|7564|| |0|0|0|0|CRPE.CRPEBackgroundThread|localhost:7524:7564.602:1|-|-|CRPE.CRPEBackgroundThread|localhost:7524:7564.602:1||||||||||||..\..\src\data\datainterface.cpp:3323,Opening database connections,Start Time,"13:21:00"

|8a84ca9e-68fb-2b84-8a9b-16f115246c27|2017 01 31 13:21:00:498|+0200|==| | |Diagnostics| 7524|7564|| |0|0|0|0|CRPE.CRPEBackgroundThread|localhost:7524:7564.602:1|-|-|CRPE.CRPEBackgroundThread|localhost:7524:7564.602:1||||||||||||..\..\src\data\datainterface.cpp:3324,Opening database connections,Number of tables in report,"8"

|10db16af-5474-4b84-6b80-5faf0307f14f|2017 01 31 13:21:00:498|+0200|==| | |Diagnostics| 7524|7564|| |0|0|0|0|CRPE.CRPEBackgroundThread|localhost:7524:7564.602:1|-|-|CRPE.CRPEBackgroundThread|localhost:7524:7564.602:1||||||||||||..\..\src\data\datainterface.cpp:3349,Opening database connections,Number of connections in report,"1"

|e82898fe-67e8-0c94-baca-10e978d35551|2017 01 31 13:21:00:498|+0200|==| | |Diagnostics| 7524|7564|| |0|0|0|0|CRPE.CRPEBackgroundThread|localhost:7524:7564.602:1|-|-|CRPE.CRPEBackgroundThread|localhost:7524:7564.602:1||||||||||||N/A:-1,Opening database connections,Elapsed Time,"0"

|54d77d3d-ac31-5004-4a9d-b41284a8b15f|2017 01 31 13:21:00:498|+0200|==| | |Diagnostics| 7524|7564|| |0|0|0|0|CRPE.CRPEBackgroundThread|localhost:7524:7564.602:1|-|-|CRPE.CRPEBackgroundThread|localhost:7524:7564.602:1||||||||||||..\..\src\data\databasequery.cpp:652,Start of OpenRowset,Start Time,"13:21:00"

|a4ef4481-9465-1bc4-f908-5a9d164e4a77|2017 01 31 13:21:00:498|+0200|==| | |Diagnostics| 7524|7564|| |0|0|0|0|CRPE.CRPEBackgroundThread|localhost:7524:7564.602:1|-|-|CRPE.CRPEBackgroundThread|localhost:7524:7564.602:1||||||||||||..\..\src\data\databasequery.cpp:714,Start of OpenRowset,run sql with asynchronous,"FALSE"

|1ff90384-ccc8-c574-4b51-dfbdff38f2d2|2017 01 31 13:21:00:574|+0200|==| | |Diagnostics| 7524|7564|| |0|0|0|0|CRPE.CRPEBackgroundThread|localhost:7524:7564.602:1|-|-|CRPE.CRPEBackgroundThread|localhost:7524:7564.602:1||||||||||||N/A:-1,Start of OpenRowset,Elapsed Time,"72"

|c53449e0-a5d7-a824-7853-21e8daf8f988|2017 01 31 13:21:00:576|+0200|==| | |Diagnostics| 7524|7564|| |0|0|0|0|CRPE.CRPEBackgroundThread|localhost:7524:7564.602:1|-|-|CRPE.CRPEBackgroundThread|localhost:7524:7564.602:1||||||||||||N/A:-1,Begin reading from database,Elapsed Time,"76"

|044d7949-367e-c3a4-487e-4a01100cf03c|2017 01 31 13:21:00:576|+0200|==| | |Diagnostics| 7524|7564|| |0|0|0|0|CRPE.CRPEBackgroundThread|localhost:7524:7564.602:1|-|-|CRPE.CRPEBackgroundThread|localhost:7524:7564.602:1||||||||||||..\..\src\print\datasource\datasrc.cpp:3057,End reading from database,Start Time,"13:21:00"

|f8541d02-4da0-70f4-2883-17e7800ce91d|2017 01 31 13:21:00:576|+0200|==| | |Diagnostics| 7524|7564|| |0|0|0|0|CRPE.CRPEBackgroundThread|localhost:7524:7564.602:1|-|-|CRPE.CRPEBackgroundThread|localhost:7524:7564.602:1||||||||||||..\..\src\print\datasource\datasrc.cpp:3058,End reading from database,Total records read,"0"

|d606080c-a968-8a74-9aa3-d224ea91e2eb|2017 01 31 13:21:00:576|+0200|==| | |Diagnostics| 7524|7564|| |0|0|0|0|CRPE.CRPEBackgroundThread|localhost:7524:7564.602:1|-|-|CRPE.CRPEBackgroundThread|localhost:7524:7564.602:1||||||||||||N/A:-1,End reading from database,Elapsed Time,"0"

Traces from working system are identical, apart from a non-zero return. Ie when the error happens I see zero records returned from all queries. Any ideas?

Share
10 |10000 characters needed characters left characters exceeded
Anders Gustafsson Feb 01, 2017 at 09:15 AM
0

Sure. Will try SP19. Sorry about the tagging :) Seems that every time I revisit this forum, the UI has changed :)

Datasource is ODBC on Windows and client is "Sql Server"

The routine prints out payslips as PDF and is called once for every employee. When it fails, it fails for all. If I subsequently try to print the exact same report to screen, it works every time. When it fails, it sometimes works to print from another PC och just wait a day and try again.

What exactly should I watch on the DB Server?

What "other logs" should I check?

Is there any way to see more detail in the log? Ie All I see is that it queries and returns no results. This could either be that the query is bad or the connection. Is there any way to see the actual SQL? I tried an SQL Trace from ODBCAD32, but found nothing worthwhile there.

Share
10 |10000 characters needed characters left characters exceeded
Anders Gustafsson Feb 03, 2017 at 09:10 AM
0

Just FYI. I have given the database people instructions how to get a trace on the SQL Server, using SQL Profiler. One that shows the actual SQL Queries, what database it is hitting and the returned rowcount. That should help us narrow this down.

Share
10 |10000 characters needed characters left characters exceeded
Don Williams
Mar 21, 2017 at 05:55 PM
0

Hi Anders,

It could be Client buffering or possibly too many connects open.

CRLogger may have more info but be patient, it does add a lot of overhead to the app while logging.

Don

Share
10 |10000 characters needed characters left characters exceeded
Anders Gustafsson Apr 13, 2017 at 01:50 PM
0

Just a follow up. Still waiting for it to happen and the database people to provide logs.

Share
10 |10000 characters needed characters left characters exceeded
Anders Gustafsson Apr 28, 2017 at 12:39 PM
0

OK. We have progress. I managed to get some logs where it happens. The actual SQL query is below and the offender is a left outer join, LEFT OUTER JOIN "LP_HISTORIK_PERIOD" "LP_HISTORIK_PERIOD" ON "LP_LONEHANDELSER"."Personnummer"="LP_HISTORIK_PERIOD"."Personnummer"). In my world that would return all the records from LP_LONEHANDELSER and corresponding records from LP_HISTORIK_PERIOD or NULL values for those columns where records in LP_HISTORIK_PERIOD are nonexistent. This does not happen, zero records are returned. Any ideas why? Oddly, if I run the same report to printer/screen first and then export, then it works. It is almost as if the SQL Server itself if optimising away my data??

Query below, I added carriage returns for legibility. The error is 100% repeatable when it happens. Ie I was able to plug the query text into an SQL Query tool and duplicate outside Crystal. No records were returned.

SELECT "LP_LONEHANDELSER"."Antal", "LP_LONESLAG"."Lonesummanummer", "LP_LONEHANDELSER"."Summa", "LP_LONESLAG"."Nummer", "LP_LONESLAG"."Namn", 
"LP_ARSSTATISTIK"."S03", "LP_ARSSTATISTIK"."S04", "LP_ARSSTATISTIK"."S02", "LP_ARSSTATISTIK"."S07", "LP_ARSSTATISTIK"."S10", "LP_ARSSTATISTIK"."S38", 
"LP_ARSSTATISTIK"."S49", "LP_ARSSTATISTIK"."S33", "LP_ARSSTATISTIK"."S35", "LP_ARSSTATISTIK"."S08", "LP_ARSSTATISTIK"."S09", "LP_PERSONAL"."Namn", 
"LP_PERSONAL"."Adress", "LP_PERSONAL"."Postadress", "LP_PERSONAL"."Signum", "LP_PERSONAL"."Nummer", "LP_PERSONAL"."Text1", "LP_PERSONAL"."Text2", 
"LP_LONESLAG"."Summera", "LP_Foretagsuppgifter"."Foretagsnamn1", "LP_Foretagsuppgifter"."Adress", "LP_Foretagsuppgifter"."Postadress", 
"LP_LONEHANDELSER"."Personnummer", "LP_LONEHANDELSER"."Fran_datum", "LP_LONEHANDELSER"."Till_datum", "LP_LONEHANDELSER"."Anm", "LP_PERSONAL"."Sprak", 
"LP_LONESLAG"."Namn_Finska", "LP_LONESLAG"."Markstat1", "LP_LONESLAG"."Markstat2", "LP_LONESLAG"."Markstat3", "LP_LONESLAG"."Markstat4", 
"LP_LONESLAG"."Markstat5", "LP_LONESLAG"."Markstat6", "LP_LONESLAG"."Markstat7", "LP_LONESLAG"."Markstat8", "LP_LONESLAG"."Markstat9", 
"LP_LONESLAG"."Markstat10", "LP_LONESLAG"."Markstat11", "LP_LONESLAG"."Markstat12", "LP_LONESLAG"."Markstat13", "LP_LONESLAG"."Markstat14", 
"LP_LONESLAG"."Markstat15", "LP_ANSTALLNING"."Anst_slutade", "LP_ANSTALLNING"."Anst_borjade", "LP_PERSONAL"."Coadress", "LP_LONEHANDELSER"."Ordning", 
"LP_LONESLAG"."Ordning", "LP_ARSSTATISTIK"."S110", "LP_ARSSTATISTIK"."S111", "LP_ARSSTATISTIK"."S112", "LP_PERIODSTATISTIK"."S86", 
"LP_PERIODSTATISTIK"."S87", "LP_ARSSTATISTIK"."S01", "LP_LONESLAG"."Format", "LP_Foretagsuppgifter"."Signum", "LP_LONESLAG"."Namn_Engelska", 
"LP_PERSONAL"."Anst_borjade", "LP_PERSONAL"."BIC", "LP_PERSONAL"."IBAN", "LP_HISTORIK_PERIOD"."Fran_datum", "LP_HISTORIK_PERIOD"."Till_datum", 
"LP_PERSONAL"."Skattenummer", "LP_LONEHANDELSER"."Anstnr", "LP_Foretagsuppgifter"."Specvar6", "LP_Foretagsuppgifter"."Specvar5", 
"LP_Foretagsuppgifter"."Specvar4", "LP_Foretagsuppgifter"."Specvar3", "LP_Foretagsuppgifter"."Specvar2", "LP_Foretagsuppgifter"."Specvar1", 
"LP_ANSTALLNING"."Avdelningsnummer", "LP_ANSTALLNING"."Arbetsplatsens_nr", "LP_ANSTALLNING"."Personalgrupp", "LP_ANSTALLNING"."Lonetyp", 
"LP_LONEHANDELSER"."Loneperiod", "LP_HISTORIK_PERIOD"."Period", "LP_PERSONAL"."Lonebesked", "LP_PERIODSTATISTIK"."S90" 
FROM   (((((("LP_LONEHANDELSER" "LP_LONEHANDELSER" 
INNER JOIN "LP_ANSTALLNING" "LP_ANSTALLNING" ON ("LP_LONEHANDELSER"."Personnummer"="LP_ANSTALLNING"."Persnr") AND ("LP_LONEHANDELSER"."Anstnr"="LP_ANSTALLNING"."Anstnr")) 
LEFT OUTER JOIN "LP_LONESLAG" "LP_LONESLAG" ON "LP_LONEHANDELSER"."Loneslag"="LP_LONESLAG"."Nummer") 
LEFT OUTER JOIN "LP_ARSSTATISTIK" "LP_ARSSTATISTIK" ON "LP_LONEHANDELSER"."Personnummer"="LP_ARSSTATISTIK"."Personalnr") 
LEFT OUTER JOIN "LP_PERSONAL" "LP_PERSONAL" ON "LP_LONEHANDELSER"."Personnummer"="LP_PERSONAL"."Nummer") 
LEFT OUTER JOIN "LP_PERIODSTATISTIK" "LP_PERIODSTATISTIK" ON "LP_LONEHANDELSER"."Personnummer"="LP_PERIODSTATISTIK"."Personalnr") 
LEFT OUTER JOIN "LP_HISTORIK_PERIOD" "LP_HISTORIK_PERIOD" ON "LP_LONEHANDELSER"."Personnummer"="LP_HISTORIK_PERIOD"."Personnummer") 
LEFT OUTER JOIN "LP_Foretagsuppgifter" "LP_Foretagsuppgifter" ON "LP_PERSONAL"."Namn"<>"LP_Foretagsuppgifter"."Id" 
WHERE  "LP_PERSONAL"."Nummer"=7043 AND "LP_LONEHANDELSER"."Loneperiod"=1709 
AND ("LP_HISTORIK_PERIOD"."Period" IS  NULL  OR "LP_HISTORIK_PERIOD"."Period"=N'1709') ORDER BY "LP_PERSONAL"."Namn", "LP_PERSONAL"."Nummer"
Share
10 |10000 characters needed characters left characters exceeded