Data Connectivity: URGENT: Why does my report ignore the DSN?

URGENT: Why does my report ignore the DSN?

Printed From: Crystal Reports Book
Category: Crystal Reports .NET 2003
Forum Name: Data Connectivity
Forum Discription: How to connect to data sources and export reports
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=7052
Printed Date: 04 May 2024 at 1:44am

Topic: URGENT: Why does my report ignore the DSN?

Posted By: RobR
Subject: URGENT: Why does my report ignore the DSN?
Date Posted: 15 Jul 2009 at 1:48pm

Greetings!

It is becoming critical for me to understand how CR XI chooses what database to look for.

As I understand it, the purpose of a DSN is to associate a common name with two (or more) databases on different computers when those databases have different names. If I have a DSN named MyDSN on Computer1 associated with a database named MyDatabaseOnComputer1 and I have a DSN named MyDSN on Computer2 associated with a database named SomeCompletelyDifferentName, then the same application that runs on Computer1 using MyDSN will happily run on Computer2 using MyDSN.

I have a database on my development machine named "AK 94 6_24". I have a DSN named WinCaps pointing to it. On my testing machine, the DSN is named "CAPS1" and the database is named "AKAnneal". The application is smart enough to know that it has to walk through all of the tables in my report and change them to make sure they're looking at the correct database. I added code to the application to print the location and the connectbufferstring properties for every table in the report into a file. The file shows me that the table connections are being changed correctly to use the CAPS1 DSN.

But when the application tries to display the report, it claims that data cannot be read from the database.

I have CR XI installed on the test machine. When I use it to open the report and preview it, I get the same error, with a bit more information. It is telling me that it cannot retrievbe data from the AK 94 6_24 database. Here's the complete query from Show Database Query:

SELECT "CHARGE"."charge", "CHARGE"."base", "CHARGE"."status", "CHARGE"."build_mode", "CHARGE"."built_by", "CHARGE"."built_date", "CHARGE"."furnace", "SEPERATOR_PLATE"."thickness", "CHARGE"."pred_heat_time", "CHARGE"."rev_heat_time", "ANNEAL_PROGRAM"."final_hotspot", "ANNEAL_PROGRAM"."final_coldspot", "CHARGE"."cooling_cover", "CHARGE"."furnace_type", "furnace_type"."description", "CHARGE"."inner_cover", "CHARGE"."flopper_stopper", "CHARGE"."cycle"

FROM {oj (("AK 94 6_24"."public"."charge" "CHARGE" LEFT OUTER JOIN "AK 94 6_24"."public"."seperator_plate" "SEPERATOR_PLATE" ON "CHARGE"."seperator_plate"="SEPERATOR_PLATE"."plate_type") INNER JOIN "AK 94 6_24"."public"."anneal_program" "ANNEAL_PROGRAM" ON "CHARGE"."charge"="ANNEAL_PROGRAM"."charge") LEFT OUTER JOIN "AK 94 6_24"."public"."furnace_type" "furnace_type" ON "CHARGE"."furnace_type"="furnace_type"."furnace_type"}

WHERE "CHARGE"."charge"=14860

Despite the fact that CR knows durn well that it's supposed to use the CAPS1 DSN, which is pointing to the AKAnneal database, it's trying to look for the AK 94 6_24 database! I have no idea why the name of that database got embedded into the report, why CR doesn't know to use the database pointed to by the DSN, or how to get it to work correctly!

I've asked this question before and gotten no answers. The issue is becoming critical. I need to be able to develop reports with the confidence that they will operate when they are installed on our customer's system, and I am not there yet.

Thank you very much.

RobR

-------------
Richardson's First Law of Computational Unpredictability: The fact that a given occurrence is impossible does not imply that it will not occur.

Replies:

Posted By: jessiemacmillan
Date Posted: 18 Feb 2011 at 4:06am

I came across this while looking for something else. I'm so relieved to see this post! This has happened to me more than once. The only way I've been able to solve it is to re-code the report from scratch on the database it should be pulling the data from. I'm working mostly in CR 2008, so whatever causes this hasn't been resolved.

And, like Rob, no matter what I do, I can't get the report to pull from the correct database. The Database Expert shows the correct database but the SQL query is incorrect.

If anyone comes across a workaround, please post it!

Posted By: jessiemacmillan
Date Posted: 18 Feb 2011 at 4:24am

I came across the fix for this (still while looking for something else).

Go into Set Datasource Location. Click on the table/view to change. Navigate to the table/view in datasource you want to use. Click on the the table/view. Then click Update. You have to do this for each table/view. Annoying, but less work than re-coding the entire report.

Posted By: comatt1
Date Posted: 25 May 2011 at 3:53am

We used to have to update the tables first, but now we can just update the main database location and the tables get updated as well; I still check after I update though in the tables properties that they connect to the right information.

Posted By: CircleD
Date Posted: 26 May 2011 at 11:56am

Another thing you have to watch out for is when you update the data source I've had several reports change the DNS under properties to False and have to change it back to True.If when you run the report after you update the data source you get a DNS error that's usually what has happened.