Error with adding stored procedures

[Former Member]

Hello all,

I am trying to add a Stored Procedure through the Database Expert to my report. My Tables and Views are working fine. I can see my Stored Procedures, but when I press "OK" on the Enter Value prompt, I get a Database Connector Error: 'Cannot obtain error message from server.' The Stored Procedure is not added to my report.

Alternatively, if I don't press okay but hit the "X" to close the Enter Value prompt, the Stored procedure is added to my report, but the table shows up blank.

I am using Crystal Report 2008 with Microsoft SQL Server 2005, and an ODBC connection (I have tried both SQL Server and SQL Native Client as drivers.

Has anyone experienced this before or know of a possible explanation?

Thank you in advance and hope this ends up helping someone else also!

Sara

[Former Member]

Hi Sheel2,

I think the parameter value which is passing to stored procedure is not proper. Can you check the parameter data type and the value which you are entering in the parameter are same data type?

When you click on X mark then the parameter is taking Null value and the stored procedure is getting executed, since you do not have Null values in your database field it is giving you the blank tables.

Thanks,

Sastry

[Former Member]

Hi Sastry,

Thank your for replying so quickly!

I have tried using "Set to Null" in the Enter Values prompt, as well as entering values (int, int, boolean, boolean). The parameter data type and the value which I am entering is the same data type. I should mention that I am using a sub report and would ultimately like to have the first int in the sub report link to main report.

I don't know if this helps, but the blank table that I am mentioning is the one in the Link tab in database expert. It does not have any fields within.

Your response was very helpful. What other ideas should I try?

Thanks,

Sara

[Former Member]

Hi Sara,

Can you give the following information :

Are you using stored procedure for your main report and sub report also ?

In which report you have this stored procedure parameters ?

How you are linking your sub report using parameters or using database fields ?

Thanks,

Sastry

[Former Member]

Hello Sastry,

This is the only stored procedure I am using, and it is only in the sub report.

I am linking the main report to the sub report using a parameter "ProjectID".

There are four parameters for the stored procedure:

int-ProjectID,

int-ProjectScheduleID

bool-isTimeEntry

bool-isCriticalPath

The two integers are values taken from the fields in the Tables of the report. ProjectID and ProjectScheduleID are from different tables.

I believe the booleans only exist in the stored procedure. I can't confirm this as I did not create the stored procedure. I have been told to set the two booleans to false though.

I have tried setting the two booleans to false and making the two integers null, but I get the same error message.

What did you have in mind?

Thank you,

Sara

EDIT: I should add that the stored procedure is supposed to return one integer- PercentComplete of the Project Schedule. When I add the stored procedure by using the red "X", the four input parameters show up in the parameter fields, but I don't see the PercentComplete anywhere.

Edited by: shlee2 on Apr 1, 2009 8:45 PM

[Former Member]

Hi Sara,

We will have to check where exactly is the issue ie. whether it is in sub report or in main report. I think there is some problem in linking / passing main report field values to sub report parameters.

Do the following :

Suppress the section where you have sub report placed in your main report and run the report. It should give some values with ProjectID and ProjectScheduleID. If you are not getting any values then there is some problem with your database or tables.

In this method it will run only main report and it will not run sub report.

2nd method

Go in designer and double click on sub report, it will open sub report design now you refresh the sub report. It will ask for 4 parameter values. Try to give the values which are exist in your database. Now you sub report should get some values.

In the above two methods, if you are getting data then there is some problem with linking of your sub report or some issue while passing main report field values to sub report parameters.

Hope this helps you.

Thanks,

Sastry

[Former Member]

Hi Sastry,

The main report runs fine.

When I refresh the sub report, I get prompted for:

Pm-ProjectDetails.ProjectID (the parameter linked from the main report using "Subreport Links")

ProjectID (linked to Pm-ProjectDetails.ProjectID using "Record Expert")

It does not prompt for the other 3 parameters of the stored procedure:

ProjectScheduleID

isTimeEntry

isCriticalPath

I get a value from the sub report, but it is from the tables that are already there.

From what I had read online, when I add the stored procedure through "Record Expert", it is supposed to automatically create a field for the result. Is that incorrect? When I added a stored procedure, 4 input parameters were created under "Parameter Fields", but not the result: percentComplete.

Am I simply setting up the stored procedure incorrectly within the sub report?

Thanks,

Sara

Edited by: shlee2 on Apr 2, 2009 4:31 PM

[former_member11696]

Hi Sara,

What happens if you create a new report off the stored procedure, no subreport? When it prompts for the parameters can you enter in them and get data?

We can enable CR database logging if required to see what Cr is doing when trying to execute the stored procedure.

If you run SQL Profiler are what are you seeing coming from Crystal? It should look the same as what you see in Show SQL in the Designer. Possibly our SQL interpreter is having issues with your SP.

Thank you

Don