Summary:

On upgrading a copy of Sage CRM, an error may be logged on running tables.es. The error refers to an issue opening a query with no SQL. This issue is likely caused by the presence of an index on a table, blocking the changing of a column's data type.

Symptoms:

A specific example was logged by a customer upgrading from Sage CR M7.1l to 7.2. The following appeared as a result of running tables.es:

After line 2970: Cannot open a query with no SQL

Cause:

The issue was caused by the presence of an existing index conflicting with an ALTER TABLE DROP COLUMN statement. This issue could equally likely be caused by an ALTER TABLE ALTER COLUMN statement The following was present in the tables.es:

DeleteColumn('Channel_Link','ChLi_ChannelLinkId');

However, this statement was not on line 2970.

There isn't a very straightforward way of approaching this problem, unless you assess the tables and columns mentioned in the ES script after the line referenced in the error. It is possible to script out all existing indexes in advance of dropping them; the following SQL file will return the content of all indexes i nadvance of making any changes:

 Download: Script out all indexes

Alternatively, it may be possible to identify the SQL that is causing the issue by running a SQL Server Profiler trace.