I’m setting up Atlas in MS Sql Server. I’ve successfully run Achilles and the synthea_omop_ach_res.dbo.ACHILLES_analysis table exists and is populated.
I’m getting the following error:
Invalid object name ‘synthea_omop_ach_res.ACHILLES_analysis’. (Note: dbo is missing)
I get the error even if I enter synthea_omop_ach_res.dbo in the table_qualifier field of the source_daimon record. I’ve also set this value to FOO.dbo and the query comes back with FOO.ACHILLES_analysis (in other words, I think I’ve confirmed that the table name is comming from the table_qualifier value in the source_daimon record).
I’ve also tried the following in MS Sql Server to try to set the default schema (and some variations of the following):
use synthea_omop_ach_res;
ALTER USER synthea_omop WITH DEFAULT_SCHEMA=dbo EXECUTE AS USER=‘synthea_omop’;
SELECT SCHEMA_NAME();
This is the connection string I’m using:
jdbc:sqlserver://host.docker.internal:1433;databaseName=synthea_omop;encrypt=false;TrustServerCertificate=True;user=synthea_omop;password=Sneaker01
How can this be fixed?
Query:
2024-06-23 09:56:31 2024-06-23 13:56:31.596 ERROR http-nio-8080-exec-10 org.ohdsi.webapi.util.GenericExceptionMapper - [] - org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar
2024-06-23 09:56:31 [(SELECT
2024-06-23 09:56:31 aa1.analysis_name AS attribute_name,
2024-06-23 09:56:31 ar1.stratum_1 AS attribute_value
2024-06-23 09:56:31 FROM synthea_omop_ach_res.ACHILLES_analysis aa1
2024-06-23 09:56:31 INNER JOIN
2024-06-23 09:56:31 synthea_omop_ach_res.achilles_results ar1
2024-06-23 09:56:31 ON aa1.analysis_id = ar1.analysis_id
2024-06-23 09:56:31 WHERE aa1.analysis_id = 0
2024-06-23 09:56:31 UNION
2024-06-23 09:56:31 SELECT
2024-06-23 09:56:31 aa1.analysis_name AS attribute_name,
2024-06-23 09:56:31 cast(ar1.count_value AS VARCHAR) AS attribute_value
2024-06-23 09:56:31 FROM synthea_omop_ach_res.ACHILLES_analysis aa1
2024-06-23 09:56:31 INNER JOIN
2024-06-23 09:56:31 synthea_omop_ach_res.achilles_results ar1
2024-06-23 09:56:31 ON aa1.analysis_id = ar1.analysis_id
2024-06-23 09:56:31 WHERE aa1.analysis_id = 1
2024-06-23 09:56:31 )
2024-06-23 09:56:31 ORDER BY attribute_name DESC];
Error:
2024-06-23 09:56:31 Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name ‘synthea_omop_ach_res.ACHILLES_analysis’.
2024-06-23 09:56:31 at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:265)
2024-06-23 09:56:31 at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1676)
2024-06-23 09:56:31 at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:615)
2024-06-23 09:56:31 at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:537)
2024-06-23 09:56:31 at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7730)
2024-06-23 09:56:31 at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3786)
2024-06-23 09:56:31 at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:268)
2024-06-23 09:56:31 at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:242)
2024-06-23 09:56:31 at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:456)
2024-06-23 09:56:31 at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:696)
2024-06-23 09:56:31 at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:638)