sqlcmd variables expansion

• Steve T

  SSCommitted

  Points: 1994

  More actions

  July 27, 2017 at 4:47 pm

  #322906

  I have a SQL agent job that uses tokens.  When I script out the job and run in SSMS, it's fine as the tokens are just text.  When I run it through SQLCMD, it's trying to interpret the tokens as variables.  I can use -x to disable variable expansion, but outside of the main script, I wanted to use some variables to dynamically assign some other data.  Is there a way to escape the $ so it doesn't get processed by SQLCMD as a variable?

  Example (inside the SQL script)

  Declare @ServerName
  SET @ServerName = $(ServerName)
  ...
  @output_file_name=N'D:\Folder\Backups_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM)).txt',

  I want $(ServerName) to be interpreted as a variable.
  I do not want this $(ESCAPE_SQUOTE(STRTDT)) to be interpreted as a variable.

• burfos

  Ten Centuries

  Points: 1141

  More actions

  July 28, 2017 at 12:01 am

  #1953141

  Try using CHAR(36) instead of the $ for the ones you don't want to be interpreted as a variable

  @output_file_name=N'D:\Folder\Backups_' + CHAR(36) + N'(ESCAPE_SQUOTE(STRTDT))_' + CHAR(36) + N'(ESCAPE_SQUOTE(STRTTM)).txt',

• Steve T

  SSCommitted

  Points: 1994

  More actions

  July 28, 2017 at 12:07 pm

  #1953236

  burfos - Friday, July 28, 2017 12:01 AM

  Try using CHAR(36) instead of the $ for the ones you don't want to be interpreted as a variable

  @output_file_name=N'D:\Folder\Backups_' + CHAR(36) + N'(ESCAPE_SQUOTE(STRTDT))_' + CHAR(36) + N'(ESCAPE_SQUOTE(STRTTM)).txt',

  Hmm, good idea. Let me try that.