What could make crystal reports create empty reports, at random

[anders_gustafsson]

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?

[former_member11696 · Accepted solution]

That is odd, CR dynamically builds the query depending on the DB client used. It suggests some other version may be loading or it's a resource issue and it can't load the client.

Enable CRLogger and see what CR is using.

And then try setting location to the otther one, ODBC -< OLE DB or OLE DB to ODBC and see what happens.

Don

[former_member11696]

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

[former_member11696]

Hi Anders,

What SDK are you using?

Don

[anders_gustafsson]

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"

[anders_gustafsson]

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

[former_member11696]

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

[anders_gustafsson]

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.

[anders_gustafsson]

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.

[anders_gustafsson]

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?

[anders_gustafsson]

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??