Skip to Content

What could make crystal reports create empty reports, at random

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?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

10 Answers

  • Best Answer
    May 04 at 11:51 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 14, 2016 at 03:35 PM

    Hi Anders,

    What SDK are you using?

    Don

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 31, 2017 at 02:39 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 02, 2017 at 08:36 AM

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

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 31, 2017 at 12:17 PM

    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?

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 01, 2017 at 09:15 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 03, 2017 at 09:10 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • Mar 21, 2017 at 05:55 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 13, 2017 at 01:50 PM

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

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 28, 2017 at 12:39 PM

    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"
    
    Add comment
    10|10000 characters needed characters exceeded