SAP Crystal Reports 2016 not disconnecting from SQL Server 2012

Greetings

I have inherited some code from a colleague and I noticed that the SAP Crystal Reports are not disconnecting from the SQL Server. The application does close and dispose of the crystal viewer instance. I have posted the code here:

Private Sub CloseCrystalReports()
        If CRReports.crRpt IsNot Nothing Then
            CloseReports(CRReports.crRpt)
            CRReports.crRpt.Close()
            CRReports.crRpt.Dispose()
            CRReports.crRpt = Nothing
        End If
        GC.Collect()
        GC.WaitForPendingFinalizers()
        GC.Collect()
    End Sub

Open in new window


I my attempts to resolved the issue, I also added this:

Protected Sub Page_Unload(ByVal sender As Object, ByVal e As EventArgs)
        If CRReports.crRpt IsNot Nothing Then
            CloseReports(CRReports.crRpt)
            CRReports.crRpt.Close()
            CRReports.crRpt.Dispose()
            CRReports.crRpt = Nothing
        End If
        GC.Collect()
        GC.WaitForPendingFinalizers()
        GC.Collect()
    End Sub

Open in new window


I also found that the issue may be with SQL Server and Connection Pooling, so I added this to the connection string:

Pooling=False

I do not see an increase in memory usage on the application server. But I do see that the connections to the database on the database server does not decrease after the application is closed.

Does anyone have any other suggestions?
RayBakkerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
You are probably using SP21 or earlier.
Upgrade the runtime to SP22 or later and the problem should go away.
RayBakkerAuthor Commented:
Ido

Thanks for the quick response. I looked at the References and the CrystalDecisions.CrystalReports.Engine is version 13,.0.3500.0

Ray
Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
Look at installed features. Find  
SAP Crystal Reports runtime engine for .NET Framework (32-bit)
Look at the version.
The SP number is the 3rd component.
For example, 13.0.24.2970 is SP24
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

RayBakkerAuthor Commented:
Ido

I forgot about the server. We are currently running  13.0.22.2668  64 bit on the server. So we should be good.
Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
Does the problem go a way if you use ODBC instead of OLEDB?
RayBakkerAuthor Commented:
Ido

I think I have found a solution.

I the report its self,Under Database > Set Datasouce Location, under properties, set OLE DB Services to -4.

Seems to have resolved the issue. I will continue to test.  I will post back on Monday. Keep you fingers crossed.

Ray
Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
Excellent. Thanks for closing the loop.
RayBakkerAuthor Commented:
My previous comment fixed the CR reports themselves. There was still an issue in code. The code create a PDF on opened it. This meant the the current page (with the CR object) was no longer executing, so the CR closing sub was not getting called.

I added a page unload sub that calls the CR close sub. Works perfectly.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.