Post reply

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

  • 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

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

  • January 3, 2017 at 3:10 pm

    #1920516

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

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

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