Post reply

sqlcmd variables expansion

  • 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.

  • 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',

  • 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.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply