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

In a .NET application using CR for VS SP 12 when previewing a report and closing the viewer and then disposing of the report objects and viewer a database disconnect is not being sent resulting in multiple database connections being left open.

This also happens in Crystal Reports Designer.

Crystal Reports for Visual Studio

Crystla Reports Designer

 

Create a simple report that connects to MS SQL Server and a simple test application in VS using CR for VS SP 12

In the test app add the viewer and 3 buttons:

1. 1st button to open report and log on using the ClientDocument repor tobject.
2. 2nd button to view the report.
3. 3rd button to close/dispose report object and close the viewer. Application continues to run

To validate the issue:

• Run MS SQL Server Profiler.
• Click the first button to open and set log on info in code.
• Click the second button to close the report, note Profiler shows a disconnect from the table.
• Run the report again and this time preview the report and hit the 3rd button to close the report object and clear the viewer
• Notice the database connection did not disconnect in SQL Profiler.

To validate the issue it only in the viewer:

Create a new test app:

1. Button 1 - Assign the Report using CrystalReportViewer1.reportSource = "c:\testreport.rpt"
2. Button 2 - has the ReportSource set to null.

Results - Notice the disconnect does not happen

This issue is due to Microsoft's OLE DB Provider and it using Connection Pooling. Connection pooling does not disconnect the applications or Crystal Report Designer connections from disconnecting until the application is close.

In CR Designer you can close all open reports and click on the Log Off Database and the connection is still open according to SQL Profiler.

The connection is closed only when the application pr CR DEsigner is closed or the default timeout on the Server is reached. MS KBase article indicates this can be from 4 to 6 minutes typically.

For more info on this issue refer to MS KBA's:

http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbconnection.close(v=vs.110).aspx

http://msdn.microsoft.com/en-us/library/8xx3tyca(v=vs.110).aspx

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring(v=vs.110).aspx

This issue has been escalated to R&D and set for Service Pack 14.

Our Developers have looked at this issue and determined there is nothing we can do to close the connections when using OLE DB. See references for more info.

Work around is to convert all of your reports and use an ODBC connection, we can specifically sent a SQLDisconnnect for our connections which will be handle by the ODBC Client.

To see this is not a Crystal issue create a simple test app and add a button with the following code, replace my connection info with your own servver info:

string nowConString = "Provider=SQLOLEDB;Data Source=VANPGDBFUN03.dhcp.pgdev.sap.corp;Initial Catalog = XTreme; User Id = guest; Password = password;";

System.Data.OleDb.OleDbConnection dbcon = new System.Data.OleDb.OleDbConnection(nowConString);

dbcon.Open();

dbcon.Close();

dbcon.Dispose();

Start SQL Profile and watch the connections, Close does not disconnect. Wait a minute or so and you will see the connection is eventually closed.

Our Developers have found a solution for this issue:

In the OLE DB connection properties in CR Designer add/change the following property:

• ‘OLE DB Services’ property to ‘-4’. Default is 5.

This new value should instruct the OLE DB Connection to disconnect right away and not place the CR DB Connection in the Pool. In both Crystla Reports Designer and in an application using the SDK's.

crystal reports for visual studio, ms sql server, database disconnect, open connections, windows.forms.viewer , KBA , BI-RA-CR-SDK , SDK related, including Java/.NET etc. , Problem

SAP Crystal Reports, developer version for Microsoft Visual Studio