Migrating from Dev to prod using ODBC DSNs 3

[tfernand]

So I have a bunch of reports created on a dev machine using ODBC DSNs that point to a dev environment. In order to migrate them over to our production Crystal server, I created a ODBC DSN with the exact same name on this server as the dev DSN on the dev machine. The DSN on the prod Crys server points to the production database. Now this works fine for reports using the "Command" functionality. However, for other simple reports it seems like the schema owner name is hardcoded and saved somewhere in the report. The schema owner name is obviously different in our production database and hence when u try to run this report on the prod crystal server machine it complains that it can't find the table.
Is there anyway to tell Crystal not to save the schema owner name when it is saving the file ?

thanks
-- Tony

 

[Turkbear]

Hi, You are correct; the schema name is part of the Sql code that Crystal constructs.

The only way I have found to redirect reports is to individually open them in CR and use the 'set location' option under the database menu to point to the new table.

 

[synapsevampire]

There is a cheat here, go into the Set Location and remove the schema name from each table used.

There is also a utility to change them for versions thru CR 8.5, and works for a batch of reports. Here's the CR 8.5 version, but I fear that you're using CR 9 (you should include technical information when requesting it):

http://support.businessobjects.com/communityCS/FilesAndUpdates/update8x.exe.asp

-k

 

[tfernand]

Hi synapsevampire,
You are correct about the cheat in CR 8.5 . Do you or anybody know of a corresponding cheat/utility in CR 9 ?

thanks
-- T

 

[synapsevampire]

I don't think that they've released one.

Perhaps you can check with the BO folk.

-k

 

[vidru]

Is this an in-house application? If so, you can reset the schema at runtime using the Location property of the DatabaseTables collection (thread768-802224).

If not, if you have access to VB6 and CR9 developer, you can write your own utility to set the schema. If you need some code, let me know.

-dave

 

[tfernand]

Hi Dave,
If you could send me some code that will change around the schema that would be super.

thanks,
-- T

 

[vidru]

I fought with CR9 last night a bit to try to get the solution I use for CR8.5 to work with 9, but to no avail. Using the ConnectionProperties of the DatabaseTable object only returned the table name, without the schema.

I tried opening the report in VB and resaving after setting the Database to my new database, and after opening the report and looking at the Datasource Location, it looked as if it was pointing to the new database. However, I ran the report, and it was still showing the data from the old db. Show SQL Query verified that it was using the schema for the old database, even though everything else in the report file showed it was using the new db. Annoying...

Well, I don't have CR9 available right now, but was just reading a document from Crystal about Connection Properties. This is how they say you can set the location at runtime:


Connecting to SQL Server (ODBC) at Runtime
[tt]
'Declare a Connection Info Object
Dim ConnectionInfo As CRAXDRT.ConnectionProperties
'Set the Connection Info to Connection Properties of
'the table object
Set ConnectionInfo = Report.Database.Tables(1).ConnectionProperties
'Set the ODBC DSN
ConnectionInfo.Item("DSN") = "ODBC DSN"
'Set the database name
ConnectionInfo.Item("Database") = "Database name"
'Set the user name
ConnectionInfo.Item("User ID") = "User name"
'Set the password
ConnectionInfo.Item("Password") = "Password"
'Set the fully qualified table name if different from
'the original data source
Report.Database.Tables(1).Location = "database.owner.tablename"
[/tt]
I'll run some tests later on today to see if this actually works. Good luck.

-dave

 

[vidru]

Using the above code, I was able to set the Database and Table location properly. I also saved the report as a new file from VB, opened it in Crystal, and verified that the new schema was saved.

-dave