cancel
Showing results for 
Search instead for 
Did you mean: 

Connection with SQL Server doesn't close/Can't Return more than 1000 results

Former Member
0 Kudos

Hi,

Recently we had some performance problems with the Reports and found that CR doesn't close the connection, creating one new connection for each report and setting them to sleep(not closing).

I've tried many different approaches found on the net, some even from the SAP Communityusing like .Close(), .Dispose() and setting ReportDocument to Null at Page_Unload.

After many trials and failures we fixed the connection issue filling a DataTable and using the ReportDocument.SetDataSource("DataTable") after cleaning all the connections in the report.

This fixed the hanging connections issue but we stuck on another problem.

I'm not sure if it's a issue with DataTable or not as the problem also happens when using crystal's connections(and creating all the connections in SQL Server) but we couldn't fill a report with more than 1000 lines(only one which had only 2 columns).

When i return 1000 lines the reports work, if i return 1001 - 1070 the report freezes in a white screen and if i return more than 1070 the report shows a "Out of Memory" exception.

We currently use VS2013 with Framework 4.0, CR 13.0.12.1494 for VS and SQL Server 2005.

All data comes from Stored Procedures.

Could anyone help me and my team with this problem ?

PS: We recently migrated from VS 2005 + Framework 3.5 + Integrated CR(reports worked properly at this point)

PS2: This is a legacy app.

PS3: The problem also happens when trying to export to pdf/xls

Here's the code

.aspx.cs page


    private ReportDocument myReportDocument;

    protected void Page_Load(object sender, EventArgs e)

    {

        GC.Collect();

        string strTipo = Request.QueryString["Tipo"];

        ArrayList arrFiltros = ((ArrayList)Session["Parametros"]);

        clsRelatorio objRelatorio = new clsRelatorio();

        if (!string.IsNullOrEmpty(Request.QueryString["RelatorioID"]))

        {

            try

            {

                myReportDocument = objRelatorio.ObterDadosRelatorio(Convert.ToInt32(Request.QueryString["RelatorioID"]), arrFiltros, Server.MapPath("~/App_Reports/"));

                if (strTipo == "Todos" || strTipo == string.Empty)

                {

                    CrystalReportViewer1.HasCrystalLogo = false;

                    CrystalReportViewer1.EnableViewState = true;

                    CrystalReportViewer1.SeparatePages = false;

                    CrystalReportViewer1.HasToggleGroupTreeButton = false;

                    CrystalReportViewer1.DisplayGroupTree = false;

                   

                    CrystalReportViewer1.ReportSource = myReportDocument;

                    CrystalReportViewer1.DataBind();

                }

                else

                {

                    string _Tipo = "";

                    ExportFormatType _ExportFormatType;

                    if (strTipo == "pdf")

                    {

                        _Tipo = "pdf";

                        _ExportFormatType = CrystalDecisions.Shared.ExportFormatType.PortableDocFormat;

                    }

                    else

                    {

                        _Tipo = "xls";

                        _ExportFormatType = CrystalDecisions.Shared.ExportFormatType.ExcelRecord;

                    }

                    myReportDocument.ExportToHttpResponse(_ExportFormatType, this.Response, false, "arquivo." + _Tipo);

                }

            }

            catch (Exception ex)

            {

                UtilFormulario.DialogMessage(this.Page, ex.Message, "Erro", false);

            }

            finally

            {

                arrFiltros = null;

                strTipo = null;

            }

        }

    }

    protected void Page_UnLoad(object sender, EventArgs e)

    {

        if (myReportDocument != null)

        {

            myReportDocument.Close();

            myReportDocument.Dispose();

            myReportDocument = null;

            CrystalReportViewer1.Dispose();

        }

    }

Internal Class


internal ReportDocument ObterDadosRelatorio(int IDRelatorio, ArrayList Parametros, string MapPath)

        {

            clsSQLHelper objSQLHelper = new clsSQLHelper();

            DataTable dt = new DataTable();

            ReportDocument myReportDocument = new ReportDocument();

            objSQLHelper.AdicionarParametros("RELATORIOID", IDRelatorio);

            dt = objSQLHelper.CreateDataTable(Connections.DefaultConn, CommandType.StoredProcedure, "spGetReportData");

            objSQLHelper.RemoverTodosParametros();

            if (dt.Rows.Count > 0)

            {

                DataRow row = dt.Rows[0];

                myReportDocument.Load(MapPath + row["Report"].ToString());

                myReportDocument.DataSourceConnections.Clear();

                clsUtilitarios Utils = new clsUtilitarios();

                for (int i = 0; i < Parametros.Count; i++)

                {

                    string ParametroValor = Parametros[i].ToString();

                    string strParametro = ParametroValor.Split(',')[0];

                    string strValor = string.Empty;

                    strValor = Utils.RetiraAcento(ParametroValor.Split(',')[1].Replace("#", ","));

                    objSQLHelper.AdicionarParametros(strParametro.Replace("@", ""), String.IsNullOrEmpty(strValor) ? null : strValor);

                }

                SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[row["BaseDados"].ToString()].ToString());

//WOP for finding max resuts

                conn.Open();

                DataTable dt2 = objSQLHelper.CreateDataTable(conn, CommandType.StoredProcedure, row["Procedure"].ToString());

                conn.Close();

                for (int i = 0; i < dt2.Rows.Count; i++)

                {

                    if (i > 1000)

                        dt2.Rows.RemoveAt(i);

                }

                myReportDocument.SetDataSource(dt2);

                myReportDocument.PrintOptions.PaperOrientation = (Convert.ToBoolean(row["Landscape"])) ? CrystalDecisions.Shared.PaperOrientation.Landscape : CrystalDecisions.Shared.PaperOrientation.Portrait;

            }

            return myReportDocument;

        }

Accepted Solutions (1)

Accepted Solutions (1)

former_member183750
Active Contributor
0 Kudos

Hi Michael

I believe the original issue you refer to is due to OLD DB not accepting db disconnect due to connection pooling, be it from CR or non CR application.

The error "Out of Memory", is actually correct in this case. ADO .NET Datasets are hogs when it comes to memory and CR must create a copy of the dataset in it's own space - security limitation by MS.

You have two options;

1) Use smaller datasets

2) Use ODBC - disconnect commands work when using ODBC

- Ludek

Senior Support Engineer AGS Product Support, Global Support Center Canada

Follow us on Twitter

Former Member
0 Kudos

Hi Ludek,

Thanks for the answer, it was very helpful.

Talking to the team and the manager, we didn't like the idea of changing to ODBC.

Do you know if we can change the default limitation imposed by MS? As it is now, when the report returns a "Out of Memory" exception the server memory is barely affected(around 50% or less).

Thanks in advance.

Michel.

former_member183750
Active Contributor
0 Kudos

Hi Michel

Unfortunately, I don't think there is any way to circumvent the MS Security. Looks like limiting the dataset vie use of selection formulas will be the only way of proceeding(?).

- Ludek

Former Member
0 Kudos

Hi Ludek,

Thanks for the help, I'll try to contact MS about it, if I get any answer I'll update here.

Michel

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi,

We found a temporary solution for the problem. It was not what we wanted but at least the users could get the reports...

Instead of using crystal viewer to display it, use either the export to pdf/excel option. Whichever the size of the dataset, we successfully exported using these options.

Michel.