We help IT Professionals succeed at work.

SAP Crystal Reports 2016 not disconnecting from SQL Server 2012

204 Views
Last Modified: 2019-03-06
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?
Comment
Watch Question

Ido MilletProfessor Emeritus of MIS at Penn State Erie and Owner, Millet Software
CERTIFIED EXPERT

Commented:
You are probably using SP21 or earlier.
Upgrade the runtime to SP22 or later and the problem should go away.
RayBakkerApplication Developer Analyst

Author

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 Emeritus of MIS at Penn State Erie and Owner, Millet Software
CERTIFIED EXPERT

Commented:
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
RayBakkerApplication Developer Analyst

Author

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 Emeritus of MIS at Penn State Erie and Owner, Millet Software
CERTIFIED EXPERT

Commented:
Does the problem go a way if you use ODBC instead of OLEDB?
RayBakkerApplication Developer Analyst

Author

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 Emeritus of MIS at Penn State Erie and Owner, Millet Software
CERTIFIED EXPERT

Commented:
Excellent. Thanks for closing the loop.
Application Developer Analyst
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.