Runtime errors in advanced queries

Runtime errors in advanced queries

  
When a runtime error happens in an eSpace advanced query, a database exception is raised.

In order to ease the process of identifying and correct the problem, the following info is included in the exception message:
- Name of advanced query and its place (screen, action, etc.)
- SQL written by developer
- System run-time error

Several problems may be the cause of an advanced query run-time error, such as incorrect sql, constraints violations, casting problems, overflows, timeouts, etc.

You should check the system run-time error in SQL Server Books Online
http://msdn.microsoft.com/library/en-us/startsql/portal_7ap1.asp
And appropriately correct the advanced query in your eSpace.

If you have direct access to the database server you can use a query tool like SQL Query analyzer and copy/paste the SQL to test it.
I have two main problems regarding this issue, that make advanced query errors a little difficult to debug:

1 - The SQL in the error log is not always complete due to size problems

2 - The transformation between the adavnced query sintax and SQL sintax is not simple and usualy generates more errors than it helps.
The transformation between the advanced query syntax and SQL syntax can be done using Ctrl-Q in advanced query SQL statement. If the espace has been published before, this will save a corrected T-SQL SQL statement in the clipboard than can be pasted using Ctrl-V in SQL Query Analyzer. This way it is easier to debug your query.
OutSystems Service Studio examines the SQL typed by the user and gives comprehensive warnings when it detects problems.

These types of problems might be related with unrecognized SQL statements, invalid SQL syntax, mismatch number of columns, invalid Entity names, etc.

However, there are still some invalid SQL constructs that are not yet detected. In such cases, no warning will be presented in Service Studio and a runtime error will be generated.

You should check the system run-time error in SQL Server Books Online
http://msdn.microsoft.com/library/en-us/startsql/portal_7ap1.asp
And appropriately correct the advanced query in your eSpace.