Solved

crystal reports 10 drops all database fields when verifying database

Posted on 2016-08-18
17
17 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
  • 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
 
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 34

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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

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…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

758 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now