Switching ODBC Data Sources in Crystal Reports -- Still Pulling From.. 1

[efoiadmin]

Hi all!

I saw this forum had regular responses, and I've already read through some of them and tried to fix my problem. I am IT Manager for a company and we are trying to run some of our normal reports against older versions of our database.

The basis info on my environment:

-- Database Server: SQL Server 2005
-- Crystal Reports: XI
-- Everything is on our LAN

Monthly we close the books, etc... I make a "pre-close" full backup each time. Now I've been asked to restore each of the backups to its own NEW database on the server. I have accomplished this task.

Now, we're trying to take our existing Crystal Reports and run them against each of these newly restored databases. So, naturally, I create some new ODBC connections for each database. I have named each database on the SQL server as follows: JANUARY2009, FEBRUARY2009, MAR....

So, I establish new ODBC connections successfully, all of them test out. Then I pop open Crystal XI and right click on the Database Fields, select Set Datasource Location, Make New Connection, then I choose the appropriate ODBC, let's say JANUARY2009's ODBC connection, then I click <UPDATE>.

I re-run the report (F5) > but I'm getting data that I KNOW is not in the newly restored database. How do I know this? I open up SQL Server Management Studio, perform a basic query against JANUARY2009:

select * from ARFIM where INVOICE_DATE > '2009-01-31 00:00:00.000'

Boom! No records. Yet records are showing in Crystal that are beyond that date (all the way up to the present).

What you think could help me on this one? The report is somewhat complex, so I'm not sure I could be able to get a brandf new one created in a short amount of time.

 

[efoiadmin]

FYI... I just looked back at the report and noticed something...when I am in the Set Datasource Location (AFTER changing it), I expand each table in the report (there are only two tables), then properties of each table...

I see the info like this:
Jan2009
--Properties
----Database Type: ODBC (RDO)
----Data Source Name: Jan2009
----User ID: myuserID
----Database: JANUARY2009
----Use DSN Default Properties: False

--ARFIM (first table)
----Properties
------Table Name: ARFIM
------Table Type: Tables
------Catalog: ESIDB (this is the name of the old database)
------Owner: dbo
------Overridden Qualified Table Name:

--SOFCM (second table)
----Properties
------Table Name: SOFCM
------Table Type: Tables
------Catalog: ESIDB (this is the name of the old database)
------Owner: dbo
------Overridden Qualified Table Name:

In both places where it reads ESIDB for the Catalog, that doesn't feel right, since that's the name of the OLD database.

 

[dgillz]

you must perform a set database location for each table in your report. If you have subreports, you must set them as well.

Software Sales, Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[efoiadmin]

Hi Dgillz,

I tried to set for each table, this did not fix my problem. Still not sure why this is happening. Oy! The CFO is all over us to get these done.

Any thoughts would help.

 

[IanWaterman]

Have a look at the SQL

database - > show sql

What does that show?

Try setting Overridden Qualified Table Name: to each table name. Double click and type in table name.

Close connection and try logging back into the correct ODBC.

Ian

 

[efoiadmin]

Ian. I will try those and reply again. In the car.

 

[efoiadmin]

Ian, I think you're on to something... check out the joins:
esidb is the production database, not my newly restored one. how do i change this?

SELECT SOFCM.CUSTOMER_NAME, SOFCM.CUSTOMER_ID, ARFIM.INVOICE_NUMBER, ARFIM.TERMS_CODE, ARFIM.INVOICE_DATE, ARFIM.CURRENT_BALANCE, ARFIM.INVOICE_AMT, ARFIM.SALESMAN_ID, ARFIM.ORDER_CLASS
FROM ESIDB.dbo.ARFIM ARFIM INNER JOIN ESIDB.dbo.SOFCM SOFCM ON ARFIM.CUSTOMER_ID=SOFCM.CUSTOMER_ID
WHERE ARFIM.CURRENT_BALANCE<>0 AND (ARFIM.ORDER_CLASS LIKE 'd%' OR ARFIM.ORDER_CLASS LIKE 'h%' OR ARFIM.ORDER_CLASS LIKE 'i%' OR ARFIM.ORDER_CLASS LIKE 'k%' OR ARFIM.ORDER_CLASS LIKE 'l%' OR ARFIM.ORDER_CLASS LIKE 't%')
ORDER BY SOFCM.CUSTOMER_ID, SOFCM.CUSTOMER_NAME

 

[IanWaterman]

Normally setting Overridden Qualified Table Name eliminates the DB name, well it does in Oracle. But I have seen this problem with ODBC to SQL server before and it was a pain to sort out!!

Did you override name?

Close Crystal completely so that ALL connections to DB are hard closed.

Ian

 

[efoiadmin]

Tried override table name, but it won't take. Rebooting that machine right now.

 

[efoiadmin]

I may have got it...after reboot i tried the override again, and got:


SELECT SOFCM.CUSTOMER_NAME, SOFCM.CUSTOMER_ID, ARFIM.INVOICE_NUMBER, ARFIM.TERMS_CODE, ARFIM.INVOICE_DATE, ARFIM.CURRENT_BALANCE, ARFIM.INVOICE_AMT, ARFIM.SALESMAN_ID, ARFIM.ORDER_CLASS
FROM JANUARY2009.dbo.ARFIM ARFIM INNER JOIN JANUARY2009.dbo.SOFCM SOFCM ON ARFIM.CUSTOMER_ID=SOFCM.CUSTOMER_ID
WHERE ARFIM.CURRENT_BALANCE<>0 AND (ARFIM.ORDER_CLASS LIKE 'd%' OR ARFIM.ORDER_CLASS LIKE 'h%' OR ARFIM.ORDER_CLASS LIKE 'i%' OR ARFIM.ORDER_CLASS LIKE 'k%' OR ARFIM.ORDER_CLASS LIKE 'l%' OR ARFIM.ORDER_CLASS LIKE 't%')
ORDER BY SOFCM.CUSTOMER_ID, SOFCM.CUSTOMER_NAME

!!!!!!!!!!<<<<<<<<<<<<<SWEET>>>>>>>>>>>>!!!!!!!!!!!!!!!

 

[IanWaterman]

Glad to hear the good old

"Have you tried switching it off and on again"

Bit of a pain if you have to do that everytime you want to switch DBs. I think it called a work around!!

Ian