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.