question

artistlover avatar image
artistlover asked

Sending mail error when using variable.

I am creating a table from a view provided. I need to send an email using a column in table and the body and subject use variables from the table to address each individual in the row. But i am obviously not formatting the two variables correctly. --insert into a table from the view select * into PresTresemail from dbo.vwLxxsNoPresTreas DECLARE @email_id NVARCHAR(450),@id BIGINT,@max_id BIGINT,@query NVARCHAR(1000) ,@xxname NVARCHAR(450) ,@xx NVARCHAR(450) SELECT @id=MIN(id),@max_id=MAX(id) FROM vwLMAsNoPresTreas WHILE @id<@max_id BEGIN SELECT @email_id= txtemail ,@lmaname = [xx name] , @lma = [xx] FROM prestresemail set @query='msdb.dbo.sp_send_dbmail @ profile_name=''xx@xx.com'', @recipients='''+@email_id+''', @subject=''St. xx, xxxx xx missing President and/or Treasurer'', @body='''+@LMA+ ''' '''? ''' ''' +@xxname+''' body of the test message.''' EXEC (@query) SELECT @id=MIN(id)FROM vwxxNoPresTreas where id>@id end drop table PresTresemail
dbmail
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Please post your error message
0 Likes 0 ·
i meant to take that out and put xx. OOPS Error Msg 102, Level 15, State 1, Line 18 Incorrect syntax near '?'.
0 Likes 0 ·
So DenisT that fixed it but it sent out 48 emails of the third row in the table and not any of the first two and it should only be one email per row.
0 Likes 0 ·
What is the point of using into PresTresemail if you use the "*" and then continue using the view in the loop? Also, you need a WHERE clause here -- SELECT @email_id= txtemail ,@lmaname = [xx name] , @lma = [xx] FROM prestresemail where id=@id
0 Likes 0 ·

1 Answer

·
DenisT avatar image
DenisT answered
Try this: set @query='msdb.dbo.sp_send_dbmail @ profile_name=''xx@xx.com'', @recipients=''' + @email_id + ''', @subject=''St. xx, xxxx xx missing President and/or Treasurer'', @body=''' + @LMA + ' ? ' + @xxname + ' body of the test message.''' Plus I don't see @LMA declared anywhere in your statement.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.