Report using wrong database name in fully qualified table names

[Former Member]

Background

Consider three SQL databases: Test, Production, and ProductionCopy. All three databases have identical schemas. All are running on the same server.

I have a report with several subreports. This report makes use of both Crystal Reports database commands and directly reading the database tables.

When developing the report inside the Crystal Reports IDE, I have set all datasource locations to point to Test. At runtime in my .NET application, all report datasources in the CrystalDecisions.CrystalReports.Engine.ReportDocument object are recursively set to Production.

Problem

When I run this report against Production, all data from tables is being read from Test. All data from Crystal Reports database commands is being read correctly from Production.

When I run the report against ProductionCopy, the problem goes away.

The only change being made to switch between the two is to the connection string in the application's Web.Config file.

When I run the application from Visual Studio, the problem goes away. To recreate, I have published the application to run locally on IIS.

Running traces in SQL Server Profiler, all report queries are being executed against the proper database. However, when running against Production, the queries reading directly from tables are using Test in the fully qualified table name.

i.e. SELECT "SomeTable"."Foo", "SomeTable"."Bar", "SomeTable"."Baz" FROM "Test"."SomeSchema"."SomeTable"

However, when running the report against ProductionCopy, the fully qualified table names are correct.

i.e. SELECT "SomeTable"."Foo", "SomeTable"."Bar", "SomeTable"."Baz" FROM "ProductionCopy"."SomeSchema"."SomeTable"

What I Have Tried

Restoring ProductionCopy directly from the live version of Production, to ensure that both contain identical data.

Attaching a debugger to the IIS process to check the values of the data connections in the ReportDocument object at runtime. At the point ExportToStream is called on the report, all database and datasource connections for the report and all subreports point to Production.

Attempting to serialize the ReportDocument object so that it can be searched for all instances of the string "test", though I was unsuccessful in my attempts.

Updating to the latest versions of Crystal Reports 2013 (14.1.6.1702) and Crystal Reports for Visual Studio (13.0.15.1840).

Running another report with a similar structure, but the problem does not occur.

Next Steps

It turns out I can't attach a .rpt file to this post, and I definitely can't attach a MCVE.

[Former Member · Accepted solution]

After recent changes to the report, the problem is no longer present. I still have no idea what the root cause was, nor what change made resolved this issue.

I have before and after versions of the report (though no easy way to diff them), which I can submit if desired.

[former_member11696 · Accepted solution]

Hi Aaron,

MS doing odd stuff with their DB clients. The MDAC driver SQLOLEDB.dll is not completely supported with newer DB Servers but CR seems to have issue when updating the client to the newer version. You are not the first to run into this types of problems. Some reports work and some don't.

Updating the reports can "fix" the DB info in the rpt file though.

Also, you may want to update all of your reports to ODBC, MS is dropping OLE DB support to their servers.

Don

[former_member11696]

Hi Aaron,

Great info and details, one thing missing, how are you connecting to the DB? OLE DB or ODBC?

And what client are you using?

Don

[Former Member]

The connection is SQLOLEDB (crdb_ado.dll).

Client is SQL Server Native Client 11.0 (2011.110.5058.00).