Improve company productivity with a Business Account.Sign Up

x
?
Solved

Crystal Reports XI sub report will not update stored procedure

Posted on 2011-03-01
10
Medium Priority
?
853 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 101

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 101

Accepted Solution

by:
mlmcc earned 668 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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 

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 101

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 35

Assisted Solution

by:James0628
James0628 earned 668 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 664 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 35

Expert Comment

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

 James
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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.

Join & Write a Comment

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…
Free Data Recovery software is an advanced solution from Kernel Tools to recover data and files such as documents, emails, database, media and pictures, etc. It supports recovery from physical & logical drive after a hard disk crash, accidental/inte…
Watch the video to know the process of migration of Exchange or Office 365 mailboxes in absence of MS Outlook. It is an eminent tool which can easily migrate Public, Archive user mailboxes from one another Exchange server and Office 365. Kernel Migr…

607 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