old syntax used in "drop index"

[wyriwyg]

Achilles is using an old syntax for "drop index", which does not work well in certain cases. Here is the error message produced by this drop index statement:

DBMS:
sql server
 
Error:
com.microsoft.sqlserver.jdbc.SQLServerException: Cannot drop the index 'results_deid.idx_ar_aid', because it does not exist or you do not have permission.
 
SQL:
drop index results_deid.idx_ar_aid
 
R version:
R version 3.6.0 (2019-04-26)
 
Platform:
x86_64-redhat-linux-gnu

This syntax is not working well and it has as also described by the official DROP INDEX documentation:

Important

The syntax defined in <drop_backward_compatible_index> will be removed in a future version of MicrosoftSQL Server. Avoid using this syntax in new development work, and plan to modify applications that currently use the feature. Use the syntax specified under <drop_relational_or_xml_index> instead. XML indexes cannot be dropped using backward compatible syntax.

Hope this can be fixed soon.

[AnthonyMolinaro]

This issue was originally posted in Achilles: https://github.com/OHDSI/Achilles/issues/609

DROP INDEX IF EXISTS is newer syntax supported by SQL Server, but not other platforms, and is not currently supported by SqlRender.

[schuemie]

We can certainly add this new syntax to OHDSI SQL. Could you help by figuring out the equivalent SQL in the other supported dialects (that do support deleting indices if they exist)? For example, DROP INDEX IF EXISTS does exist (using exactly the same syntax) in Postgres.

[AnthonyMolinaro]

@schuemie Thanks, Martijn. Yes, will do.