Report taking too long to generate

[stephenstuntz]

I am running a crystal report using the viewer from vbnet (framework 4.0)  and reports without record selection run as expected.   If I add a record selection such as JobNum > 4000 to the report the report generates and database error code 17 which I suspect comes from a timeout.  I also get the same error if there are parameters on the report.

I am running 13.0.10.1385.

any ideas of how to solve the problem.

[stephenstuntz · Accepted solution]

more information.

the db is mssql

there is one test report without any parameters or formulas or subreports

     the report is connected through ODBC with a system DNS

the report runs in the vs2010 ide  in a reasonable amount of time

the report runs from code in a reasonable amount of time (using the viewer)

to me this means all connections are working.

i change the report to include a record selection limiting the value of one field

the report runs in vs2010 ide in a reasonable amount of time with the records limited.

the report starts to run and finally gives up with a DB error of 17 from code.

i change the report back by removing the record selection and

it runs in both places.

[former_member183750 · Accepted solution]

Unfortunately you are not providing the info I need:

It would be so useful to know the version of MS SQL you are using. The db client are you using (e.g.; MS SQL Native 10, etc.). The Service Pack of CRVS you are using. Have you looked up error code 17 in your db documentation? It may look like I'm trying to be difficult, so why is all of this important? See the following re MS SQL:

MS SQL

For OLE DB then use:

MS SQL 2005 - OLE DB Provider

MS SQL 2008 - SQL Native 10

MS SQL 2012 - SQL Native 11

MS SQL 2013 - SQL Native 11

For ODBC then use:

MS SQL 2005 - SQL Native

MS SQL 2008 - SQL Native 10

MS SQL 2012 - SQL Native 11

MS SQL 2013 - SQL Native 11

Hope the above makes sense.... The more accurate info you provide, the quicker we'll be able to help. Otherwise, we're just guessing and assuming.

- Ludek

[stephenstuntz · Accepted solution]

sorry i left the ms sql info out - i am using sql 2012  i am not at the test machine now so i do not know if i am using native 11

i did assume that since the report ran under code and the only change was to adding  record selection to a crystal report (the sql statement on the report looked ok) that although it was a DB error i was guessing it was a timeout problem

[stephenstuntz · Accepted solution]

ok -  i corrected to native client 11 (from 10) and now the reports do not work at all.  at least that is consistent and probably easier to find.

i have compiled the application (advanced compile options) for target cpu x86  and target framework 4.0d

the compile platform does not give me the choice for x86 only active platform

sap crystal reports for microsoft visual studio  13.0.10.1385

sap crystal reports runtime engine for .net framework (64 bit)  13.0.10.1385

the reports loads ok and then i set the connection info

  myConnectionInfo.ServerName = "Driver={SQL Server Native Client 11.0};Server=" & dbserver & ";"

        myConnectionInfo.DatabaseName = dbname

        myConnectionInfo.UserID = "" 'dbuserid

        myConnectionInfo.Password = "" 'dbpwd

        myConnectionInfo.IntegratedSecurity = True

	For Each myTable As CrystalDecisions.CrystalReports.Engine.Table In CR.Database.Tables
	Dim myTableLogonInfo As TableLogOnInfo = myTable.LogOnInfo
	myTableLogonInfo.ConnectionInfo = myConnectionInfo
	myTable.ApplyLogOnInfo(myTableLogonInfo)
	Next

is there a way to test the connection before i call the report viewer?

[stephenstuntz · Accepted solution]

so i took out all the connection code and let the report run in the viewer using the data in the set database connection part of the report

reports all ran fine.

now to figure out how the connection string i used was screwing up the connection string in the report.

thoughts?

[former_member183750 · Accepted solution]

Not sure why: myConnectionInfo.IntegratedSecurity = True

If the database is set up with integrated security, then you should not need any db logon code at all. E.g.; this should just work:

Dim crReportDocument As New CrystalDecisions.CrystalReports.Engine.ReportDocument

crReportDocument.Load("C:\WindowsApplication5\test.rpt")
CrystalReportViewer1.ReportSource = crReportDocument

Re. testing the connection:

If there is no integrated security use the code below:

Dim crDatabase As Database
    Dim crTables As Tables
    Dim crTable As Table
    Dim crTableLogOnInfo As TableLogOnInfo
    Dim crConnectionInfo As ConnectionInfo
     Dim crReportDocument As New CrystalDecisions.CrystalReports.Engine.ReportDocument


crReportDocument.Load("C:\WindowsApplication5\test.rpt")

crConnectionInfo = New ConnectionInfo()
        With crConnectionInfo
            .ServerName = "Server1"    'physical server name
            .DatabaseName = "Pubs"
            .UserID = "myuser"
            .Password = "mypassword"
        End With

        'Get the table information from the report
        crDatabase = crReportDocument.Database
        crTables = crDatabase.Tables

        'Loop through all tables in the report and apply the connection
        'information for each table.
        For Each crTable In crTables
            crTableLogOnInfo = crTable.LogOnInfo
            crTableLogOnInfo.ConnectionInfo = crConnectionInfo
            crTable.ApplyLogOnInfo(crTableLogOnInfo)
        Next

        CrystalReportViewer1.ReportSource = crReportDocument

To test connectivity: crTable.TestConnectivity

It may not be a bad idea to look at the sample app vbnet_win_dbengine available here:

Crystal Reports for .NET SDK Samples - Business Intelligence (BusinessObjects) - SCN Wiki

Other useful resources:

Crystal Reports for Visual Studio 2005 Walkthroughs (Applies to all versions of VS and CR)

SAP Crystal Reports .NET SDK Developer Guide

SAP Crystal Reports .NET API Guide

At the risk of overcompensating the issue, see if this KBA will help (but only if the above does not help...):

1553921 - Is there a utility that would help in writing database logon code?

If you do decide to go the route of the above KBA, you will want to see:

Report Application Server .NET SDK Developer Guide

Report Application Server .NET API Guide

How to Use The RAS SDK .NET With In-Process RAS Server

In all of this, note that the report must be able to run in the CR designer. If it does not run there, it will never, ever run in your app...

- Ludek

[stephenstuntz · Accepted solution]

Thanks for all that information.  i was logging on because i need to change the database location from code and not on each report.

having gotten this far i feel the question is answered.  i will be able to deal with the change data base from the stuff you sent me.

thanks again.

[former_member183750]

The database error code is coming directly from the database client. So, you need to consult your db documentation to find out what the error actually means...

As for the query taking a long time, you will have to provide some more basic information:

What is the database?

How are you connecting to it?

What db client are you using (e.g.; MS SQL Native 10, etc.).

How many subreports?

What section are the subreports in?

What are you comparing the performance to? E.g.; where do you see an acceptable performance?

Etc., etc., etc. The more info the better.

- Ludek

Senior Support Engineer AGS Product Support, Global Support Center Canada

Follow us on Twitter