Database connecor error:'' Failed to Retrieve data from the database

[Former Member]

Hi,

    Currently I am facing this weird Issue. This issue happens only with few reports and most of them are working fine.

My colleague created two reports. Report 1 and Report 2. Report 2 is a copy of Report 1 with some modifications and both are calling the same storedprocedure. When I call it from Asp.net then Report 1 is working fine but on Report 2, I get Database Connector Error. I have this problem only in .net but in delphi its working fine.

For Testing purpose

. In VS2010, When I run the report using 'Main Report Preview' with some parameters and the report works fine.

. I run it in web using same parameters, I get database connector error.

. Both use same login info for MSSQL[2008 R2] database.

. In SQL Profiler, I get error before the StoredProcedure gets called.

For database connection I have used

  ReportDocument cryRpt = new ReportDocument();

            //TableLogOnInfos crtableLogoninfos = new TableLogOnInfos();

            TableLogOnInfo crtableLogoninfo = new TableLogOnInfo();

            ConnectionInfo crConnectionInfo = new ConnectionInfo();

            //load report.

            cryRpt.Load(reportPath);

            //connection.

            string connStr = WebConfigManager.GetReportConnectionString();

            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(connStr);

            crtableLogoninfo.ConnectionInfo.AllowCustomConnection = true;

            crConnectionInfo.ServerName = builder.DataSource;

            crConnectionInfo.DatabaseName = builder.InitialCatalog;

            crConnectionInfo.UserID = builder.UserID;

            crConnectionInfo.Password = builder.Password;

            //pass connection to each tables.

            Tables CrTables = cryRpt.Database.Tables;

            foreach (Table CrTable in CrTables)

            {

                crtableLogoninfo.ConnectionInfo = CrTable.LogOnInfo.ConnectionInfo;

                crtableLogoninfo.ConnectionInfo = crConnectionInfo;

                CrTable.ApplyLogOnInfo(crtableLogoninfo);

                CrTable.Location = "dbo." + CrTable.Name;

            }

            //pass connection to each sub-reports.

            foreach (ReportDocument subRep in cryRpt.Subreports)

            {

                foreach (Table CrTable in subRep.Database.Tables)

                {

                    crtableLogoninfo = CrTable.LogOnInfo;

                    crtableLogoninfo.ConnectionInfo = crConnectionInfo;

                    CrTable.ApplyLogOnInfo(crtableLogoninfo);

                    CrTable.Location = "dbo." + CrTable.Name;

                }

            }

return cryRpt;

       

Wondering

1. Those reports where I get errors are copy of other report which is working fine with .net, so why do I get connection error?

2. This same connection info is used for all the reports. If there is a  connection problem then I should be getting the same error in all reports but I don't.

Could you please help me to figure out whats the real problem is.

Crystal Report XI

Visual studio: 2012

MSSQL: 2008 R2

Crystal Report: 11.0.0.2495 [SP: 4]

.Net Version: 4

Crystal Decision dll version: 13.0.2000.0

Thank you

Lakpa

[former_member11696 · Accepted solution]

Hi Lapka,

CR X1 did not support connecting to SQL 2008 R2 server, it requires the SQL Native 10 client. There are limitations posted by Microsoft when using the MDAC driver to connect to 2008.

Only option may be to upgrade to CR 2011 or CR 2013 and then update each report manually or possibly using the ReplaceConnection API ( search SCN and KBA systems for samples on how to ) to update the reports on the fly.

But always better to start off with valid reports with valid connection properties.

Don

[Former Member · Accepted solution]

Hi Don,

Currently we are using

SQL native client Version : 10.52.4000.0

The confusing part is,

.net code is working for all other CR X1 reports except for few reports. so there should be no SQL native client problem. Since this "Error Report" is a copy of another report thats working fine in .net, so why is it giving database connector error for few reports?

We cannot upgrade it to latest version, as it many not support delphi 7.

[former_member11696 · Accepted solution]

What does Delphi have to do with this list?

Crystal Report XI

Visual studio: 2012

MSSQL: 2008 R2

Crystal Report: 11.0.0.2495 [SP: 4]

.Net Version: 4

Crystal Decision dll version: 13.0.2000.0

Download the trial for CR 2013 and see if it works in CR Design and then your app.

Don

[Former Member · Accepted solution]

Our report guy works with delphi, so he has to make a report thats compatible with delphi. Then I have to call that report and use it in .net without any modification. So on deployment we can send one report and I am not allowed to change anything from Report.

[former_member11696 · Accepted solution]

Ah, since XI does not support SQL Server R2 you don't have much to work with...

All I can say is try it in .NET and CR 2013 and Native 10 client and if it works there you know the answer... IF it doesn't work then look at Profiler to see what the SQL is being sent by CR and for current versions you can enable CRLogger, search and you'll find the KBA on how to enable it. We don't have one for XI though.

Don

[Former Member · Accepted solution]

"Ah, since XI does not support SQL Server R2 you don't have much to work with..."

Crystal Reports 2008 (and 9.1, XI R1, XI R2) - Version and Download information for Service Packs - ...

I agree in this case but why my other Crystal Report XI Reports are working fine?

Its only with 3 reports that I have a problem and These 3 reports are copy of working version with one different parameter. The datatypes used in crystal report are just "String","Number".

And the error "Database Connector Error" is kinda vague.

Currently used in VS

In SQL Profiler, I don't see StoredProcedure being executed.

For Testing Purpose

. Removed SQL connection

. Removed Grouping/Sorting/Formula/Parameters

. Loaded Report only

After Executing in ASP.net, When prompted for SQL connection and Parameters, default values are added. Works for all Crystal Report XI reports except those 3. But that same report works in VS Crystal Report Preview in VS 2013.

[former_member11696 · Accepted solution]

I can't say for sure. All I now is MS has limited support for MDAC DB drivers.

Have you tried creating a new report based on that SP? Could be the files are partially corrupted.

Download the trial version of CR 2013 and install it on a different PC than what you are Developing on, there may be version issue if you do.

And the Assembly version is always the same. You can browse the \Windows\assembly folder or search for crpe32.dll or add this to your app to get the dll version:

Public frmMain()

foreach (Assembly MyVerison in AppDomain.CurrentDomain.GetAssemblies())
{
    if (MyVerison.FullName.Substring(0, 38) == "CrystalDecisions.CrystalReports.Engine")
    {
        //File:             C:\Windows\assembly\GAC_MSIL\CrystalDecisions.CrystalReports.Engine\13.0.2000.0__692fbea5521e1304\CrystalDecisions.CrystalReports.Engine.dll
        //InternalName:     Crystal Reports
        //OriginalFilename:
        //FileVersion:      13.0.9.1312
        //FileDescription:  Crystal Reports
        //Product:          SBOP Crystal Reports
        //ProductVersion:   13.0.9.1312
        //Debug:            False
        //Patched:          False
        //PreRelease:       False
        //PrivateBuild:     False
        //SpecialBuild:     False
        //Language:         English (United States)

        System.Diagnostics.FileVersionInfo fileVersionInfo = System.Diagnostics.FileVersionInfo.GetVersionInfo(MyVerison.Location);
        txtRuntimeVersion.Text += fileVersionInfo.FileVersion.ToString();
        // check if CrsytalDecisions.Enterprise dll's can be loaded ( Anything but Cortez - managed reporting )
        if (fileVersionInfo.FileVersion.Substring(0, 2) == "13")
        {
            btnRasOpen.Enabled = false;
        }
        CRVer = fileVersionInfo.FileVersion.Substring(0, 2);
        return;
    }
}

//VB code
//Imports CrystalDecisions.CrystalReports.Engine
//Imports CrystalDecisions.Shared
//Imports System.Reflection
//Imports System.Runtime.InteropServices

//Public Class Form1

//    Private Sub CrystalReportViewer1_Load(sender As Object, e As EventArgs) Handles CrystalReportViewer1.Load

//        For Each MyVerison As Assembly In AppDomain.CurrentDomain.GetAssemblies()
//            If MyVerison.FullName.Substring(0, 38) = "CrystalDecisions.CrystalReports.Engine" Then
//                'File:             C:\Windows\assembly\GAC_MSIL\CrystalDecisions.CrystalReports.Engine\13.0.2000.0__692fbea5521e1304\CrystalDecisions.CrystalReports.Engine.dll
//                'InternalName:     Crystal Reports
//                'OriginalFilename:
//                'FileVersion:      13.0.9.1312
//                'FileDescription:  Crystal Reports
//                'Product:          SBOP Crystal Reports
//                'ProductVersion:   13.0.9.1312
//                'Debug:            False
//                'Patched:          False
//                'PreRelease:       False
//                'PrivateBuild:     False
//                'SpecialBuild:     False
//                'Language:         English (United States)

//                Dim fileVersionInfo As System.Diagnostics.FileVersionInfo = System.Diagnostics.FileVersionInfo.GetVersionInfo(MyVerison.Location)
//                MessageBox.Show(fileVersionInfo.FileVersion.ToString())

//                Return
//            End If
//        Next

//        Dim Report As New CrystalDecisions.CrystalReports.Engine.ReportDocument
//        Report.Load("C:\reports\formulas.rpt")
//        CrystalReportViewer1.ReportSource = Report
//    End Sub

//End Class

//
// TODO: Add any constructor code after InitializeComponent call
//
}