Competition: What’s your favorite Redgate tool? Enter now.

Format fails if SQLCMD mode variable is present

eklein@ahd.com[email protected] Posts: 22 Bronze 2
edited October 25, 2016 11:33AM in SQL Prompt
the following
UPDATE [provider_master]
 set     FIPS = p.fips_code,
         MSA  = p.msa_code,
         longatude = p.fac_long,
         latatude  = p.fac_lat
FROM         provider_master pm INNER JOIN
                       profile_$(HCycleP).dbo.Profile_most_recent p
                        ON pm.RPT_REC_NUM = p.RPT_REC_NUM
								 WHERE 1 = 1

will fail to Format. If i replace $(HCycleP) with it's substitute value then the format functions correctly

SQLCMD mode is on at the time I attempt to do the Format function

Comments

  • Hi,

    Unfortunately SQL Prompt doesn't handle partial replacements of identifiers with SQLCMD variables at the moment, however a simple work around is to surround the identifier with square brackets, eg:
    UPDATE [provider_master]
     set     FIPS = p.fips_code,
             MSA  = p.msa_code,
             longatude = p.fac_long,
             latatude  = p.fac_lat
    FROM         provider_master pm INNER JOIN
                           [profile_$(HCycleP)].dbo.Profile_most_recent p
                            ON pm.RPT_REC_NUM = p.RPT_REC_NUM
                             WHERE 1 = 1
    

    Hopefully adding square brackets around the identifier will work for you but if not, please let me know.

    Thanks,
    Aaron.
Sign In or Register to comment.