DSN Problem!!!Please Help 1

[CrystalVis]

I am using CR8.5 reporting off SQL Server 2000. I develop all my reports at a workstation then move the reports to the TEST Server for testing purpose. Once the reports pass testing, I move them to the PRODUCTION Server. I am experiencing a problem with the DSN not looking at the right database. At the workstation, I setup a DSN name REPORT pointed to the TEST_Db. On the TEST Server, I setup the same DSN REPORT pointed to the TEST_Db). The reports are pulling the correct data from the TEST_Db when run on the TEST Server. On the PRODUCTION Server, I setup the same DSN name REPORT and pointed to PRODUCTION_Db. However, when I run the reports on the PRODUCTION Server, the reports are still pulling the data from the TEST_Db. The reports should be pulling the data from the PRODUCTION_Db because the DSN on the PRODUCTION Server is pointed to the PRODUCTION_Db.

I also develop report against Oracle database. This setup is working fine in the Oracle environment. I don’t know what is the different in SQL database. Can someone please help me understand why Crystal behaves this way in SQL environment. Is there a workaround for this problem? Open up each report and set the location to the DSN that is pointed to the PRODUCTION_Db is an option for our location. But this will not work for our clients. Your help/suggestion is greatly appreciated.
TIA

 

[TedLiu]

Hi,

Which way do you use to call report object? I assume you use logonserver method to pass logon information, if so, you can't change database in runtime, you can use setlogoninfo, which allow you change back-end data source in runtime.

Also, before you run, did you try verify database in report designer?

Hope it help!

Ted

 

[CrystalVis]

Ted,
Thank you for the respond. I am using the WCS calling the report via URL command line. The user name and password are passing through the command line.

 

[TedLiu]

Seems you are not using Crystal Report but Crystal Enterprise. I didn't try change data source in url command yet because of the limitation of url charactor length. But have you tried to use CMC to preview report against new database?

Ted

 

[mdw1958]

Go to set location and remove the [database].[user]. from the table or stored procedure name. If the explicit name is left there it will reference the table identified by name rather than DSN.
At least that is what happens using the CRPE. We redirect the reports to the database our application is logged into, but we must remove the database and user segments of the name.

 

[TedLiu]

mdw is right. When you use DSN, report expert interface will keep schema name in front of table name. Make sure remove it before you switch to another data source.

Ted

 

[CrystalVis]

Thank you all for your suggestion. I did get the answer from the SQL forum. Someone suggested the same thing that MDW1958 suggested. My problem is solved. Again, thanks for all you help.