Solved

Crystal Reports XI sub report will not update stored procedure

Posted on 2011-03-01
10
815 Views
Last Modified: 2012-05-11
I have a report, that fires a sub report.  The sub report's datasource is a stored procedure on a SQL Server 2008 database.    It works fine.
Then I realize I need the stored proc to return additional data elements.  So I add the new fields to the result set of the stored proc, and alter the stored proc in the database.     I can run the stored proc in the database directly (via management studio). it works fine.
So I attempt to refresh the datasource, using the usual methods, and I just cant get the sub report's datasource to see the new columns.  
In "normal Crystal", when I modify a table, and rerun, I get the "table XYZ has changed! Proceding to fix the report!"   I can't get this to happen in this scenario.

Aside from rewriting the whole damn thing, what am I missing?    
0
Comment
Question by:fhcdaver
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 35010655
Did you try VERIFY DATABASE when editting the subreport?

mlmcc
0
 

Author Comment

by:fhcdaver
ID: 35011004
yes.  
We also tried pointing the sub to another database.
the only thing we know works is that we change the sp to a new alias name, and re-work all the formulas, summaries, calculations, etc and basically rebuild the whole thing from scratch.
Even SAP's website says that.  I just have such a hard time believing that this is the best answer.
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 167 total points
ID: 35011159
That is the purpose of VERIFY DATABASE to force Crystal to go and read the stored procedure and update it.

Have you tried logging off the database in Crystal
Close Crystal
Open Crystal and logon the database

mlmcc
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

 

Author Comment

by:fhcdaver
ID: 35011655
I've even tried shutting down the whole computer and starting up again!
But i have not tried that exact sequence.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35011755
I recall having an issue when the report is just opened.  The connection seems to still be there so it doesn't really try to read the new one.

mlmcc
0
 
LVL 34

Assisted Solution

by:James0628
James0628 earned 167 total points
ID: 35015006
Could the subreport be using an old/different stored procedure?  Go to Database > "Set Datasource Location", open up the datasource under Subreports, open the Properties for the stored procedure and make sure the name is correct.

 If the name is correct, you could try using "Set Datasource Location" to change the datasource for the subreport, but just point it back to the same stored procedure, and then do a "Verify Database".  Or create a copy of the stored procedure, temporarily change the subreport's datasource to the copy, do a "Verify Database", change it back to the original stored procedure, and then do another "Verify Database".

 James
0
 
LVL 4

Assisted Solution

by:musalman
musalman earned 166 total points
ID: 35015053
Adding James Comment:

Try to Update Sub Report separately. I mean Open Sub Report apart from Main Report in Crystal and then try to Update the Data Source.
0
 

Author Comment

by:fhcdaver
ID: 35060625
Hi guys,
I got detoured from this issue with another couple of fires. I will give the datasource within the sub a try.
Dave
0
 

Author Comment

by:fhcdaver
ID: 35214873
Hi,
So we tried verifying the sub as if it were independent, as well as verifying it all, setting datasource, etc.  it just wouldnt work. Separately, the SAP blog suggested that the only thing to be done was to rewrite the entire thing, including all the formulas, etc.  very disappointing.  It's not the fault of the experts who tried to help, so I've apportioned the points accordingly. thanks for trying.
0
 
LVL 34

Expert Comment

by:James0628
ID: 35221466
Sorry we couldn't be of more help.

 James
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

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…
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 …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
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…

856 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