db_Mail Syntax error message when attempting to pass variable to @Body

• rjjh78

  SSCrazy

  Points: 2787

  More actions

  January 3, 2017 at 1:53 pm

  #316580

  To all:

  For some reason, when I attempt to add a variable to the string of my @Body I get them error message Incorrect syntax near '+'. Here is my code, any suggestions are appreciated in advance!

  DECLARE

  @CRLF varchar(10),

  @EmailTo varchar(100)

  SET @EmailTo = 'rhayward@autopartintl.com'

  SET @CRLF = char(13) + char(10)

  EXEC msdb..sp_send_dbmail

  @Profile_name = 'Reporting',

  @Recipients = @EmailTo,

  @Subject = 'Test',

  @Body = '**** This is an automated email - Please do not respond to this message. ****' + @CRLF + 'The enclosed file contains...',

  @execute_query_database = 'myDB',

  @Query = 'SELECT * FROM TEST WHERE Err IS NOT NULL',

  @attach_query_result_as_file= 1,

  @query_attachment_filename = 'TestRept.csv'

  PRINT @Body

• Lowell

  SSC Guru

  Points: 323527

  More actions

  January 3, 2017 at 2:09 pm

  #1920496

  the variable assignments CANNOT be a calculation. it has to be a variable or static string.

  below you are appending things together.

  @Body = '**** This is an automated email - Please do not respond to this message. ****' + @CRLF + 'The enclosed file contains...',

  instead, create some @variables above, do your calculations/assemble custom strings prior to the mail section, with SET/SELECT,and assign to a new @variable. use that variable to assign the value.

  repeat that logic for anything that gets constructed(like @mysubject + varchar date or whatever)

  DECLARE @HTMLBody varchar(max) = '**** This is an automated email - Please do not respond to this message. ****'

  + @CRLF

  + 'The enclosed file contains...',

  ...

  @Body = @HTMLBody ,

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• rjjh78

  SSCrazy

  Points: 2787

  More actions

  January 3, 2017 at 3:10 pm

  #1920516

  Thank you Lowell! I don't think I forget this one moving forward!