Active TopicsMemberlistSearchHelpRegisterLogin

Data Connectivity
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Data Connectivity
Message Icon Topic: Changing Data Source Post Reply Post New Topic
Page  of 2 Next >>
Author Message
Snuffie
Newbie
Newbie


Joined: 31 May 2010
Location: Australia
Online Status: Offline
Posts: 3
Quote Snuffie Replybullet Topic: Changing Data Source
    Posted: 31 May 2010 at 6:58pm
hi, iv got an existing report that uses a view as the data source. iv been asked to switch the datasource to a stored procedure which is exactly the same as the view except for the fact that it is a stored proc.

i go to 'Set Datasource Location' -> select the view in the 'Current Data Source' window and select stored proc in the 'Replace with:' window.  I then click 'Update' expecting the view to be replaced by the stored proc.  well it doesn't get replaced and that's where i need help.  iv tried this a few times already to no avail.

the only alternative that i can think of is remove the view and add the stored proc manually.  i might as well re-write the report if i do it this way which is what im trying to avoid.

any suggestions?  thanks in advance.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 01 Jun 2010 at 4:07am
Are you sure it did not change?
The actual name will not alter but if you expand the Properties, the Table Name value should now be your stored proc name.
IP IP Logged
Snuffie
Newbie
Newbie


Joined: 31 May 2010
Location: Australia
Online Status: Offline
Posts: 3
Quote Snuffie Replybullet Posted: 01 Jun 2010 at 11:15am
you're right!  Smile

my bad that i didnt check the properties.  can i ask why the name of the data source didn't change to the stored proc and is there any way to change the Table name value to reflect the stored proc name?

thanks heaps.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 01 Jun 2010 at 11:28am
I do not know why it does not update nor do I know how to change it to reflect the 'current source'. Wish I did...
if you happen to find either please post what you find Clap
 
It can get to be very confusing later as you go back to work on these reports. Too many times I have gone into a view or stored proc to fix / update something and it never updates in the report. Takes me hours to realize that is because the report is not using that original source any more.
 
You may want to add in a suppressed Report Header A (visible in design mode) that clearly states for you or any other future designer some text like 'updated sources which required looking at the data source properties'.
Might save you a lot of headaches down the road.


Edited by DBlank - 01 Jun 2010 at 11:38am
IP IP Logged
Snuffie
Newbie
Newbie


Joined: 31 May 2010
Location: Australia
Online Status: Offline
Posts: 3
Quote Snuffie Replybullet Posted: 01 Jun 2010 at 11:32am
thanks heaps!  Smile
IP IP Logged
shell_l_d
Newbie
Newbie
Avatar

Joined: 08 Jun 2010
Location: Australia
Online Status: Offline
Posts: 4
Quote shell_l_d Replybullet Posted: 16 Jun 2010 at 3:51pm
Crystal Report 9 - replace SP - unable to open rowset
 
Need to be able to copy an existing rpt file & replace it's data source with another Stored Procedure which returns the same type of data. To save having to keep re-creating the same thing & have everything line up so can be used as subreports in a main report.

Eg: Report has:
* GroupName (string)
* SLA (string)
* Apr (int)
* May (int)
... etc
* Feb (int)
* Mar (int)

Steps I took:
1) Copy a *.rpt file & paste it to a new name
2) Open the new *.rpt
3) Database > set datasource > find new sp (CCCMonthlyReport_Sub6) & select it, click in top window on the sp name (CCCMonthlyReport_Sub5), click update
4) Database > database export > click on the SP name (on the right) & edit it to the new sp name (eg: CCCMonthlyReport_Sub5 change to CCCMonthlyReport_Sub6) (OPTIONAL)
5) Database > verify database
6) Run
7) Error msg box 1: Unable to open a rowset
8) Error msg box 2: Not supported. Details: Cannot obtain error message from server.

Regardless of doing step 4) it still has errors.

* Database > Show SQL Query still shows the old SP name (sub5).
* Field explorer shows new SP name
* Report Explorer shows old SP name - even if I right click one > select export > new > sub6 field > delete sub5 field > OK. When look at select expert again, it hasn't made the change.

Any help please?

Also posted here just now (post #6, 1wk ago- awaiting approval)... the solution didnt work:
http://www.crystalreportsbook.com/Forum/forum_posts.asp?TID=10149&KW=update+data+source&PID=32140#32140



Edited by shell_l_d - 16 Jun 2010 at 3:52pm
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 17 Jun 2010 at 4:02am
Sounds more like an rights issue to the source.
Try creating a report from scratch using the new source. Just a simple query to see if you can use it.
IP IP Logged
shell_l_d
Newbie
Newbie
Avatar

Joined: 08 Jun 2010
Location: Australia
Online Status: Offline
Posts: 4
Quote shell_l_d Replybullet Posted: 17 Jun 2010 at 2:26pm
Yes I can create a new one from scratch no problem, but that's the point, I dont want to have to to create a new report identical (pretty much) to an existing report but using a different SP. :)
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 19 Jun 2010 at 4:19am
I understood you wanted to replace but wanted you to test connectivity and access rights and validate the sp worked ok for crystal in general.
I do not know why you are getting the error but that error is not unique to swapping the data source so something else is going on.
Is there a missing column in the new SP that is being used in the report in a formula?
IP IP Logged
Emir_W
Senior Member
Senior Member
Avatar

Joined: 25 Apr 2010
Online Status: Offline
Posts: 228
Quote Emir_W Replybullet Posted: 08 Jul 2010 at 1:55am
i've done for some reports (not all).
 
what i did is:
 1. make sure that all fields in the report are exist in new SP
 
 2. noted for the fields in existing CR with fields in new SP (for mapping)
 
 3. add the new SP into CR
     so, there will be some tables (existing tables) + new SP
 
 4. remove the tables
     CR will gives some messages that there are fields from the table are used in the reports. it is ok.
 
 5. then put/replace the existing fields with the new one from SP
     including in Formula / parameters / conditions / groups / etc.
 
 6. verify the report and make sure no error.
 
 
hope it help.
 
Emir W
IP IP Logged
Page  of 2 Next >>
Post Reply Post New Topic
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.062 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.