error using SQL server stored procedure with Crystal 8

[jcaulder]

Hi,

I have a stored procedure and Crystal report that were both created by a vendor and shipped to us to use. However, the stored procedure had errors in it which I resolved by correcting a join condition in the sp. However, the client asked for an extra field to be added to the report. I assumed I could just modify the sp to select the new field and that Crystal would see the new field. However, the field added in the sp never shows up as available in the report. Furthermore, if I try to run the report based on the new sp(even though I'm not displaying the field in Crystal), I get a dll database error. I finally tried creating a new report using the unmodified sp as its database but I received the following:

DATABASE ERROR

There are no fields in the file: "PMMTEST2.dbo.Proc(up_rpt_expDetail)".

The end result of the sp is a union of 5 sql statements. There are also 7 parameter fields being passed to the sp.

Any ideas on what would cause these type errors?

Just for information, if I run the query generated by the sp in Query Analyzer, I receive no errors and get back an accurate result set.

Thanks in advance!

 

[synapsevampire]

Open the report in Crystal and select Database->Verify Database, that ought to clean it up.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[jcaulder]

Clicking 'Verify Database' gives me the same type errors. It first prompts me for parameter values then gives the same error I stated earlier.

Do all parameters have to be entered and entered correctly for the database connection to work on a sp?

It seems like it wants me to enter these parameters.

If so, what is the format for entering the various datatypes such as dates, text, sets, etc.

I am typing dates like: '2002-01-02 00:00:00.000'
sets like: '(1000,1001)'
text like: '1671'

Thanks again

 

[synapsevampire]

How you enter the parameters is based on the type used, and the version of CR.

Since these parameters are being generated by the SP, check the data types and expected format in the SP and pass them accordingly.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[jcaulder]

Looks like I have it working finally. The steps I have to follow are:

1) Open the Crystal report and 'Set Location' to my sp
2) 'Verify Database' in Crystal
3) Open SQL Server and drop the old sp
4) Create the new sp in SQL Server
5) Grant permissions on the sp
6) Re-open the report in Crystal and 'Verify Database' again
7) It will prompt to 'Fix the report' so say 'yes'

The new fields will be available after the report has 'fixed' itself. While this still isn't working consistently, it appears to be related to errors now in adding fields in the sp which is difficult because there are 5 sql statements unioned together.

Thanks for the assistance!!

 

[Mdavis123]

Hi,

I'm having a similar error message and no errors on the server.

I have added a couple of fields to the output, but crystal dosn't seem to want to re-verify. It just simply gives me an error.

I came back to work this afternoon to test it here and the same problem. I created this in 8.0 and now using 8.5. Im gonna get a machine with 8.0 on it and try it.

Otherwise I will try what you did. I can't even create a new report and point it to the SP. Like you, It runs great in QA.

MikeD

 

[Mdavis123]

JCaulder,

We 'Converted Drivers' to ODBC and were able to verify and run the report in question.

Not sure that this is the correct fix. We are testing still.

Will keep you advised.

MikeD

 

[jcaulder]

My original problem was resolved as stated in my earlier message. However, trying to create a new report based upon the same stored procedure does not work. I get prompted for parameters, I enter them and I am dropped right back to the 'Select Datasource' window as if I'd never selected a datasource. Maybe I'll try to connect through ODBC and see if there's any difference as you suggested.

 

[jcaulder]

Using ODBC gives the errors I reported initially in my original post(no fields in the datasource). Using the native SQL Server driver lets me get through with no errors but appears to completely ignore my datasource selection and parameters. Guess I'll keep playing around with this and hope I, or someone else, comes up with a solution.


Thanks for the replies!