How can we help you today? How can we help you today?

Format fails if SQLCMD mode variable is present

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
eklein
0

Comments

1 comment

Sort by
  • Aaron L
    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.
    Aaron L
    0

Add comment

Please sign in to leave a comment.