Is it possible to configure a Crystal Report to use a DSN without storing the databse name?

Question

DSNs are very helpful things. I can configure a DSN to point to a development database, and when I'm finished developing, just reconfigure it to point to a production database and I'm done. No need to make any changes to my program or its configuration.

But if I create a Crystal Reports XI report using an ODBC connection and specify a particular DSN, CR looks into the DSN to find the name of the current database, and uses that name in its queries. If I change the database the DSN points to, the report will use the old database. For example, I have a DSN named "WinCaps" that pointed to a database named "Anneal". I created a report and supplied a connection string like "DSN=WinCaps;user=caps;passwd=xyz" (syntax may not be perfect). I was then prompted for a host name, user name and password, all of which were provided in the DSN and should not have been necessary. I saved the report and closed it, and then changed the DSN to point to a database named "Worthington HS Control". I reopened the report and selected "Show SQL Query". I saw that the query was selecting data from the Anneal database. Is there any way to change this behavior? Does the latest version of CR behave the same way?

Answer 1

When you are setting an ODBC connection to a server database, Crystal will ask for a database name. As far as I know, you can change the database name either in code or using Crystal designer. There is no way to make a change in the DSN and force Crystal to use it. You can use a tool like http://r-tag.com/Pages/CRDataSource.aspx, which is able to process a whole folder of reports and completely overwrite the connections. However, this is useful in extreme scenarios, for example when you need to replace ODBC with OLEDB connection. Your scenario is pretty simple, you do not need to overwrite connection(s), just to update the database name. This could be done with the same code , which is setting the password. Do you have control over the application , which is running the report ?

Answer 2

Different ODBC connections will ask for different settings. If you create one ODBC connection for MS Access and one for MS SQL server you will notice that when you select the MS Access DSN Crystal designer will ask you just for username and password, if you select the DSN for SQL server Crystal will ask for user name , password ( you can instead use trusted connection), and database name. So, when you change the connection to SQL Server using Crystal reports it will ask you for the database name and will update it in the query. My guess is that in your scenario, the DSN for Postgres contains information about the database. DSN for SQL Server doesn't contain information about the database. It has settings for the default database, which is optional. When you try to use this DSN in Crystal designer it will ask you for database name. If you are changing the connection in code you need to handle database by yourself.