Knowledge Base

If you get this error message when trying to open a report either to preview it or go into its layout/design, use the following steps to solve the problem:

WHAT VERSION OF THE PROGRAM ARE YOU RUNNING? From the pull-down menu options across the top of your Camelot screen, choose Help, and then About, and there it will show you your version and build number.

If you are running Camelot 3.x:

1. Confirm you are running the latest update for Camelot 3.x.  Refer to our main website home page to find out information about upgrading (camelotsoftware.com).

2. The criteria for the report may be incorrect.  Double-click on the report again to access its criteria screen, and be sure there is nothing wrong there.  Try it with only minimal criteria that you know is accurate.  You can right-click on the report and choose "Reset SQL for this report", and that may solve your problem.  For more information on setting report criteria, see the kb topics about the Criteria Screen.

3. If you are getting this error when trying to create a mailout from the Calls and Mailouts screen, then it still could be a problem with the particular report you are trying to create.  In order to find the report to check, on the Calls and Mailouts screen, right-click on the mailout you are trying to create and choose View/Edit Correspondence Definition.  This takes you to the Sources screen, and on the upper right, click on the Mailout/Correspondence tab, and there in the Report to use for Mail Merge box, you will find the name of the report being used.  Then you can go to the Reports screen, to the Mail Merge Reports folder there, right-click on the appropriate report, and choose Reset > SQL for selected report.  Then try to create your mailout again and see if it works.

4. You could have a problem with one or more of your Custom Field definitions.  Go to Misc/Setup > Custom Fields, and be sure there are no duplicated field names, or fields with anything other than letters or numbers used in the field name itself.  Once this is confirmed, click OK on the custom field screen, and try your report again.

5. If you've checked all the above steps and you still get the error message, download and run this program ONLY ON YOUR SERVER machine to update the user-defined functions used by the database engine: http://www.camelotsoftware.com/download/Camelot3FirebirdUDFs.exe

6. If you have done ALL of the above steps and you still get the error message for the report, then:

a. Close everyone out of the program, and open the program only on the server machine (for single-user machines, just be sure you only have the one copy of Camelot open on your computer).

b. From the Tools pull-down menu, choose Utilities > Redo Database Structure Updates.  This will ask you to reopen the program, so let the program close and then reopen it.

c. After that finishes, go to Reports, then right-click on any folder there, and choose Reset > ALL Dataset Definitions.

If you still have the problem, then please contact our technical support department.

If you are running an older version of Camelot (2.x and older):

1. For any error message in Camelot 2.x and older, always the first thing to do is close everyone out of the program and on your main computer, run the Compact Main Database (windows Start > Programs > Camelot > Compact Main Database).  After doing that, try your report again.

2. If the compact did not solve this error, then the criteria for the report may be incorrect.  Double-click on the report again to access its criteria screen, and be sure there is nothing wrong there.  Try it with only minimal criteria that you know is accurate.  For more information on setting report criteria, see the kb topics about the Criteria Screen. 

3. You could have a problem with one or more of your Custom Field definitions.  Go to Misc/Setup > Custom Fields, and be sure there are no duplicated field names, or fields with anything other than letters or numbers used in the field name itself.  Once this is confirmed, click OK on the custom field screen, and try your report again.

4. If you have done the above steps and you still get the error message for the report, then reload the reports and datasets that came with the program by doing the following:

a. Close everyone out of the program, and open the program only on the server machine (for single-user machines, just be sure you only have the one copy of Camelot open on your computer).

b. Go to Tools > Upgrade From > Load Custom Update, and browse and choose the C:\Program Files\Camelot\CEUpdate.mdb file to import.

c. After importing the stock reports, go to Misc/Setup > Custom Fields, and simply click OK so that any custom field definitions you have will be reset.

d. Then also go to Misc/Setup > Company/Misc Info, to the Logo/Message tab, and if you've chosen a company logo graphic for your work orders and invoices, then browse and select that file again in the box for that there, and click OK on the company setup screen so it will reset you reports to show that logo.


This will usually solve the "The SQL for this report is invalid" error message.  If it does not, please use the Contact link at the top of this web knowledgebase to send us a message with your particular problem and your contact information.


sql error sql invalid sql sql report