Avatar of RayBakker
RayBakker
Flag for Canada asked on

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?
SQLCrystal ReportsMicrosoft SQL Server

Avatar of undefined
Last Comment
RayBakker

8/22/2022 - Mon
Ido Millet

You are probably using SP21 or earlier.
Upgrade the runtime to SP22 or later and the problem should go away.
RayBakker

ASKER
Ido

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

Ray
Ido Millet

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
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
RayBakker

ASKER
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 Millet

Does the problem go a way if you use ODBC instead of OLEDB?
RayBakker

ASKER
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Ido Millet

Excellent. Thanks for closing the loop.
ASKER CERTIFIED SOLUTION
RayBakker

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question