OHDSI Home | Forums | Wiki | Github

Corozon - SQL Server db with dashes in the name

Implementers
#1

I have a CDM based in SQL Server (in azure) named “myorg-db-omop”. Because it has the dashes in the name I have to include brackets around that name when I refer to it or any schema within it like this in R:

cdmDatabaseSchema ← “[myorg-db-omop].dbo”
cohortDatabaseSchema ← “[myorg-db-omop].results”
cohortTable ← “my_study_cohorts”

However running the execute command for the Corazon package returns this error in errorReportSql.txt which implies that for some reason, the brackets have been dropped even though the SQL below it includes those brackets.

Does anyone know what is going on here? Running the IF … DROP… statements in the error report’s SQL code works when I submit it to the database directly in SQL Server.

from errorReportSql.txt:

Error:
com.microsoft.sqlserver.jdbc.SQLServerException: Reference to database and/or server name in ‘myorg-db-omop.results.my_study_cohorts’ is not supported in this version of SQL Server.

SQL:
IF OBJECT_ID(’[myorg-db-omop].results.my_study_cohorts’, ‘U’) IS NOT NULL
DROP TABLE [myorg-db-omop].results.my_study_cohorts

R version:
R version 4.2.2 (2022-10-31 ucrt)

Platform:
x86_64-w64-mingw32

Attached base packages:

  • stats
  • graphics
  • grDevices
  • utils
  • datasets
  • methods
  • base

Other attached packages:

  • corazon (0.0.1)
  • DatabaseConnector (6.0.0)
#2

In addition to using the [] brackets in SQL Server, you can instead use the double-quotes ("). I haven’t tried it with R, but you could probably use the escape character (\) before the embedded quotes to include them in the string. Something like:

cdmDatabaseSchema ← "\"myorg-db-omop\".dbo"

Untested, FWIW

#3

Thanks Roger,

I tried your idea as well as using single quotes to create a string which will includes double quotes within it like below.

cdmDatabaseSchema <- '"myorg-db-omop"."dbo"'
cohortDatabaseSchema <- '"myorg-db-omop"."results"'
cohortTable <- '"my_study_cohorts"'

Unfortunately, both of those solutions caused the same error:

Error:
com.microsoft.sqlserver.jdbc.SQLServerException: Reference to database and/or server name in ‘providence-db-omop.results.my_study_cohorts’ is not supported in this version of SQL Server.

SQL:
IF OBJECT_ID(’“providence-db-omop”.“results”.“my_study_cohorts”’, ‘U’) IS NOT NULL
DROP TABLE “providence-db-omop”.“results”.“my_study_cohorts”

I have also now tried updating my jdbc driver for java runtime environment (v8). I was previously using jdbc v9 but jdbc v12.2 found on Microsoft’s website returned the same issue.

#4

I suspect, then, that the problem has nothing to do with the brackets, but lies somewhere else.

Silly question, but needs to be asked. Did you spell everything correctly?

#5

I agree, it must be something between R and SQL Server. I have visually checked the spelling and I am further confident that the syntax is fine because I am successful when submitting the sql code from the error directly into SQL.

I have even used my usual method for connection via R using the package DBI with the “ODBC Driver 17 for SQL Server”. Doing so with that package/driver worked without a problem which is what made me suspect it was a JDBC driver issue.

I am not very familiar with the difference between JDK and JRE. Do you know if JRE (v8 specifically) works with OHDSI R packages?

#6

I don’t. Sorry. Maybe @Ajit_Londhe could help. (He knows everything.)

#7

Guessing here, but the original error seems to indicate the database name should be handled in the connection itself rather than in the query as the fully qualified schema name.

Can you adjust your connectionDetails to have “myorg-db-omop” in the server name and then use “results” as the schema name?

connectionDetails <- DatabaseConnector::createConnectionDetails(dbms = "sql server", server = "some-server/myorg-db-omop.....)

#8

I’m not sure which version of SQLServer you are using (is it Sql Express?) but it’s saying you can’t reference the database name (or server name) in the table qualifier. So, no amount of escaping will get around this (if that’s the problem). So, instead, just put the database as part of the connection string and then don’t provide the database name in the table qualifier.

To test if this is a sql version issue, just execute the following in your SqlServer IDE:

select count(*) from [myorg-db-omop].results.my_study_cohorts

If i’m correct, you shouldn’t be able to reference the database in the table qualifier of the FROM clause.

Edit: does this stack-overflow article address your question?

#9

Thanks again everyone.

Chris, I am successful using this code below which I think answers your question. I can’t find exactly which version of SQL Server is used in Azure or in our specific server.

select * from [myorg-db-omop].dbo.concept

I tried Ajit’s suggestion but that didn’t work. It did give me the idea to copy and modify the connection string I found in Azure for this specific database. I realized that using the connection string allows me to state the database name which createConnectionDetails doesn’t allow as a stand alone argument.

This is my code now and I think it works in terms of a connection. I think this because my new error is a WRITE permissions issue with the SQL user profile I am using which I realized is READ only.

connectionDetails <- DatabaseConnector::createConnectionDetails(
        dbms = "sql server",
        connectionString = "jdbc:sqlserver://myazureserver.database.windows.net:1433;database=myorg-db-omop;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;",
        pathToDriver = Sys.getenv("DATABASECONNECTOR_JAR_FOLDER"),
        user = Sys.getenv("username"),
        password = Sys.getenv("password")
)

I will revisit this post after I get access to the read and write username for the db.

#10

This connection string above did work for me but something is off with my SQL Server instance. I have instead gotten this to work in snowflake but it took some finagling.

  connectionDetails <- DatabaseConnector::createConnectionDetails(dbms = "snowflake",
       connectionString =  'jdbc:snowflake://mysnowflakeserver.snowflakecomputing.com?db=MYOMOPDB&schema=OMOP',
                    pathToDriver = Sys.getenv("DATABASECONNECTOR_JAR_FOLDER"),
                    user = Sys.getenv("sf_user"),
                    password = Sys.getenv("sf_pass"))
t