createConceptTable() fails - DROP TABLE IF EXISTS not supported by SQL Server before 2016

[kzollove]

The version of SQL Server that hosts our main CDM (2014) does not support the DROP TABLE IF EXISTS statement. Because of this, we get FATAL error at the createConceptTable() step when running executeDiagnostics:

R Report

Thread: Main
Message:  Error executing SQL:
com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'IF'.
Level:  FATAL
Time:  2023-04-13 12:28:34

Stack trace:
12: (function (condition) 
{
    if (is(condition, "error")) {
        log("FATAL", conditionMessage(co
11: signalCondition(cnd)
10: signal_abort(cnd, .file)
9: abort(paste("Error executing SQL:", message, paste("An error report has been created at ", fileN
8: .createErrorReport(dbms, err$message, sqlStatement, errorReportFile)
7: value[[3]](cond)
6: tryCatchOne(expr, names, parentenv, handlers[[1]])
5: tryCatchList(expr, classes, parentenv, handlers)
4: tryCatch({
    startQuery <- Sys.time()
    lowLevelExecuteSql(connection, sqlStatement)
    delta 
3: DatabaseConnector::executeSql(connection = connection, sql = sql, progressBar = FALSE, reportOve
2: createConceptTable(connection, tempEmulationSchema)
1: CohortDiagnostics::executeDiagnostics(cohortDefinitionSet, connectionDetails = connectionDetails

R version:
R version 4.2.1 (2022-06-23 ucrt)

Platform:
x86_64-w64-mingw32

Attached base packages:
- stats
- graphics
- grDevices
- utils
- datasets
- methods
- base

Other attached packages:
- CohortDiagnostics (3.2.1)
- FeatureExtraction (3.2.0)
- Andromeda (0.6.3)
- dplyr (1.1.1)
- DatabaseConnector (6.1.0)

SQL report

DBMS:
sql server

Error:
com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'IF'.

SQL:
DROP TABLE IF EXISTS #concept_ids

R version:
R version 4.2.1 (2022-06-23 ucrt)

Platform:
x86_64-w64-mingw32

Attached base packages:
- stats
- graphics
- grDevices
- utils
- datasets
- methods
- base

Other attached packages:
- CohortDiagnostics (3.2.1)
- FeatureExtraction (3.2.0)
- Andromeda (0.6.3)
- dplyr (1.1.1)
- DatabaseConnector (6.1.0)

https://github.com/OHDSI/CohortDiagnostics/blob/311e7c6944d82b9ab9da0bd7d856a975fa5a55b9/inst/sql/sql_server/CreateConceptIdTable.sql#L1

[azimov]

Hi @kzollove this is an issue with SqlRender - We dropped support for very old versions of SqlServer around last year. We discussed supporting it again but it's difficult for us to justify. Is there any reason that you can't upgrade to a newer version?

[kzollove]

Hi @azimov whoops I should've looked into the backlog (https://github.com/OHDSI/CohortDiagnostics/issues/981 is referring to same CDM instance).

I imagined that our SqlServer version was just too old to be supported. We are in the process of cloud migration but need to use the legacy database for an urgent project. For the time being I think we will just fork and edit.