Print Page | Close Window

How to remove the hardcode dabasename from CR

Printed From: Crystal Reports Book
Category: Crystal Reports for Visual Studio 2005 and Newer
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=11628
Printed Date: 21 Apr 2025 at 5:12am


Topic: How to remove the hardcode dabasename from CR
Posted By: Seven.Qiu
Subject: How to remove the hardcode dabasename from CR
Date Posted: 08 Nov 2010 at 8:25pm
In CR, I has set a store procedure use database expert,
but now i found when my client change the connect database, the client still get the data from the database where i setted in RPT but not the new DB,
others RPT files is normal.
could anyone help me?


Replies:
Posted By: Emir_W
Date Posted: 08 Nov 2010 at 9:42pm
how you connect the report w/ data? ODBC?

make sure the datasource for this report are same as other.
you can check for below points for this report:
- ODBC name
- Server name used inside ODBC
- DB name used inside ODBC

Other point is:
make sure the Stored procedure that you use on the report also exist on other DB.

hope it help.



-------------
Emir W

Posted By: Seven.Qiu
Date Posted: 08 Nov 2010 at 9:52pm
Originally posted by Emir_W

how you connect the report w/ data? ODBC?
make sure the datasource for this report are same as other.you can check for below points for this report:- ODBC name- Server name used inside ODBC- DB name used inside ODBCOther point is:make sure the Stored procedure that you use on the report also exist on other DB.hope it help.


I connect the Database use OLE DB,
My issue is just in the RPT file,it connects to DB is in HardCoded,but not dynamic datasouce.

Posted By: Emir_W
Date Posted: 09 Nov 2010 at 12:03am

if possible, you can add 2 lines below before your in your SP:
  --- add these 2 lines ---
  Use [DBname]
  Go
  ----------- your SP ------

the bad thing is if your customer need to run same report for other DB.
in this case you need to modify the first line ("use [dbname]").


hope it help.



-------------
Emir W

Posted By: lockwelle
Date Posted: 09 Nov 2010 at 3:36am
have you application set the ole connection for the report.  It should point to the same one as the application (one would suppose).
 
my company uses what I now consider the best solution to this whole issue...it has the application pass the dataset to the report.  The report doesn't know or care where the data comes from.  As long as the data matches the data schema it expects, it displays the results.  This way you don't have to worry about the client changing databases.  The application knows where it is pointing and gathers the data from the correct database and passes it to the report.
 
Yeah, we provide a wizard to gather the parameters from the user as well, then get the data.  there is an up front cost to developing this style of report, but once you have the framework set up, the design of the report is way simpler as you have useful parameters that can be controlled as you want them (like a date editor where you don't need to include the time, lists as long as you want, and of course they are dynamic and cascading if you want) and your report doesn't care about the connection.
 
HTH

Posted By: Seven.Qiu
Date Posted: 09 Nov 2010 at 2:03pm
Originally posted by Emir_W

if possible, you can add 2 lines below before your in your SP:  --- add these 2 lines ---  Use [DBname]  Go  ----------- your SP ------the bad thing is if your customer need to run same report for other DB.in this case you need to modify the first line ("use [dbname]").hope it help.


when I select Show SQL Query in database field,it shows
"EVEREST_SAMPLE"."dbo"."C_BAFO_INVENTORY_AGING_REPORT";1 NULL, NULL, NULL, NULL, NULL, NULL
but the expect value should be
"C_BAFO_INVENTORY_AGING_REPORT";1 NULL, NULL, NULL, NULL, NULL, NULL
so add the statement "Use [databasename]" is unuseful.

Posted By: Emir_W
Date Posted: 09 Nov 2010 at 7:23pm
if you 'show SQL Query' it will gives you that line and you can't change it because its not editable.

that line it means that:
- Stored Procedure (SP) is exist in DB EVEREST_SAMPLE
- SP name is C_BAFO_INVENTORY_AGING_REPORT

in this case make sure that this SP is exist also in other DB that used by your client.
I assume you using SQL Server, if you able to open the SQL Srvr Mgmt Studio (SSMS) then make sure the SP is exist in DB that used by your Client.

- open SSMS
- select DB EVEREST_SAMPLE
- open your SP
   if you see these lines below then delete it
      USE [EVEREST_SAMPLE]
      GO
- select DB that used by Client
- compile it (Alt-X)

make sure the ODBC in Client is pointed to the correct DB.
then verify your report in CR and run it.

oops...
or you can also follow what lockwelle suggestion.
because it very flexible and we use it with same method for one of our client here.


hope it help.



-------------
Emir W

Posted By: Seven.Qiu
Date Posted: 09 Nov 2010 at 7:33pm
Originally posted by Emir_W

if you 'show SQL Query' it will gives you that line and you can't change it because its not editable.that line it means that:- Stored Procedure (SP) is exist in DB EVEREST_SAMPLE- SP name is C_BAFO_INVENTORY_AGING_REPORTin this case make sure that this SP is exist also in other DB that used by your client.I assume you using SQL Server, if you able to open the SQL Srvr Mgmt Studio (SSMS) then make sure the SP is exist in DB that used by your Client.- open SSMS- select DB EVEREST_SAMPLE- open your SP   if you see these lines below then delete it      USE [EVEREST_SAMPLE]      GO
- select DB that used by Client- compile it (Alt-X)make sure the ODBC in Client is pointed to the correct DB.then verify your report in CR and run it.oops...or you can also follow what lockwelle suggestion.because it very flexible and we use it with same method for one of our client here.hope it help.

I'm sure my sp is correct.
and another DB is same to the old DB,so I just want to delete the catalog and ownner info in Property of the SP from "Set DataSource Location".

Posted By: Emir_W
Date Posted: 09 Nov 2010 at 7:53pm
ok..
if you sure that another DB is same as old DB then you can do it by replacing the DB from 'Set Datasource Location'.

just make sure that your SP is really exist in other DB and it's not mention:
"USE [oldDB]
  GO"
if it is, then you need to delete those lines and compile it again.

and, you can create new ODBC and pointed to other DB.

from 'Set Datasource Location' you can change your current Datasource with the new one by:
(in Current Data Source)
- select your SP
- expand your SP
   it will show the detail / property for the SP
- click your SP

(in Replace with section)
- expand 'Create New Connection'
- select 'ODBC (RDO)'
- select your new 'Data Source name'
  provide all information as needed and you will see your DB
- expand 'dbo'
- find your SP
  make sure your SP name is same as 'Table Name:' in 'Current Data Source:'
- click 'Update'

done.
And don't forget to verify the report.


hope it help.



-------------
Emir W

Posted By: Seven.Qiu
Date Posted: 09 Nov 2010 at 7:59pm
Hi Emir_W,
Now I can change replace the DB from 'Set Datasource Location', but when I Updated it, it still shows the Catalog and ownner info in Property of the SP from "Set DataSource Location".
My others normal reports here has no the 2 infos.

Posted By: Seven.Qiu
Date Posted: 09 Nov 2010 at 8:05pm
I can't Follow lockwelle's suggestion because I just fix a bug,others report is normal.There's just 2 reports have the issues.

Posted By: Emir_W
Date Posted: 09 Nov 2010 at 9:47pm
can you refresh your report?
and make sure all data are valid from the other DB.


hope it help.



-------------
Emir W

Posted By: Seven.Qiu
Date Posted: 09 Nov 2010 at 10:15pm
I have refresh my report,
Others normal reports work fine with other DB.

Posted By: Emir_W
Date Posted: 09 Nov 2010 at 10:28pm
now the result is it ok (with the right DB) ??

Just wondering, how you write your SP?
from SQL server or directly from 'Write Command' in CR?




-------------
Emir W


Print Page | Close Window