Opening multiple reports in Crystal Reports for VS causes database connect limit to be reached. Seems to be no way to force Crystal Reports to close database connection (other than exiting application)

[Former Member]

I am working on upgrading an application that has been in use for many years.  The application is written in VB6 and I have been tasked with upgrading the current application to Crystal Reports for Visual Studio.  I am using Crystal Reports for VS Version 13.0.12.1494.  The system's database is a Sybase SQL Anywhere 16 database with an ODBC connection using integrated login.  Each of the reports has the database connection set up from within the report.  There is only once database server, so each of the reports are pointing to the same DB.  The database server is currently installed as a "Personal Server" with a limit of 10 connections. 

I have implemented the CR viewer as part of a COM-callable wrapper that exposes a COM interface for VB6 to interact with.  Inside of my viewer component is a Winform that embeds the Crystal's Report viewer.  The COM interface basically maps the basic Crystal apis to methods that the VB6 can call (i.e., Load Report, Set Field Text, Update SQL Query, etc).  This architecture is working as designed and the reports are displaying correctly and responding correctly to changes in queries, etc.

The issue is that after I open 9 reports, the tenth one will respond with an error indicating that the database connection limit has been reached.  The database connections used by the reports aren't released until after the application is closed.  The application is designed for a secure environment that prohibits the non-administrative user from accessing the systems desktop, so asking the user tor restart the application after 10 reports isn't a viable option.

I have checked and database connection pooling is turned off for the SQL Anywhere 16 driver.

I have been digging on this for a few days and have tried adding code in the FormClosed event to close and dispose of the Report Document as follows:

ReportDocument reportDoc= (ReportDocument) crystalReportViewer1.ReportSource;

reportDoc.Close();

reportDoc.Dispose();

GC.Collect();       // Force garbage collection on disposed items

I have also tried the following (as well as maybe 20 or so other permutations) trying to fix the issue with no success.  

ReportDocument reportDoc= (ReportDocument) crystalReportViewer1.ReportSource;

foreach (Table table in reportDoc.Database.Tables)

     table.Dispose();

crystalReportViewer1.ReportSource = null;

reportDoc.Database.Dispose();

reportDoc.Close();

reportDoc.Dispose();

reportDoc = (ReportDocument)crystalReportViewer1.ReportSource;

GC.Collect();       // Force garabe collection on disposed items

Any ideas or suggestions would be greatly appreciated.  I have been pulling my hair out on this one!

[Former Member · Accepted solution]

Going to SP 10 fixed my issue when testing on a clean machine.  The issue was still showing up on my development machine when I had both the Run-time engine and the Developer's version installed.

A test when on a non-development machine showed the connections not closing issue with Version 13, SP 12.  Changing from SP 12 to SP 10 fixed the issue.

I would recommend using SP 10 until the issue is addressed in a later release, but that is only my advice to others.

[former_member11696 · Accepted solution]

Brian,

NEVER install the VS installer and the runtime packages on the same PC. Not required so no reason to do so....

Don

[Former Member · Accepted solution]

Thanks for the information.  I will say that although I should have figured that out, for someone how is diving into Crystal Reports and just needing to figure out what needs to be done to get the project completed, it wasn't obvious that this would raise issues.  The dual install was actually a left over artifact from my initial feasibility investigation.

At any rate, I hope this issue gets resolved in the next version as it is a critical issue that I am assuming is affecting other develops/products as well.

Thanks so much for your help Don!  The idea of trying SP 10 was spot on.

-- Brian

[former_member11696 · Accepted solution]

Hi Brian,

R&D has looked into this issue and they had the same problem even with SP 10. It turns out this is not something we can fix. It is all due to MS SQL Servers Connection Pooling feature which likely has been updated in some patch.

You can test this yourself. See this KBA for details on how to:

2107357 - Windows.Form Viewer does not disconnect from the Database when the viewer object is close/Null

Sample code in the KBA has no references from CR and simply connects and tries to close the connection. I have also added some links from MS KBase articles on how Connection Pools work. I tried a few options in the Connection string to change the defaults but nothing seems to affect the outcome. Only way the connection was disconnected was by leaving the app idle for a minute or so or by closing the test app.

The easiest work around we have for you is to convert all of your reports to ODBC, we can and do send a SQLDisconnect to the ODBC driver which does disconnect when the report is closed.

The other work around is to convert your application to a Multi-thread app so each report object spawns a separate process, when the report is done the process is closed and that should tell SQL Server to close the connections thus freeing up the pool.

Thanks again

Don

[Former Member · Accepted solution]

We are not using MS SQL, we are using Sybase SQL Anywhere, version 16 from SAP (refer to the original post on this thread).  We found the issue using SP 12 for Crystal Reports. Reverting back to SP 10 does indeed fix the issue, at least with our application. 

I would suggest that R&D look into this issue on Sybase SQL Anywhere as it's one of your own products that this latest SP for Crystal Reports isn't behaving properly with.

[former_member11696 · Accepted solution]

Ah, good point.

So if you open CR Designer and connect to Sybase through OLE DB and then Disconnect do you see the connection being dropped?

I'm don't support Sybase so I have no contact info for them.

I'll get the client installed and see what I can figure out.

What is the connection string you are using?

Don

[Former Member · Accepted solution]

We actually have an ODBC connection setup for the database.  Both the application and the reports use the same ODBC DSN.  Part of the issue for us was that Sybase is installed as a "Personal Server" with a limit of 10 connections, which makes the issue fairly easy to replicate.  I don't know the exact connection string - I work on the application, someone else is responsible for maintaining the reports, but I believe it's simply a matter of selecting the right ODBC connection from the database expert.  We use integrated login, so even the login information is handled automatically via the ODBC settings.

And yes, closing the report and calling close/dispose on the report document is closing the connection for us now.

[former_member11696 · Accepted solution]

Hi Brian,

I see what I did... RDI IMS                    Dec 15, 2014 11:04 AM               

Posted his issue using SQL Server, that is what I ended up testing.

So for RDI IMS this is something we cannot fix.

For Brian, So now are you saying it is working now with SP 12 or do you mean it's working for you now with SP 10? Closing and Disposing of the report object is what is required, the Viewer keeps the connection open because it's not aware of or has the ability to close/dispose.

Can you enable ODBC Tracing and attach the log? Do one for CR Designer and one for your app.

Zip the file up, rename it to *.txt and then attach to this post using Advanced Editor. As long as it's less than 1 meg.

Use a simple report, one table and one field so it's not cluttered with all the other reads...

So the quick solution is to open the Report using the engine and then when the viewer is closed to then close the report object and that should disconnect from the Server.

In the backend if only using the viewer to load the report it is InProc RAS that is opening the report. Recent updates to RAS may have changed this work flow. Problem is I don't know if this part is by design or documented but I will ask DEV to look into it.

Thanks again

Don

[Former Member · Accepted solution]

We have it working with and am planning to release with 13.10.  13.12 is the one we had the issue with that should be addressed in a future release.  It's not acceptable to not have a way of closing a data connection that was opened just for the purposes of viewing a report.  View 10 reports and your app is screwed until it's restarted.

To be honest, I have that project in the final QA stages and am already working on another project.  I wasted almost 2 weeks trying to figure this out (I'm not one to post on line at the first indication of a problem - I worked almost a week trying to fix this before I posted on this site).

I don't really have the band width to go back now and do a test report and application to demonstrate the problem.  I would assume that issues like this should be caught on the first go-no-go test by your QA team as proper handling of DB connections is vital to Crystal Reports being a viable option for reporting in a production environment.

[former_member11696 · Accepted solution]

Hi Brian,

R&D tested it and they say it works fine, disconnects using SP 12. I'm asking for more details from them on how their DB is configured. It may be that we are now using a new property or they may not have used Trusted Auth..

See if disabling Connection Pooling helps:

Info from the Sybase Engineer:

SQL Anywhere 16 does employ connection pooling for efficient re-use of database connections by default. See: http://dcx.sap.com/index.html#sa160/en/dbadmin/sa22c8e99f-db62-4851-a551-ea775eeed6e8.html
. Try “CPOOL=NO” on the ODBC connection string to disable connection pooling.

If you’re using the iAnywhere.Data.SQLAnywhere ADO.NET driver, it has its own connection pool settings: http://dcx.sap.com/index.html#sa160/en/dbprogramming/connection-pooling-dotnet-dev.html

So unless I can replicate the issue or explain to R&D how to not sure if we can "fix" this since they say it works for them.

We can enable crlogger, can you run your app and a simple report with it enabled, one table one field and filtered for a few rows of data? With SP 10 and SP 12 installed, separate logs of course?

Zip up the logs and rename them it to *.txt and then attach to this post. 1 Meg file max, so it it's bigger than that I'll have to get in contact with you directly.

If you can do this for me that may help explain what is happening to R&D:

Redirecting...

If the link above does not work then search for this KBA

1553469  - How to enable Database logging in Crystal Reports for Visual Studio 2010

Don

[former_member11696 · Accepted solution]

Hi Brian,

Update for you, R&D has confirm Sybase also uses Connection Pooling and that is why the connections are not being released there also.

They are looking into if there are ways ( OLE DB Commands ) we can manage to tell the Servers not to Pool CR connections and if so what kind of impact that would have on the Servers. As you know CR can generate multiple Connections, one for the Main report and one for each subreport as the report runs....

Thanks again

Don

[Former Member · Accepted solution]

I'd like to chime in on this as I've been having the same problem but using SQL Server 2008 R2. The report uses ODBC and the DSN uses the "SQL Server" driver. This is a 32 bit app and DSN.

My application just creates ReportDocument instances, loads a report, sets the location of the tables within, sets the record selection formula and then exports as PDF. It does all this in a tight loop 1000s of times so it was a significant problem for us. The problem presented itself if I used a new ReportDocument instance per export or if I reused the same report and just changed the selection formula.

I was using SP12 but downgrading to SP10 has completely resolved the issue. RAM consumption is now a flat line and connections are closed correctly.

Many thanks Brian for identifying the solution

[former_member11696 · Accepted solution]

Hi Paul,

I'm still updating the Fixed issues wiki but there was a known memory leak in SP 12 that has been fixed in SP 13 so you may want to try it also.

So are you say ODBC is also not releasing the connections in SP 12? Or are you saying you had issues due to the memory leak?

Thanks

Don

[Former Member · Accepted solution]

I just noticed SP13 was there and gave it a whirl and it too has resolved the issue.

We were experiencing both connections being left open and a memory leak with SP12. I'm assuming the two were related.

Message was edited by: Don Williams Fixed it

[former_member11696 · Accepted solution]

Hi Paul,

Great, thanks for confirming. No they were not related actually, ODBC always disconnects. It's just an issue with OLE DB drivers and connection pooling.

Don

[Former Member · Accepted solution]

Interesting. Regardless, both issues seem to have been resolved with SP13.

I also had an issue whereby reusing the same ReportDocument instance for a large number of iterations would eventually fail due to the temp .rpt file disappearing but that seems to have been resolved also.

[Former Member · Accepted solution]

Just for the record,  the issue I encountered was with ODBC also - NOT OLE DB.  The situation that Paul described with his app and DSN is very similar to what we were seeing with SP 12.  The product that I was working on is already in final quality checks so I likely won't do anything with SP 13 until the next revision cycle, but it's good to know that the issue has been addressed and that my frustrations ended up being of some assistance to someone else

[Former Member · Accepted solution]

I can't edit my post but I should correct my statement "We were experiencing both connections being left open and a memory leak with SP10". That was supposed to be SP12.

[former_member11696 · Accepted solution]

@Paul, I fixed your post.

@Brian, Interesting, DEV said ODBC should work, or at least they confirmed it works for MS SQL Server, I don't know if they tested Sybase...

I've sent the test image with Sybase over to them so SP 14 should have the fix...

Don

[Former Member · Accepted solution]

Well something must have changed I as am using ODBC too.

[former_member11696 · Accepted solution]

Good morning Paul,

R&D found a work around/fix for this issue. In the report modify the OLE DB Connection properties:

"If you want to release connection immediately, you can set the ‘OLE DB Services’ property to ‘-4’."

See if that works for you and let me know?

Thanks again

Don

[Former Member · Accepted solution]

Hi Don,

I am working with progress/OpenEdge 11.2 and progress does not support OLE DB drivers. Hence I must use the ODBC connection to run the reports using report viewer 13. I have followed all the steps in this thread but still the ODBC connection doesn't get closed.

Can you let me know any suggestion or changes I should make to the .net app.

FYI :- No connection string is been specified in the .net app. The ODBC connection is inside the Crystal Report it self.

Thanks

Gihan.

[former_member183750 · Accepted solution]

Please create a new Discussion. This one is so old and so long, it may not even apply to your issue.

- Ludek

Senior Support Engineer AGS Product Support, Global Support Center Canada

Follow me on Twitter

[Former Member]

Hi Ludek,

Thanks so much for the quick reply.  Unfortunately I did not have time to work on the reporting project Friday afternoon, but did a quick test this morning with some interesting results.  I'm hoping if I describe what I'm doing, you can show me the error of my ways.  This is really my first major undertaking with Crystal Reports.

If I simply load the report, then close and dispose, I don't hit the limit of 10 files.  Note that I do not logon manually in my code as the logon parameters are all defined within the reports themselves.  The logon happens when you actually view the report.  Loading the report doesn't seem to actually log in to the DB.

What I did was create a very simple form with a single button that creates the WinForm class which contains the Crystal Viewer.  It then loads the report, sets the ReportSource property on the CrystalReportsViewer object contained in the WInForm and shows the report. The report does show correctly, until the 10 reports limit is reached.

The relevant code is shown below. More than I wanted to post, but i want to be as complete and unambiguous as possible. 

This code displays the same behavior as my earlier post (after 10 reports we are unable to create another connection to the DB).

// Initial Form that simply has a button

  public partial class SlectReport : form

  {

        public SelectReport()

        {

            InitializeComponent();

        }

        private void button1_Click(object sender, EventArgs e)

        {

            ReportDocument rd = new ReportDocument();

            ReportForm report = new ReportForm();

            try

            {

                rd.Load(@"Test.rpt");

                report.ReportSource = rd;

                report.Show();

          }

         catch (Exception ex)

         {

              MessageBox.Show(ex.Message);

          }

      }

  }

// The WinForm containing the Crystal Reports Viewer

    public partial class ReportForm : Form

    {

        public ReportForm()

        {

            InitializeComponent();

        }

        private void Form1_Load(object sender, EventArgs e)

        {

            this.crystalReportViewer1.RefreshReport();

            this.FormClosed += new FormClosedEventHandler(ReportForm_FormClosed);

        }

        void ReportForm_FormClosed(object sender, FormClosedEventArgs e)

        {

            ReportDocument rd;

            rd = (ReportDocument)crystalReportViewer1.ReportSource;

            rd.Close();

            rd.Dispose();

        }

        public object ReportSource

        {

            set { crystalReportViewer1.ReportSource = value; }

        }

    }

Again, any guidance would be greatly appreciated. 

[former_member292966]

Hi Brian,

I would have though the Close and Dispose would remove the connection.  If the reports all use the same logon, then you can look at using the LogonServer method.  You call this method before you open your first report.  All subsequent reports will use this same connection until you destroy the Application object or you call the LogOffServer method.

This will force all the reports to share the same connection.

Good luck,

Brian

[Former Member]

Where is the "LogonServer" method defined - what Using and/or references do I need to use this.  I've been doing some research, but don't find this API in the Crystal Reports help files and very few references to it when I search Google.  I did find something called an "Application" object, but I can't seem to find a definition for it when I try to define the object in my C# code.

I really appreciate the help and will keep looking into the LogonServer method while I wait for a reply, but I currently have over a week into solving this one issue and it's kind of messing up my project schedule.  With all due respect, there has to be a reliable way to disconnect a database connection after viewing a report.  The C# code that I previously posted is a rather trivial example of displaying a report.  I am curious how others have worked around this issue - I'm guessing I'm missing something simple, but it has me more than a little frustrated

[former_member11696]

Hi Guys,

I've been testing this question since a few others have noted the DB is not connecting and I do believe it is a problem. If you open the report using the engine and log on and then close the report it disconnects. True as always...

But if you preview the report and close the viewer it does not disconnect the DB connection. Only disconnects when the app is closed.

Setting the Viewer to Null does not work either.

I'll ping the developers and get them to verify the issue and escalate and should have the fix in SP 13...

Don

[Former Member]

What is meant by using the engine versus the viewer?  Is there a work around or another way to display the report to the end user that I can try to alleviate this issue?  I am willing to put the time in to try any possible workarounds!

I am so appreciative of your help and suggestions, but am desperate to try and resolve this issue.  It is holding up a major release for us and although I appreciate the issue being addressed in a future release, I'm not sure I have the luxury to wait for a fix.

I am looking into the possibility of reorganizing the COM object to just gather the required parameters and then calling an executable that I can feed the filename, field settings, selection and SQL modifications, etc. to, but would love to find an easier solution as this will take a fair amount of re-work to develop a new solution.  The idea is that the secondary executable would simply be closed on the Viewer/form closed event.

[former_member11696]

Hi Brian,

To open a report you would do something like:

rpt.load("c:\report1.rpt");

That is the engine loading the report

To have just the viewer load the report it would be:

CrystalReportViewer1.ReportSource = "c:\report1.rpt";

If you don't view the report then closing the report logs off from the DB.

If you view the report then it does not log off, that's the issue.

No work around, there is no way to tell the Engine or the Viewer to specifically log off from the DB. It will only happen when the report of viewer is closed.

I don't believe this issue has been around that long so try going back to SP 10. I'll try a few other SP's also to find when the issue started.

Don

[Former Member]

Thank you for the information that this is a newly introduced issue.  I never thought about trying an earlier release.  With that said, I have seen the same issue now in both SP 10 and SP 9.  Oddly, SP 9 and 10 both open one more report than SP 12 did, but I am still seeing after 11 reports the failure to log in.  I have the SQL Anywhere Personal Server window open while I'm running my test and I'm not seeing any disconnects happening until I exit my application (and then I see 10 or so all at once).

Will keep going backwards in the SP list until I find something that works.  I am trying the same rd.close(); rd.dispose(); that Ludek posted earlier (code is pretty much exactly like I posted in my reponse to Ludek).

[edit]

So I was going to keep trying to find an earlier version that maybe  didn't show this problem, but I just realized that the links aren't there for anything before SP 9, so I am back to waiting on guidance from you guys.

Right now I have the following installed on the Win 7 machine that I am testing/developing on:

SAP Crystal Reports runtime engine for .NET Framework (32-bit) Version 13.0.9.1312

and

SAP Crystal Reports, version for Microsoft Visual Studio Version 13.0.9.1312

I am including this information to make sure that nothing is conflicting or if the MS VS version matters (I have been using this same version of the MS VS for all my test).

Thanks!

Brian

Message was edited by: Brian Lanting

[former_member183750]

Hi Brian

Many thanks for the excellent description of the issue .

The .Close and .Dispose calls should do the trick as they remove the report object from memory and thus there is nothing to keep the connection open.

One test I'd like you to do is outside the COM-callable wrapper that exposes a COM interface for VB6. E.g a new sample app, .NET only:

load report

logon to the database

view report (or you don't even have to do this)

.close

.dispose

Loop through the above 10 times. Does that work? If it does, then it is the COM interface that will need to be looked at.

- Ludek

Senior Support Engineer AGS Product Support, Global Support Center Canada

Follow us on Twitter

[Former Member]

having the same issue with my MySQL server, ODBC driver. the connection limit reach 151 and stop others from connecting to the database. any news or workaround yet? currently using the latest SP/runtime.

[former_member183750]

Well, lets see what dev tells Don(?)...

- Ludek