Failure to retrieve data from the database (Vendor Code 6550)

[Former Member]

I am having a trouble with two of the 6 reports that I have created. I am using Crystal XI and Oracle 10g. The underlying database object is a stored procedure that accepts as input a start time as a TIMESTAMP, an end time as a TIMESTAMP and a furnace number as a number. I have a cursor ref as a return parameter.

When I run the stored procedure in Oracle I get the selected records and can view them on the screen.

When I refresh data on the report in Crystal XI developer, the designer asks me for the start and end time and furnace number. When I input these values the report displays properly.

When I launch the report from my VB .NET 2005 application I am asked for the username and the password for the database (this is another problem I need to solve as this information I put into the program seems to be ignored in my program) then I receive the following failure message:

Failure to retrieve data from the database (Vendor Code 6550)

When I launch the other reports, they only ask me for the username and password then they display the proper data.

The main difference between my other reports and the two that are returning the above failure code is that the other reports are to either tables or views. The two that don't work are tied to stored procedures. I there any way that I can solve this problem or at least get more information?

Any help would be greatly appreciated. Let me know if you need any other information.

[Former Member · Accepted solution]

Hello Robert,

I have seen a similar issue with stored procedures on oracle 10g.

The issue was gone when they applied SP1.

Have you applied any [Service Packs for CR XI|http://resources.businessobjects.com/support/additional_downloads/service_packs/crystal_reports_en.asp#CRXIR1] ?

Hope this helps

Falk

[Former Member · Accepted solution]

No luck. I installed all of the service packs up to SP4. I am still having the same problem. After I upgraded I opened the reports, made a change to the reports and saved them. I also deleted the crystal references from the VB application and added the references back in. None of this helped. I am still seeing the same results when I eliminate all fields from the report that are tied to the stored procedure, it works. As soon as I add a field I get the error.

Is there anything I can do to find more information about the error code? I am not even finding any info about the error I am getting back.

[former_member11696 · Accepted solution]

Hi Robert,

Create a new SP that does not use time or date fields. It may be the format of the timestamp field you are entering in is not formatted correctly. Are you modifying these fields at runtime?

Also, as for the log on issues, search the forums for sample on setting log on properties and methods. Oracle doesn't require a database to be specified. If it's prompting usually something is missing.

Try exporting the report ot RPT format, saves data with the report, and then preview in your app, it should not require hitting the database or prompting for parameters. Enable the refresh button and click on it in the viewer and see what the default settings are.

Thank you

Don

[Former Member · Accepted solution]

First off, to all who are helping me in this problem thank you. You have given me at least another way of looking at the problem.

Out of frustration, ego and glimmer of hope that there might be some tie-in between my login problem and the stored procedure problem, I set out to fix the login problem. I did that this morning with the help of the samples that Don pointed me to. I no longer have the problem where I need to login.

I then created a new stored procedure which has no input paramters. The new stored procedure's name is: TestGetFuelLog. The input parameters are now hard coded for start time, end time and furnace number. All the stored procedure does is return a CURSOR REF.

Now when I display the report from the Crystal Reports design program I get the records selected from the hard coded parameters. When I load the same report into my viewer via VB .NET I get the following error message:

The Table 'TESTGETFUELLOG' could not be found.

Error in File C:\blah\blah\blah\Test1.rpt

The table could not be found.

I thought this was odd that the stored procedure is refered to as a table. Is this normal? Might there be some protection on accessing stored procedures that I have to change realising that the Crystal Reports Designer can access the stored procedure with no problems.

Any help would be greatly appreciated.

[Former Member · Accepted solution]

How are you passing credentials to the database? Is it possible that the credentials you're using don't have access to that stored procedure? (This sort of thing usually comes up when the application is supposed to be impersonating.)

[Former Member · Accepted solution]

Once again thanks to everyone for their help. No one answered the question exactly but everyone nudged me in the right direction.

For the sake of completeness I thought I would finish the thread with the solution so that anyone coming afterwards can learn (I hope).

Before I decided to go directly to the database I used to have an Access database that resided on the local machine. My server process would create a file on the server, the HMI client would be sent a message that the file was ready and the HMI client would FTP the file to the local machine. After the FTP my software would populate tables in the Access database and then the user could choose to view the Crystal report which would hit the local Access database.

When I made the change to the program I stripped out all of the code to manage the Access tables and FTPing of the files. What I did not get rid of was a subroutine to connect to the Access database. I was populating the login information for Oracle correctly and it worked on most of the reports but for some reason this additional connection to the Access database was causing problems with the reports that were relying on stored procedures. It thought they were tables. As soon as I deleted this connect procedure to the Access data base everything worked.

Once again thanks for the input.

[Former Member]

This might be due to schema hasn't not set well. Please try below. I am sure this should work:

                ConnectionInfo connInfo = new ConnectionInfo();

                connInfo.ServerName = "dbserver";

                connInfo.DatabaseName = "databasename";

                connInfo.UserID = "userid"

                connInfo.Password = "password";

     private void SetDBLogonForReport(ConnectionInfo connectionInfo)

     {

            this._report.DataSourceConnections.Clear();

            Tables tables = this._report.Database.Tables;

            foreach (CrystalDecisions.CrystalReports.Engine.Table table in tables)

            {

                TableLogOnInfo tableLogonInfo = table.LogOnInfo;

                tableLogonInfo.ConnectionInfo = connectionInfo;

                table.ApplyLogOnInfo(tableLogonInfo);

                table.Location = connectionInfo.DatabaseName + ".dbo." + table.Location;

            }

     }

[Former Member]

I am now trying Don Williams suggestion on Aug 26, 2008 11:43 PM. I am exporting the main report once it is loaded from the .NET web application, and I will try to reopen it at the Crystal Report Developer to see what's going on.

When I go to the main report's subreport hyperlink and right click it will actually have an option to Edit Sub Report as per the following print screen.

I use this "edit sub report" to both explore the "SQL Explorer" for both the Designed Sub Report against the exported RPT sub report.

SQL Explorer View for Designed Sub Report

SQL Explorer View for Exported Sub Report

I don't understand why does the SQL Explorer lay out is different between the Designed Sub Report against the Exported Sub-Report Lay out. Is this caused by the way the .NET web application or is it caused by the way I assigned the datasource?

[former_member11696]

Those screen shots don't tell us anything.

Click on Database, Set Location and expand the connection info. This will tell you what the connection info is. Both Main and Subreports connections should have the same name, if not then the subreport code is not being logged on.

Don

[Former Member]

Dear Don,

My .NET web page vendor have replied to me stating that the Code is not the issue, he tried to insert a simple one query sub report to my preexisting main report, and it is able to load with no issues. This is what hey have done.

1) Create a new sub report link to the main report

2) He later added a connection to the database and executed a simple command to
retrieve some data.

3) He deploy the new main report to the .NET web application and got the screen below:-

4) He then tested the newly created sub report to see if it could load and it loaded successfully

What he further suggested is that I do something similar, by adding a simple sub-report to my existing report to make sure that it works and then gradually add complexity to my report (i.e. pass parameters to it, call stored procedures etc) and build up the sub-report that I require.  It is possible at some point I will encounter the error that I am getting now, but as I have gradually built the report I will know what it is that I have added that has caused the problem.

Any other better suggestions?

[Former Member]

I have a similar problem, the only difference is that the crystal report error is only thrown when I try to load the sub reports at my web application. When I deploy my report to the Web application the main report loads up fine but when I try to launch the sub reports from the main report it can no longer load.

But when I use the Crystal report developer I am able to load the main report and it's associated sub reports without any issues.

My issue only occurs when I try to load the sub report in the web application. The following sections shows how my reports look like both in my .NET web application and also at the Crystal Report Development IDE.

Failure: Loading my Reports from .NET Web Application

1) When I load from the web application I am able to load the main report as per the print screen below.

2) When I try to click on the hyper links to enter the sub reports I am now able to load them, both sub reports cannot load and will throw the same error "

Failed to retrieve data from the database. Details: [Database Vendor Code:

6550 ] Failed to retrieve data from the database. Details: [Database Vendor

Code: 6550 ] Failed to retrieve data from the database. Error in File OAWR3011A

{CEC8A94A-4490-4640-95FB-2739A679978B}.rpt: Failed to retrieve data from the

database. Details: [Database Vendor Code: 6550 ] "

Success: Loading my Reports from Crystal Report Development Tool

1) This is my Main Report Loaded from the Crystal Report Development Tool, the first two hyperlinked fields are links to my sub reports, step number 2 and number 3 shows my sub reports when I load it using Crystal Report Developer

2) Sub Report 1 Loaded Successfully, when I click on the fields in the first link

3) Sub Report 2 loaded successfully when I click the second field hyper link

[Former Member]

I have tried "Don Williams suggestion on Aug 26, 2008 11:43 PM" to export the Main report that is loadable from the web application into an RPT file and opening the same RPT file on the Crystal Report Development tool.

I am able to open the main report as per the print screen below:-

From the Crystal Report Development Tool, I click on the refresh button and was prompted for log in credentials to the database, I keyed in the credentials accordingly and the data was re queried.

The next thing I try to do is to sub report by clicking on the hyperlink to the sub report hyper link that is as marked by the previous print screen. But I was prompted for another Database Log in Credential Screen, that is so wired, I already keyed it in for the first time.

This is not suppose to happen because both the main report and the sub report queries from the same Oracle database. I tried to key in the credentials but the DB log in screen jut keep popping up without loading the sub reports as per the print screen.

[former_member11696]

Hi Kian,

Not the same issue... CR makes a separate connection for EACH subreport.

Code is not complete but you get the idea.... loop through all the sections looking for subreports and set the log on info, same as the main report:

//loop through all the sections to find all the report objects
foreach (CrystalDecisions.CrystalReports.Engine.Section crSection in crSections)
{
    crReportObjects = crSection.ReportObjects;
    //loop through all the report objects to find all the subreports
    foreach (CrystalDecisions.CrystalReports.Engine.ReportObject crReportObject in crReportObjects)
    {
        if (crReportObject.Kind == ReportObjectKind.SubreportObject)
        {
            //you will need to typecast the reportobject to a subreport
            //object once you find it
            crSubreportObject = (CrystalDecisions.CrystalReports.Engine.SubreportObject)crReportObject;
            string mysubname = crSubreportObject.SubreportName.ToString();

            //open the subreport object
            crSubreportDocument = crSubreportObject.OpenSubreport(crSubreportObject.SubreportName);

            //set the database and tables objects to work with the subreport
            crDatabase = crSubreportDocument.Database;
            crTables = crDatabase.Tables;
            tableIndex = 0;
            bool SecureDB;

            //loop through all the tables in the subreport and
            //set up the connection info and apply it to the tables
            foreach (CrystalDecisions.CrystalReports.Engine.Table crTable in crTables)
            {   // check if integrated security is enabled
                SecureDB = crSubreportDocument.Database.Tables[tableIndex].LogOnInfo.ConnectionInfo.IntegratedSecurity;
                string TableName = crTable.Name.ToString();
                tableIndex++;
                crConnectioninfo.ServerName = "VMW2K8CRSE2K8";
                //if (!SecureDB)
                {
                    crConnectioninfo.UserID = "sa";
                    crConnectioninfo.Password = "PW";
                    crConnectioninfo.DatabaseName = "QTYLTD";
                }

                    crTableLogOnInfo = crTable.LogOnInfo;
                    crTableLogOnInfo.ConnectionInfo = crConnectioninfo;
                    crTable.ApplyLogOnInfo(crTableLogOnInfo);
            }
        }
    }
}

rpt.VerifyDatabase();

[Former Member]

Dear Don,

Thank you so much for your prompt response, I have forwarded your recommendation to our .NET web application vendor, the .NET web application is actually owned externally by a vendor, we don't have access to the codes, what we do is just created custom reports from CR and plug it in to their .NET web application, I have forwarded you code snippet to them, we are now awaiting their response. There are from UK and our time zone differences would mean that they will only respond in about 7 to 8 hours time.