OHDSI Home | Forums | Wiki | Github

How to run CDM ddl scripts for MS Sql Server out of the box (having problems setting "@cdmDatabaseSchem")

Implementers
#1

The DDL scripts for CDM have variables like “@cdmDatabaseSchema”. If I try to set the variable and then run the script using something like DECLARE @cdmDatabaseSchemavarchar(64) = 'my_schema';
I get errors when I try to run the script in MS Sql Server Manager.

The post at the link below seems to suggest that to use the variable the entire query should be wrapped in a variable.

Is there a way to set this variable and run the script or do I basically just need to do a global search and replace?

(I did a search on “@cdmDatabaseSchema” on this forum and didn’t get any results so appologies in advance if this has already been asked and answered. )

Thanks,
John

#2

Hi @greshje.gmail ,

We usually use our SqlRender package to do such replacements in all our code. You will find many examples there. Feel free to ask again if anything is not clear there.

1 Like
#3

@jposada is correct: those tokens you see resemble variable names in MSSQL, but they are actually tokens that are replaced in something like SqlRender. The reason is: you can’t declare a variable to represent a schema name. You have to resort to things like string replacement to do the actual work.

1 Like
#4

If you need to quickly render a snippet of OHDSI-SQL code, this tool might be helpful:
https://data.ohdsi.org/SqlDeveloper/

1 Like
t