Changes to SQL Query Not Saved

[fluteplr]

I have a Crystal 8.5 report accessing SQL 2000 using OLE/DB. The query would really be helped by adding the following optimization hint.

option(force order)

I can add that hint in the view sql window of the database menu and it works great. However when I save the report and reload it the hint is gone.

Since this report is normally tun through an interface in my program that does not allow the users to modify the report this is a bit of a problem. Any ideas?

 

[rogar]

I don't think Crystal supports that modification of its SQL. You should try putting the SQL into a stored procedure, where you can do anything your database allows, and redo your report based on the SP.

 

[fluteplr]

I am trying to avoid that. It would require a massive change to a bunch of reports and a change of the report interface.

Also your answer is invalid on the face of it. Crytal does allow a change to the sql, I made one.

It even runs the changed sql.

(Verified by looking at the query the server recieves.)

The problem is that it does not SAVE the changed SQL.

 

[rogar]

You can edit the SQL in Crystal, as you say, and run it. But Crystal will not SAVE your edited statement, because Crystal does not support the functionality of the edited statement. I have tried that, too, with the same results you have experienced. You could try creating a query in the Crystal SQL Designer. I don't know if that tool supports hints or not.

 

[Chopstik]

I ran into a similar problem a while back and if I remember correctly could only correct it using a stored procedure. however, think after consultations with Seagate that they also suggested using the SQL Query Designer and that it would work. Don't remember if I tried that since I was able to use the SP, but it may be worth trying.

 

[fluteplr]

thanks