crystal reports 10 drops all database fields when verifying database

i made a change in a stored procedure that added a field.
the field does not show up in the database fields list of the report.
i do a verify database.
enter the parameters
message comes up..   table RPT_MyReportSP;1 does not exist.  Remove this table from report?

if i say yes...  all the database fields are removed from the report...   as you might expect.


after i redo the  Set DataSource Location and point at --THE SAME--  stored procedure i was trying before...   it connects and pulls in the new field.

if i say no... i keep the database fields in my report, but the new field from the stored procedure is not in the list.

how do i refresh the database field names and not lose all the already placed database field names in my report.
CASorterAsked:
Who is Participating?
 
CASorterConnect With a Mentor Author Commented:
its not a sql thing...

i finally gave up and bought CR 2016...   which updates the data fields without breaking the report.

thanks for your input
0
 
vastoCommented:
Did you change the name of the stored procedure ?
0
 
CASorterAuthor Commented:
no. its the same..

i have tried the girations suggested by




quick version of above...  
1. update sp using set datasource location
2. in database expert, remove sp from list of selected tables...   at this point even though it prompted that "there were feilds in use"  it was not supposed to remove the fields (it did)  then readd the SP to the report...
3. it was supposed to not blow away the fields...   it did.
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
CASorterAuthor Commented:
suggested solution

here is the link that had the suggestions above in...

i can get the changes in by letting CR10 zap the database names and then i have to readd all the fields and logic in the report...   a real PITA.
0
 
mlmccCommented:
Is this a complex SP or a single select?

What database?

How are you connecting to it?

Have you tried forcing the report to logoff the data source then do the VERIFY?

mlmcc
0
 
CASorterAuthor Commented:
1. somewhat complex..
2.MSSQL 2014
3.ODBC
4. yes.. no affect...

as a test
i just did a simple select stored procedure
with 2 field outputs
create PROCEDURE [dbo].[aaa_GetOrder](@orderIntId int)  as
Select o.orderdate,o.orderextid From Orders o
Where O.OrderIntId = @orderIntId
i used the crystal 10 to start a new report.
found the newly added sp in the list of stored procedures in the connection.

added the sp ..
and my fields were in the database fields list.

i then added the fields to  the design.
clicked on refresh lightning bolt to get the data
and it said    the database table "aaa_Getorder;1" cannot be found. proceed to remove this table from report?
(which i didnt really expect...   as i had JUST added it.)

why cant you just do a refresh.. (somehow) and get the new output fields of a stored procedure????
0
 
vastoCommented:
try adding
set nocount on

ALTER PROCEDURE [dbo].[aaa_GetOrder](@orderIntId int)  as

set nocount on

Select o.orderdate,o.orderextid From Orders o
Where O.OrderIntId = @orderIntId
0
 
CASorterAuthor Commented:
no apparent affect...   pretty sure it has  nothing to do with sql.
0
 
James0628Commented:
I'm honestly not sure what this will tell us, but if you create a new report and have it use the old stored procedure, with the new field(s) added, does the new report connect to the SP and see all of the fields?

 James
0
 
CASorterAuthor Commented:
yes... AND if i say yes when it verifies in the existing report (to the question ..  cant find the sp, do you want to remove it?  ->>Yes)  
i can then re-link to the samenamed stored procedure and it sees the new fields.

i just have to put all the database fields back into the report  (because i had to say yes to remove it)
0
 
mlmccCommented:
I saw one discussion in the SAP (Crystal) KB that seemed to indicate you need to create a NEW connection to the SP then use the SET DATA SOURCE location.

Have you tried logging off the data source with the report open then reconnecting through a refresh?

mlmcc
0
 
CASorterAuthor Commented:
yes..


still banging on it...
0
 
vastoCommented:
Is this SQL server database ?
0
 
CASorterAuthor Commented:
yes.
0
 
vastoCommented:
Are you using dbo or another schema ?
0
 
mlmccCommented:
Glad you were able to solve it.

mlmcc
1
 
CASorterAuthor Commented:
could not resolve problem ..   bought newer version of software
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.