Need to change the DATABASE NAME reference 1

[dean12]

OK, so I created a report with an ODBC entry that pointed to a database named "XXX". I populated the report with data fields and I have a SQL query that says something like:

SELECT
ACC_TRANS."CHARGE_NO",
FROM
{ oj ((("XXX"."dbo"."ACC_TRANS" ACC_TRANS INNER JOIN "XXX"."dbo"."CHRG_DETAIL" CHRG_DETAIL ON

. . . . . and so on

The key part is the "XXX" database qualifier. I must get this changed to a different name. In reality, the ODBC should have pointed to database "ABC".

If I remove the table, all my fields drop off the report. I'm told that if I edit the SQL statement directly, then it will not be updated in the future as new tables or fields are added.

Any thoughts on how to change this?

 

[lyanch]

Use set location

 

[dean12]

Looks as if the DATABASE -->SET LOCATION option does the trick.

 

[CESHRPR]

I hate to look stupid, but could you take this a step further? I have the same problem, where the development database shows up in the SQL query and eventually it will need to be the production database. Did you have to change the location for one table at a time (if so how? On the first screen or second?), or could you point to an entirely different database? I tried changing it in the initial dialog box for 'set location' in the single line where the table name is. I deleted the db name in front of it for each of the 7 tables. When I closed it I got the generic Database Error screen 7 times.

In short, would you be so kind as to walk me through the steps of what you did after you picked 'set location' off the menu? Id be very grateful. I hate having half the answer.

 

[lyanch]

click the set location. One table name will be highlighted. Click set location and browse to that table in the database that you want to connect to. (the selection screen looks just like the one you use to add tables to start with). Once you select it, you will get a dialog box asking if you want it to propogate the changes across all tables in the "from" database, select yes.

They all should be changed. This works MUCH better than it used to!

Lisa

 

[Guest_imported]

Yes, I agree. Sorry I didn't respond quicker but I was out of pocket. That's exactly what I did, Set Location off the menu bar (down from Database maybe?).

 

[CESHRPR]

Thanks to both of you. That works great. I'm just not sure it's what I need. My reports have to run on several similar databases. I develop them on one database (XXXDR). They are tested on a QA database (XXXQR), then when complete they are moved to production and run on XXXPRD. When I wrote them on DR and noticed the DB name showing in the SQL query, I moved one to QR for testing. It continued to pull the data from DR since I hadn't set the location. If I use set location to change it to QR, I'll have to do it again after testing to move all these reports to PRD. There are dozens that would have to be changed DR to QR, then to PRD. I don't like changing things post-testing. I was actually hoping for a default that would say 'pull the tables from whichever database you're connected to' since there is only one for each region (DR, QR, PRD). I want to just use the table name and not include the DB name. I just can't figure out how to do this. But thanks for your help.

 

[lyanch]

You can do that with ODBC. Just change where the driver points to. I have also had *some* success by removing the xxxx.dbo. portion of the tables. It is tricky to get it to actually STAY removed. Once you have it removed, however, then it does indeed read from the source that it is connected to.

One note, CR9 was released today. The developer is supposed to have complete control over the SQL (finally). That should help address this issue. Not that you want to chance version mid stride but at least in the future you might have a better solution.

Lisa

 

[CESHRPR]

Thanks - this is great information!