Urgent Issue with CR XI data connectivity using ODBC

[khan007]

Hello

I am using CRXI Designer. SQL Server 2000. Windows 2000
I have a question related to DataSource Location settings in CRXI.

We have One database server with two different databases as Testdb and Proddb. One ODBC System DSN as ReportsDSN pointing to each database on respective application servers.
Reports were initially created in CR 8.5 against System DSN and saved each report in (created two identical copies) respective repository of Prod and Test application Server.
Everything worked perfectly fine in each environment for years.

Since I have converted reports in CR XI I am facing a problem with DSN.

Here is the detail:
Initially I set my DSN "ReportsDSN " to Testdb and created a New connection by using ODBC (RDO) and select Testdb.

Now I changed my DSN settings and pointed to Proddb.

What I would expect from my prior 8.5 experience is, Report designer should ask me to verify database and once run will display data from production database.

My understanding is Reports are DSN dependent rather than any specific database.

But this is not the case in CR XI.
Even i change the DSN settings and pointing to Proddb, Inside the DataSource Location settings the catalog name and database name always remains as Testdb and I won't get any data. just a blank report.

Also noticed, when I opened the Show SQL Query it says:

{Call "testdb.dbo.reportid"........
rather than mentioning like CR 8.5 as {call "dbo.reportid"

The only way it works in PRoddb is if I will create a complete new connection and get rid of testdb connection.


Please advice

 

[dgillz]

Try database, set location, and see if this has any effect.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[IanWaterman]

Go to database, set location.

Click the + by each table and then for properties. Double click the Overriden qualified table name and then type in name as it appears in Table name.

Repeat for each table

This will remove the testdb. from each table when viewed in SQL. Report will now be portable across all databases.

Ian

 

[khan007]

Thanks Ian...

There is a problem in CRXI...It's not like 8.5 where you just go and modifiy the name as needed.

Here in Set DataSource Location Option there are two panes.
The upper one is for current data source and lower one is for Replace with.

Let Suppose my report id built on a DSN: ABC and Stored procedure is RPT_111000.

Inside that upper pane you have ABC DSN connection properties including:

Database type: ODBC
UserID:xxx
Database:Testdb
Data Source Name:ABC
User DSN Default Properties: True

Then, inside that upper pane you have Stored Procedure (object) properties including:

Table name: RPT_111000;1
Table Type: Stored Procedure
catalog:Testdb
Owner:dbo
Overridden Qualified Table Name

Now please tell me how come i change something here because whatever changes you made on your Windows 2000 DSN, it never reflects here, the database , catalog, owner always remain same as built originally.

It works perfectly fine with 8.5, when you change the Windows 2000 DSN, reports works fine, but not here.

Please advice...

Thanks in advance

 

[IanWaterman]

I do not have CR11, but I am sure the process is the same as in CR10. However, I have not done this with Stored Procedures, only tables and views.

Have you tried double clicking
Overridden Qualified Table Name
And typing in the Stored Proc name?

I did some work for a client recently where they were trying to do what you are doing, and they got me to convert all reports from Stored Procs to Views. I can not recall why, it may have been for this exact reason.

Do you really need an SP can a view be used instead?

Perhaps some one else can offer advice.

Ian

 

[khan007]

The table name is correct, exactally the same.

No i cannot use views. We have a web based application.
I pass all my parameters from JSP UI in a url to CRXI reports (residing on our Lotus Notes application server) including the Dates parameters, then report call SP with all passing parameters.
Each report specific SP calls couple of more Stored Procedure to identify/determine that passing parameter is actually a single or multiple value and then return data-set to report.So It's a complex structure.

I have done my 95% of work on SQL SP's side. Report basically have a complete data-set ready to display when user calls.

Thanks