September 15, 2008 at 7:41 am
i have this procedure to send email , and i have two records in the table , but i when i run the procedure it give me the two record in one email and i want it in sperate emails, what i mean that i want every record ( every row ) in one email --- second request i want to put the email address in a variable, this variable i will select from table like :
SET @Email_recipients = select @Email_recipients = Email from [Helpdesk].[dbo].send_mail
but this one not working
Procedure :
___________
ALTER PROCEDURE [dbo].[Send_Request_Mail]
AS
begin
--Delete [Helpdesk].[dbo].send_mail where
-- send_mail.ID IN (Select send_mail_Temp.ID from [Helpdesk].[dbo].send_mail_Temp )
DECLARE @tableHTML NVARCHAR(MAX) ;
DECLARE @Email_recipients NVARCHAR(MAX) ;
SET @Email_recipients = 'ikrami.sami@gd-tv.com'
--select @Email_recipients = Email from [Helpdesk].[dbo].send_mail
SET @tableHTML =
N' ' +
N' ' +
N' ' +
N' ' +
N' '+
N' '+
CAST ( ( SELECT td = Requestor, '',
td = Details, '',
td = Phone, '',
td = Department, '',
td = ID, '',
td = Email, ''
FROM [Helpdesk].[dbo].send_mail
WHERE Flag is null or flag <> '1'
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N' ' ;
EXEC MSDB.dbo.sp_send_dbmail
@profile_name = 'Helpdesk',
@recipients = @Email_recipients ,
@subject = 'Subject text',
@body = @tableHTML,
@body_format = 'HTML' ;
-- Inserting Record From Send_Mail to Send_Mail_Temp
update send_mail set flag ='1'
end
September 15, 2008 at 10:42 am
You're going to need to have a cursor step through the rows of your table and call sp_send_dbmail once for each row.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 15, 2008 at 1:55 pm
can you please explain how can ido that
September 16, 2008 at 6:54 am
Books Online has directions on how to do that, under "declare cursor". Also has samples and examples you can copy.
I don't know your tables, so I can't give you a complete solution, but if you go by the samples in BOL, you'll be just fine. It's pretty easy to learn. (Too easy, actually.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply