Solved

crystal reports 10 drops all database fields when verifying database

Posted on 2016-08-18
17
25 Views
Last Modified: 2016-08-28
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.
0
Comment
Question by:CASorter
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 4
  • 3
  • +1
17 Comments
 
LVL 18

Expert Comment

by:vasto
ID: 41761429
Did you change the name of the stored procedure ?
0
 

Author Comment

by:CASorter
ID: 41761450
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
 

Author Comment

by:CASorter
ID: 41761455
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 100

Expert Comment

by:mlmcc
ID: 41761461
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
 

Author Comment

by:CASorter
ID: 41761503
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
 
LVL 18

Expert Comment

by:vasto
ID: 41761541
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
 

Author Comment

by:CASorter
ID: 41761595
no apparent affect...   pretty sure it has  nothing to do with sql.
0
 
LVL 35

Expert Comment

by:James0628
ID: 41762278
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
 

Author Comment

by:CASorter
ID: 41762455
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 41762511
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
 

Author Comment

by:CASorter
ID: 41762564
yes..


still banging on it...
0
 
LVL 18

Expert Comment

by:vasto
ID: 41762790
Is this SQL server database ?
0
 

Author Comment

by:CASorter
ID: 41762794
yes.
0
 
LVL 18

Expert Comment

by:vasto
ID: 41762911
Are you using dbo or another schema ?
0
 

Accepted Solution

by:
CASorter earned 0 total points
ID: 41767257
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 41767334
Glad you were able to solve it.

mlmcc
1
 

Author Closing Comment

by:CASorter
ID: 41773587
could not resolve problem ..   bought newer version of software
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Suggested Courses

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question