BadSqlGrammarException in WebAPI when using a sql server database

Diego_Bosca_Tomas · October 8, 2021, 9:12am

I’m launching Atlas and while I can explore the vocabulary, I’m getting errors when clinical tables are involved. For example when searching the vocabularies I get the following

The problem seems to be that conceptRecordCount throws an exception of ‘BadSqlGrammarException’
imagen

The strangest thing is that both vocabulary and CDM are in the same schema. So no clue on why it’s failing
When configuring the source in webapi I defined the dialect as “sql server”, as I’ve seen it referenced in some places but maybe it has another name

Diego_Bosca_Tomas · October 18, 2021, 7:46am

I’m suspecting this is due to the version of the database being sql server 2019 and not sql server 2020, as it seems to be lacking some functions (specifically “GREATEST” and “LEAST”). Any way of solving this without upgrading the sql server version?

Diego_Bosca_Tomas · October 18, 2021, 9:51am

After reviewing the sql that gave errors we realized it was mostly due to the missing achilles_results table, which was not being created by the missing function.

Once we removed the specific analysis that was giving problems from the achilles launch we were able to get the achilles_results table and the Atlas did work fine.

As a suggestion, the badSqlGrammarException should probably return the stacktrace to ease debugging