question

Ben-6959 avatar image
0 Votes"
Ben-6959 asked ·

How to set SQLCMD variables dynamically in SQL Server Database projects?

I'm trying to set SQLCMD variables inside an script file (rather than the project properties). Wondering if it's possible for both system-defined and custom variables. I most like to change $(DatabaseName) which is system-defined.


For this scenario, I've created a JSON file which contains the key/values for variables I want to set (Key DatabaseName).

 {
   "EnvironmentDetails": {
     "ProjectSettings": {
       "DatabaseName":  "TicketingDB"
     },
     "AppSettings": {
       "Name": "AppName"
     }
 }

Then, in a script file, I read the JSON content and try to change the variable using :setvar

 DECLARE @SQL NVARCHAR(MAX);
 DECLARE @Environment NVARCHAR(MAX);
 DECLARE @FilePath NVARCHAR(MAX) = '$(ProjectPath)' + '\Environment.json';
 DECLARE @CustomDatabase NVARCHAR(MAX);
 SET @SQL = N'
 SELECT @Result = BulkColumn FROM OPENROWSET(BULK ''' + @FilePath + ''', SINGLE_NCLOB) JSON;
 ';
 EXEC sp_executesql @SQL, N'@Result NVARCHAR(MAX) OUT', @Environment OUT;
    
 SELECT @CustomDatabase = [value] FROM OPENJSON(@Environment, '$.EnvironmentDetails.ProjectSettings') WHERE [key]='Name'
  :SETVAR DatabaseName @CustomDatabase 


But when I check the build script, there is no change in the project.

My Goal is to provide an Environment file (similar to .env files in web projects) and set project properties through a JSON data file and a script file (rather than setting project properties for each clone of my project)

I'm new to database projects, will appreciate any guides about my misconceptions and mistakes


sql-server-general
· 1
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Ben-6959,

We have not received a response from you. Did the replies could help you? If the response helped, do "Accept Answer". If it is not work, please let us know the progress.

0 Votes 0 ·
DanGuzman avatar image
1 Vote"
DanGuzman answered ·

SQLCMD variable values are set by the tool (i.e. SQLCMD or SqlPackage) before the script executes so you can't change the value dynamically via T-SQL. Instead, you need to pass the variable values to the tool, such as via the v command-line argument.


· 2 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Adding to Dan's answer: Keep in mind that SSMS/SQLCMD on the hand hand and SQL Server on the other are two different programs, typically running on different machines. Thus, SQLCMD/SSMS cannot access T-SQL variables.

1 Vote 1 ·

Thanks @Danguzman. Now I understand why I was getting issues. Unfortunately had to skip implementing this in my project as it seems not feasible

0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered ·

But when I check the build script, there is no change in the project.

That's a pre-deployment script? That script isn't evaluated/executed during build, but on deployment. Have you tested the deployment to see if your script works as expected?



· 1 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I wasn't aware that sqlcmd variables are set by the Tool (as power @DanGuzman 's answer). Had to skip implementing this in my project :/

0 Votes 0 ·
GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered ·

Your sample JSON missed a closing curly bracket:

 {
   "EnvironmentDetails": {
     "ProjectSettings": {
       "DatabaseName":  "TicketingDB"
     },
     "AppSettings": {
       "Name": "AppName"
     }
   }
 }

The issue is in the SELECT statement to open the json. Try this:

 SELECT @CustomDatabase = [value] FROM OPENJSON(@Environment, '$.EnvironmentDetails."ProjectSettings"')


·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.