mercredi 22 juillet 2015

body_format not working when specified in cursor

I am using the following code to grab values from an "email_address" field, and sending an email message to those recipients found in the table. I previously had emails sending in html format with the sp_send_mail, but now in this new script, the email sends to the right people, but it is just showing up as plain text.

    DECLARE
  @email VARCHAR(64),
  @format VARCHAR(20),
  @emailSubject varchar(128),
  @emailBody varchar(128)
  DECLARE c1 CURSOR FOR 
    SELECT 
      email_address
    FROM customeremailtest
    WHERE id > 0

  OPEN c1
  FETCH NEXT FROM c1 INTO @email
  WHILE @@FETCH_STATUS <> -1
    BEGIN
    SELECT 
      @format = 'HTML',
      @emailSubject = 'Please send Feedback for Order#',
      @emailBody = '<img src="http://ift.tt/1GDw3Y1" />'

      --you cannot concatenate strings  as the parameters for a query, they must be eitehr a static string or an already built variable.
      EXEC msdb.dbo.sp_send_dbmail 
      @profile_Name ='P21 Alert',
       @recipients= @email ,
       @subject = @emailSubject,
       @body = @emailBody,
       @body_format = @format


    FETCH NEXT FROM c1 INTO @email
    END
  CLOSE c1
  DEALLOCATE c1

What am I doing wrong?

Aucun commentaire:

Enregistrer un commentaire