Verify Database Not Adding New Field from Updated Stored Oroc

[earljgray]

Environment:
Crystal 2008
Oracle 11
Stored Procedure

A stored proc was updated to include a new field which would form a new group in the report. The report has been in production prior to the addition of the fieldfor three months.

Followed the procedure:
=================================================
If it is a new field, when you click on Verify Database you will receive a pop-up message stating…”The database file….has changed. Proceeding to fix up the report.”

Click OK to this message.

The new field added should now appear in your Field Explorer.

If it is a field name change or data type change, when you click on Verify Database under the Database menu you will receive the Mapped Fields dialog box.
==================================================

The EXPECTATION was that the new field would appear in the Field Explorer without any further ado.

In fact, the Verify routinr popped up the Mapped Fields box with the new field in the Unmapped Field quadrant as if there had been a data type change or field name change. Since this was the addition of a new field, I fully expected to see
”The database file….has changed. Proceeding to fix up the report.”
as I have seen over the last 15 years!

Attempts:
Log On/Log Off/ Log On to DB - Verify.... Now the db is up to date, but the field does not show

Changed Datasources - Verify.... The db is up to date, but the field does not show

No matter what has been attempted either on of two things happens:

On a 'clean' versions of the rpt file, tried each 'trick' - no field; but the db is 'up to date'

Saved each iteration, closed down CR, re-started and re-opened each iteration and did a Verify... no joy.

Any experiences similar - and, if so, were you able to overcome this? If so, any suggestions?

Thanks....


Crystal Reports Design/training/Consultation
ecsdata@ecsdata.com

 

[RTag]

Do you have different output from the stored procedure depending of a parameter?

For example :
if Parameter1=1
select a,b from ...
else
select c,d from ..

http://www.R-Tag.com

Viewer, scheduler and manager for Crystal reports.
Send you report everywhere.

 

[earljgray]

Hi!

Thanks for the interest in this little problem....

No, no parms at all. Straight call to a Stored Proc., one that was in service for a while before getting the added field.

Crystal Reports Design/training/Consultation
earljgray@gmail.com

 

[RTag]

Any chance your report is pointing to another database (test world or on local computer) ?
What is the database type?

http://www.R-Tag.com

Viewer, scheduler and manager for Crystal reports.
Send you report everywhere.

 

[earljgray]

Answers...

Oracle 11g
Stored Proc
File DSN is used - uniquely IDs that one DB

Crystal Reports Design/training/Consultation
earljgray@gmail.com

 

[RTag]

Can you check what is actually sent to the database ?
I don't know how to do this in Oracle. Just as an example: in SQLServer you can use SQLProfiler and it will show you any command sent to the server.

May be the report was pointed to a new stored procedure but the table name is still the name of the old stored procedure. Is this possible ?

http://www.R-Tag.com

Viewer, scheduler and manager for Crystal reports.
Send you report everywhere.

 

[earljgray]

Unfortunately, not.

All the procedures are tucked into a packlage body which forms the data source for numerous reports, each calling their own procs from within the package. Becasuse of the File DSN and the package concept, unless you REALLY go out of your way, it's almost impossible to select the wrong proc....

It doesn'tr ppear4 the issue is the data - the New Field appears in the Map Fields dialogue box when you cliok VERIFY DATABASE. The problem is, there should not *be* a Map Fields dialogue - CR should just suck in the new field and that's that.

It's almost as if the report 'thinks' the field was already within it.

Very frustrating...

Crystal Reports Design/training/Consultation
earljgray@gmail.com

 

[RTag]

Oh, I am sorry, I missed that part.
This may happen when a field is removed and a new one is added (which can be considered as a subcase of the name/type change you already mentioned).
Which field is shown on the left?
Do you have formulas with the same name as the new field?

http://www.R-Tag.com

Viewer, scheduler and manager for Crystal reports.
Send you report everywhere.

 

[hilfy]

Have you tried using Set Location instead of Verify Database? Sometime this will work better when you're dealing with Oracle Packages.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[earljgray]

To RTag:

Nope, no formula, and no fields removed/replaced with new. Just a standard procedure that had 1 field added. The {new field) is shown on the left quadrant; other fields of the same type (but not to be mapped to) are on the right.
Thanks

To hilfy

Tried Set Location. Even set the location to another version of the package, then to a standalone procedure, then back to the original package. Nada...
Thanks

Crystal Reports Design/training/Consultation
earljgray@gmail.com

 

[lbass]

If the new field is appearing on the mapping screen in the unmapped list, it implies that it is IN the report. What did you do in the mapping screen? Did you leave it unmapped and then hit okay?

-LB

 

[jlopeman]

Did you recompile the stored procedure?