Question / Problem:
Why are SQL queries to the TotalAgility_Documents database failing after upgrading to KTA 7.8?
Answer / Solution:
The structure of the Documents database is not documented, and queries against the Documents database have never been supported. The only supported interaction with the content of the Documents database is through the API.
In instances where unsupported queries have been used, you will find that they will no longer work in KTA 7.8. This is because the structure of the database has changed to improve runtime performance. One of the strategies in the change is to use fewer tables and fewer columns, with more data stored in binary blobs. This results in fewer joins and less blocking.
A good example of how this would affect unsupported queries is with document field data. Under the previous structure there was a Field table and it was easy to see how it could be queried by document ID, field name, value, etc. Now the field data for a given document is a single binary blob on the document record. There is no way to use SQL to parse this, as the binary data structure must be parsed with code, thus the need to use the API (Updating a Field with the API). While a custom SQL query on field data has always been unsupported, it is now impossible in a technical sense.
If your solution has used unsupported queries in previous versions of KTA and that functionality is still needed, these must be redesigned before upgrading to KTA 7.8. There is no shortcut to doing this: you will need to review the available API functions and determine how or whether you will implement equivalent functionality.
Separately, there are a few specific tables in other KTA databases that are supported for reporting purposes. Because they are supported, backwards compatibility between versions is expected, and any breaking changes would be documented. For details on those, see the following article: