How to move a database without breaking reports

[malcolmt]

Hi there,

I'm using CR9/CE9 and (moving to) CRXI/BOEXI with reports based on SQL Server 2000 stored procedures via odbc file dsn.

I have a problem that I have been trying to work out with BO Tech Support and it's about to crash down on me.

Later this week one of our key SQL Server databases is to be moved to a different server/machine. The database name will remain the same.

This will require me to do a set datasource location for each report, migrate to enterprise production and then reschedule to get them going again!

Left as is, with the same dsn simply repointed to the new server results in a "Database Connector Error".

Tried changing database connection information via CMC/process/database, to erase database name & prefix but still get the same error.

I know of one other site here in Australia that has the same problem and is also desperately seeking a solution.

Has anyone else had to deal with this and if so, were you able to find any solution to resolve or get around the problem?

Thanks,

Malcolm.

 

[Turkbear]

Hi,
When you changed the DSN's target database, did you also confirm that the CE services' account(s) have access to the new location?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[eyeswideclosed]

is the machine name the new db is hosted on the same as the old machine? i think sql server uses that as part of the db name, change your dsn to point to the new machine if it is different.

 

[malcolmt]

Thanks for the replies.

1. All ce serviced run as a domain account with access to the new database server and database.

2. The machine name of the new database server is different to the current database server. I have changed that to the new server within the dsn.

The problem seems to be that the .rpt retains some reference to the identity of the original database server??

Some other things I have tried are:

APOS Object Manager, but it's capability to set database logon applies to the settings that you see in CMC/Process/Database - not to what is within the report itself which is where the problem seems to be.

.rpt Inspector Enterprise, but it also encounters the "database connector error" when trying to set the datasource. That tool still prompts for stored procedure parameters to verify so it would not save me much time at all time compared to going to each report via CR Designer.

Via CR - Set datasource location - right click edit on database name to erase it & set dsn default properties to true. This works for repointing a report based off tables/views to a different database on the same database server, but again not when the database is on a different server. This method cannot be used for reports based on stored procedures as CR immediately prompts for stored procedure parameters again when you erase the database name - another catch.

Ultimately I am searching for a way of divorcing CR from any specific reference to both database name and database server? i.e. So that CR just goes wherever the dsn takes it to find the tables/views/procedures that it uses.

I am starting to think that only BO are going to be able to resolve. In the mean time, it looks like I will be facing a few hours late night work when the database moves to the new server.

Thanks,

Malcolm.

 

[thebigcheese]

Hi Malcolm,

I have just discovered an issue similar to this as I am upgrading a CE9 environment to BOXI. Note that my SQL server has not moved as in your case - rather I have created a new BOXI deployment and migrated reports.

On the new BOXI server I defined an ODBC DSN in exactly the same manner as on the CE9 server. After migrating 10 reports and checking that the logon info was correct via CMC/process/database I ran into the "The database logon information is incomplete or incorrect".

I did find however that once I had opened one report (on the Enterprise server) from within CRXI and verified the database, then resaved to Enterprise, all other reports worked as well (when I say all I mean the sample set of 10 I migrated initially).

One note, after resaving this report you need to go and put the password in again in the CMC via CMC/process/database.

I will be migrating further reports via import wizard today and I will update this thread if anything changes. I like you was contemplating long nights - perhaps this won't be the case now. I still have 600 reports to migrate though.

TBC

 

[malcolmt]

G'day BigCheese,

I may very well have had the same scenario when I migrated reports (also via import wizard) as I have seen the same message a few times. I would have also reset/verified the datasource.

Generally I do not need to set a database password as the enterprise services run under an account with database access.

Thanks for now.

Malcolm.

 

[SFLLC]

Malcolm,

For batch data source / database migration and conversion of the reports themselves (not just the metadata that is stored in BO/CE) you can use .rpt Inspector Enterprise Suite. One of the 18 tools is a wizard that does just that.

Plus there are other great tools for monitoring and managing other reports properties in batch (fonts, formulas, parameter values, analyze which are in use, etc.) as well as BO/CE items like instances and schedules (rescheduling failed instances without re-entering all the data again, modify schedules without re-creating them, ...), destinations (add/remove e-mails, ...), formats, database logons, etc.

A FREE trial is available on our website:

http://www.softwareforces.com

 

[malcolmt]

Thanks SFLLC,

I have already downloaded .rpt Inspector Enterprise a few days ago.

However I found that it also encounters the "database connector error" when trying to set the datasource. The tool still prompts for stored procedure parameters to verify so I'm not sure that it would not save me much time time compared to going to each report via CR Designer.

Are you able to offer any more tips or advice?

Malcolm.

 

[SFLLC]

Not to take up this forum's space for .rpt Inspector Enterprise Suite support -- I suggest that you contact our support with any errors you encountered (screen shots will help) and they'll work with you to resolve the issue(s).

The "data source / database migration and conversion wizard" tool is designed to do batch change data source / convert driver / set location / verify. And it does make a connection to your destination before it makes a change in order to confirm that the connection was a success. So support should be able to figure out why you get the error.

 

[DGeo]

If the old machine were going away you could change the dns entry for the old server name to alias the new db server. You could also possibly use a hosts file entry on your reporting server if you don't need continuing access to the old db server.