Format fails if SQLCMD mode variable is present

eklein@ahd.com

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

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.