Report failure when setting DB source to different Database

[ksaab2002]

Please, help..

I have never seen this happen before and cannot figure it out. I have a two sets of Crystal reports (9.0) pointed to two databases in SQL Server 2003 - one a US DB and one a UK DB.

The DB structures are identical, the reports are identical except for date formats. Both sets worked fine until a new instance of SQL server was installed. Now..the UK reports return empty..

I can take a US report and set the datasource to the UK data tables and it goes from being a working report to a non-working report?

Any ideas would be immensely appreciated!

Thanks!

 

[synapsevampire]

What is the data source, tables, Views, SPs?

Try opening the Database->Show SQL Query and see what it's passing, and then run that SQL in the Query Analyzer of the UK database to see what happens.

-k

 

[ksaab2002]

The data source(s) are tables. When I ran the SQL statement for UK it returned (0 rows affected) when I ran the same query for US I got (4656 rows affected)..

If you would like me to post the SQL, please let me know..

and thank you

 

[Turkbear]

Hi,
Are you compensating for the difference in Date formats?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[synapsevampire]

There's obviously some difference.

Try posting what's in the Report->Selection Formulas->Record and what's in the WHERE clause of the SQL generated.

-k

 

[ksaab2002]

The differnce in dates is only present in the way dates are formatted to display on the reports. The databases have the same date time formats:
{'2005-02-03 00:00:00'}

So I don't think that the problem is with the dates, I am leaning towards it being permissions based within SQL at this point actually..especially after having tried the same Query in the two DB's with two different results..

 

[ksaab2002]

UK Report:

{COMPANY.GL_CLASS} <> "CRPP" and
{SALEITEM.INTERNATIONAL} like {?International} and
{SALEITEM.SORT2} like {?Break2} and
{SALEITEM.AIRLINE} <> "XD" and
{SALEITEM.SORT1} like {?Break1} and
{@DepartOrInvoice} and
{SALEITEM.TRAVELTYPE} = "A" and
right({SALEITEM.TICKET#},1)<>"V" and
{SALEITEM.PASSENGER_NAME} like {?Passenger} and
{SALEITEM.SORT3} like {?Break3} and
{SALEITEM.DIVISION} like {?DK} and
{SALEITEM.COMPANY_CODE} like {?Company} and
not ({SALEITEM.DOC_TYPE} in ["CRE", "DCM"])

US report:

{COMPANY.GL_CLASS} <> "CRPP" and
{SALEITEM.INTERNATIONAL} like {?International} and
{SALEITEM.SORT2} like {?Break2} and
{SALEITEM.AIRLINE} <> "XD" and
{SALEITEM.SORT1} like {?Break1} and
{@DepartOrInvoice} and
{SALEITEM.TRAVELTYPE} = "A" and
right({SALEITEM.TICKET#},1)<>"V" and
{SALEITEM.PASSENGER_NAME} like {?Passenger} and
{SALEITEM.SORT3} like {?Break3} and
{SALEITEM.DIVISION} like {?DK} and
{SALEITEM.COMPANY_CODE} like {?Company} and
not ({SALEITEM.DOC_TYPE} in ["CRE", "DCM"])

from Report->Selection Formulas->Record

 

[ksaab2002]

Ok, so I found a difference, that very well could be causing the failure.

The DB structures were identical until recently...when the new SQL instance was installed the network admin had a failure and recovered the data (in doing so re-setting some field properties to "allow nulls"..

When I remove these fields from the report it runs perfectly.

Now, one last question: what is the most efficient way of "cloning" the structure of my US Database and populating the cloned tables with data from the UK Database tables?

Thanks in advance,

 

[synapsevampire]

To my knowledge there isn't a SQL Server 2003 as you've posted, so please correct me or yourself.

I believe that a Bulk Insert would be the fastest, and I think you can do all of this from within a DTS package.

This type of question is better suited for the SQL Server forum, they're the experts.

-k