Solved

crystal reports 10 drops all database fields when verifying database

Posted on 2016-08-18
17
20 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
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…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

862 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