Please HELP: Error with editing SQL query in report

[SmileyFace]

Need some help with this people! I edited my query in the 'Show SQL Query' window, added one more criteria to the where clause. Was getting a 'Database DLL Error'. When I checked on the Crystal support page it said that if a change is made in the show query window, Crystal cannot handle that very well and most of the time won't accept it! It had some steps to correct it. Finally after playing around with it, I got it to work, got rid of the error but then it was only running this new report if I ran it from the report designer, but for some reason the browser, which runs the application which opens the report from an asp page, was still running the old report as if it had it cached somewhere on the server. This was very weird. So I renamed the report and then started getting the error saying 'Failed to open a rowset. Incorrect syntaxt near the keyword AND.'

The report was designed in CR 8.5 (which I have on my installed on my computer) while the version that runs on the server is CR 9. I am guessing this is the problem and CR 9 won't accept the change in the SQL query but I am unsure how to work around this. ANy help with this is highly appreciated. Thanks in advance!

 

[synapsevampire]

Editing the SQL in CR 8.5 isn't a good idea, and when you are considering architecture like this, why not post what you're INTENDING to do within this SQL rather than assume that your methodology is best practice.

Open the report in CR 8.5.

Select Database->Show SQL Query

Copy the SQL out of there.

Open the report in CR 9

Select Database->Set Datasource Location

ighlight the existing table above and select add command below and select Add Command

Now paste in the SQL

This is now the reports data source.

-k

 

[SmileyFace]

Always to my rescue! Thanks a lot!

Ok, but I am not sure I follow what you mean. Here is the SQL code in Query Window. What is highlighted is what I added and what messed it up.

SELECT
CUSTOMER.CI_FNAME, CUSTOMER.CI_LNAME,
HOUSEHOLD.HI_ADDRESS1, HOUSEHOLD.HI_ADDRESS2, HOUSEHOLD.HI_CITY, HOUSEHOLD.HI_STATE, HOUSEHOLD.HI_ZIP
FROM
IneosBathAndSpa.dbo.CALL_INFO CALL_INFO, IneosBathAndSpa.dbo.CUSTOMER CUSTOMER,
IneosBathAndSpa.dbo.HOUSEHOLD HOUSEHOLD
WHERE
CALL_INFO.CLI_CUST_ID = CUSTOMER.CI_CUST_ID AND CUSTOMER.CI_HOUSEHOLD_ID = HOUSEHOLD.HI_HOUSEHOLD_ID AND CALL_INFO.CLI_PROC_STATUS = 2 AND (CALL_INFO.CLI_EU_CATEGORY = 'A' OR CALL_INFO.CLI_EU_CATEGORY = 'S') AND (CALL_INFO.CLI_CALL_OUTCOME = 'REFR' OR CALL_INFO.CLI_CALL_OUTCOME = 'REF2')
ORDER BY
HOUSEHOLD.HI_ZIP ASC

As you can see all I did was added an OR in the last part. After doing that it gave me an error saying 'Error with Database DLL'. Anyways I got that resolved such that now if I run it from the designer it works fine but when I use the browser it gives me the error 'Failed to open a rowset. Incorrect syntaxt near the keyword AND.'

I have CR 8.5 in which I made the change, why do I need to go to the server and change something there? Until now it was running fine right? Why am I able to run it on my computer but not through the browser which is opening it from the server?