Achilles Error in 'Create the concept hierarchy'

Implementers
1

Trying to implement the Achilles R package, but I’m getting an error in the ‘Create the concept hierarchy’ section of the code as seen here: https://github.com/OHDSI/Achilles/blob/master/R/Achilles.R

connectionDetails ← createConnectionDetails(
dbms=“sql server”,
server=“DBCLTYt01”,
schema = “EpicCare.omop”,
extraSettings = “trusted_connection=true”)

achilles(connectionDetails,
cdmDatabaseSchema = “epiccare.omop”,
resultsDatabaseSchema=“epiccare.omop_results”,
vocabDatabaseSchema = “epiccare.omop”,
’ scratchDatabaseSchema = “#”,
’ scratchDatabaseSchema = “epiccare.omop”,
numThreads = 1,
sourceName = “”,
cdmVersion = “5.2.0”,
runHeel = TRUE,
runCostAnalysis = TRUE,
’ dropScratchTables = FALSE
)

You’ll note the commented out parameters in my calling code. The same error happens with or without them.

The following output shows that the Concept Hierarchies are created, but errors out BEFORE the subroutine completes.

Done. Achilles results can now be found in schema epiccare.omop_results
Executing Concept Hierarchy creation. This could take a while
Connecting using SQL Server driver using Windows integrated security
|=========================================================| 100%
Executing SQL took 3.29 secs
|=========================================================| 100%
Executing SQL took 12.3 secs
|=========================================================| 100%
Executing SQL took 4.14 secs
|=========================================================| 100%
Executing SQL took 2.96 mins
|=========================================================| 100%
Executing SQL took 2.76 mins
|=========================================================| 100%
Executing SQL took 1.47 secs
|=========================================================| 100%
Executing SQL took 2.99 secs
Connecting using SQL Server driver using Windows integrated security
Error in rJava::.jcall(resultSet, “Z”, “next”) :
com.microsoft.sqlserver.jdbc.SQLServerException: Database ‘#’ does not exist. Make sure that the name is entered correctly.

Most likely during the dropAllScratchTables() routine.

The concept_hierarchy table is created in the omop_results schema in the database.

Any clues as to what the problem could be would be appreciated.

2

If running v1.6 - try re-running after adding this additional input parameter: conceptHierarchy = FALSE. This will skip some SQL code for that feature. It may bypass the error. (but if you need the hierarchy, this will not help you).

I think the plan is that in next version - this part of Achilles will be moved to webAPI.

Also, look at error.txt file in your working directory. It will have more detail about the SQL error.

3

Thanks. I’ll try that.

4

Well, that worked – sort of. I need to fiddle with stuff to make it work. I’m just posting it here for anyone else who might run into this problem.

Adding “conceptHierarchy = FALSE” to the arguments list allowed the process to complete – without any hierarchy information (of course). However, if I try to run it a second time I get this error:

Error: Error executing SQL:
com.microsoft.sqlserver.jdbc.SQLServerException: The operation failed because an index or statistics with name ‘idx_ch_cid’ already exists on table ‘epiccare.omop_results.concept_hierarchy’.

If I delete the concept_hierarchy table, I get this error:

Error: Error executing SQL:
com.microsoft.sqlserver.jdbc.SQLServerException: Cannot find the object “epiccare.omop_results.concept_hierarchy” because it does not exist or you do not have permissions.

Damned if it’s there, damned if it’s not.

So I have to run it with conceptHierarchy = TRUE, and get my original error. Then change it to FALSE and run it a second time.

I’ve also discovered if I drop the two indexes on concept_hierarchy:

drop index omop_results.concept_hierarchy.idx_ch_cid
drop index omop_results.concept_hierarchy.idx_ch_tmap

it will also let me run it a second time with conceptHierarchy = FALSE

Either way, I don’t get hierarchy information and I still have to do something extra. But I get a HEEL report, which is better than nothing.

I’m not an R programmer, but it seems to me that if conceptHierarchy = FALSE is set, the code should not try to create indexes on the table, whether the table exists or not.

5

Just for fun, I tried running Achilles in the Multi-thread mode.

library(Achilles)
connectionDetails <- createConnectionDetails(
  dbms="sql server", 
  server="DBCLTYt01", 
  schema = "EpicCare.omop",
  extraSettings = "trusted_connection=true")

achilles(connectionDetails, 
         cdmDatabaseSchema = "epiccare.omop", 
         resultsDatabaseSchema="epiccare.omop_results",
     vocabDatabaseSchema = "epiccare.omop",
     
     scratchDatabaseSchema = "epiccare.omop_scratch",
     numThreads = 10,
     
     sourceName = "", 
     cdmVersion = "5.2.0",
     runHeel = TRUE,
     runCostAnalysis = TRUE,
     conceptHierarchy = TRUE,
     dropScratchTables = TRUE

)

This produces a different error:

Thread 2 returns error: "Error executing SQL:ncom.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name ‘epiccare.omop_scratch.tmpach_dist_1502’.

And I’m left with a whole bunch of scratch tables that I have to delete manually. The same thing happens if I use conceptHierarchy = FALSE,

Deleting the files can be done by re-running the code with these modifications:

         runCostAnalysis = FALSE,
         conceptHierarchy = FALSE,
         dropScratchTables = TRUE

It still ends with this error:

	Error: Error executing SQL:
 	com.microsoft.sqlserver.jdbc.SQLServerException: The operation failed because an index or statistics with name 'idx_ch_cid' already exists on table 'epiccare.omop_results.concept_hierarchy'

But at least the scratch files are deleted.

6

Thanks @roger.carlson. Would you mind opening up an new issue with this info.

7

Consider downgrading to prior version. If you can live without the new features in v1.6

You can do that by this command in R

devtools::install_github("OHDSI/OhdsiRTools@v1.5.0")