Crystal Reports 9 Database Connection Issue

Question

Crystal Reports 9 seems to save the database connection information inside the report file itself. I am having an issue changing that connection. I work with a team of developers who all have their own copy of a database on the same server. We are using Trusted Connections to the db. When we need to make changes to a crystal report, and we click the lightning bolt to execute the report, Crystal does not ask for login information to the database. It actually ends up connecting to the last database that was used when the report was saved last.

We came up with 2 workarounds:

1. Take the database that crystal thinks it should connect to offline, then crystal will ask for login info.
2. Remove permissions for the username that is making the crystal change.

Neither of these are acceptable for us. Does anyone know how to remove the crystal connection from the report file?

We have tried Log Off Datasource Location and all of the settings in the Database Expert.

UPDATE

I still have not found a solution that fits my case. But our newest workaround is to load up a crystal report and just before you click the lightning bolt (to run report against the database), unplug your ethernet cable. Then when Crystal cannot find the database, plug the ethernet cable back in and it will allow you to choose a different database server and name.

Solution 1

You could use a .dsn datasource file in a user-specific location (i.e. the same path for every user, but a different physical location) and point Crystal Reports at that. For example, on everyone's C drive: C:\DSNs\db.dsn, or on a network drive that is mapped to a different location for each user.

You can get more info on .dsn files on MSDN: http://msdn.microsoft.com/en-us/library/ms710900(VS.85).aspx

Solution 2

We are using such way (using sql authentication however):

• open report
• database - log on server
• database - set datasource location
• refresh/preview

You may disable your [domain user] access to dev database, should help too :)

Solution 3

I am probably answering too late to have any chance at the bounty, but I'll offer an answer anyway.

If you are running the Crystal Report directly or with Crystal Enterprise then the only way I can think of to do this is by using a dsn as paulmorriss mentions. The drawback to this is that you'd be using ODBC which I believe is generally slower and thought of as outdated.

If you are using this in an application then you can simply change the database connection settings in code. Then, everyone can develop the report against their own test database and you can point it to the production database at runtime (assuming the developers database is up to date and contain the same fields as the production database).

To do this you should be able to use a function like the following:

private void SetDBLogonForReport(CrystalDecisions.Shared.ConnectionInfo connectionInfo, CrystalDecisions.CrystalReports.Engine.ReportDocument reportDocument)
{
    CrystalDecisions.CrystalReports.Engine.Tables tables = reportDocument.Database.Tables;

    foreach (CrystalDecisions.CrystalReports.Engine.Table table in tables)
    {
        CrystalDecisions.Shared.TableLogOnInfo tableLogonInfo = table.LogOnInfo;

        tableLogonInfo.ConnectionInfo = connectionInfo;
        table.ApplyLogOnInfo(tableLogonInfo);
    }
}

For this to work you need to pass in a ConnectionInfo object (which will contain all of your login information) and the report document to apply it to. Hope this helps.

EDIT - Another option, that I can't believe I haven't thought of until now, is that if you are using SQL Server you can make sure that all of the development databases names are the same, then use "." or "(local)" for the server and integrated security so that everyone effectively has the same connection info locally. I think this is probably the best way to go assuming that you can get all of the developers to use the same setup.

EDIT Again :) After reading some of the comments on the other answers, I think I may have misunderstood the question. There is no reason that I can think of why you wouldn't be able to do the steps in Arvo's answer outside of not having rights to edit the report, but I'm assuming that you've been able to make other changes so I doubt that is it. I assumed that to get the report to work for each developer you had been doing these steps all along.

Comments

[Jon]

We already do change the connection at runtime. That is not the issue. Unfortunately the issue is the designer.

[Jon]

I have tried Logging off the server and changing the datasource location. Neither have worked.

[Chris Chilvers]

Set data source location works fine with integrated authentication as well.

[Arushi Rajput]

I think that they are currently doing this since it looks like all of the developers have there own copy. I think the issue is that they don't want to have to do this every time a different developer works on the report against their own local database.

[yoka]

Sometimes (often just on same server) you need to set all table locations individually. If you look at table properties in 'set location' dialog, you may notice that database and schema names are remembered.