March 26, 2012 at 12:03 am
Dear All,
I need help from you. I am having a VIEW named as todayBday where it'll show Present day B'day List of Employees in following format:
-------------------------------------------------------
Name | Email | DOB
-------------------------------------------------------
John Carter | john@gmail.com | 3/26/1985 12:00:00 AM
Mia Suzki | mia@hotmail.com | 3/26/1978 12:00:00 AM
-------------------------------------------------------
Now my query is how to send the Birthday Wish to the Following Employees using MS SQL Database Mail.
For you information, I had already created Email Profile & tested the mail sending configuration, it's working. But don't know how connect the same with the VIEW and send mail using Column data.
Hope I can make you understand my query, please help.
March 26, 2012 at 1:16 am
you would want to look at the sp_send_dbmail procedure which lives in MSDB passing in the parameters you need.
if you want to send personalised mail messages to each, i am thinking your going to have to do a loop of sorts, but if its a standard message to all, you could pass them all in as the BCC recipients and do it in a set based fashion.
March 26, 2012 at 2:43 am
I just want to send a personalized message like this:
Dear John Carter,
Wish a very Happy and Prosperous Birthday.
Regards,
Administrator
I am new to Database Mail, what T-SQL should I opt to this?
March 26, 2012 at 2:48 am
you will need to build some kind of loop, (while loop, cursor) then pass in the email and other data into parameters which are passed into sp_send_dbmail
pesudo code would be something like this
declare loop
get data
declare parameters
set parameters
pass paramaters into sp_send_dbmail
get next record
March 26, 2012 at 2:57 am
Sorry I have no idea for pseudo code..:ermm:
March 26, 2012 at 3:00 am
its the steps you need to take to get the task done.
first point of call would be to google sp_send_dbmail and see what parameters it needs to work, then google loops/cursors in sql
March 26, 2012 at 3:03 am
OK..let me go ahead and get my T-SQL ready, if some error comes up, will take your help..thanks a lot for guidance
March 26, 2012 at 3:57 am
I did this T-SQL:
DECLARE @p_body as nvarchar(max), @p_subject as nvarchar(max)
DECLARE @p_recipients as nvarchar(max), @p_profile_name as nvarchar(max)
DECLARE @xml NVARCHAR(MAX)
SET @p_profile_name = N'Birthday Wish Admin'
SET @p_recipients = (SELECT Stuff((SELECT N'; ' + FROM [dbo].[todayBday] FOR XML PATH(''),TYPE).value('text()[1]','nvarchar(max)'),1,2,N''))
SET @p_subject = N'Wish You a Very Happy & Prosperous Birthday'
SET @p_body = '<p style="text-align: center;"><span style="color:#ff0000;"><strong><span style="font-size: 22px;">Wish YOU a very Happy & Prosperous Birthday</span></strong></span>
<span style="font-size:20px;">May the sun's rays shine brightest on you today with the wind at your heals.</span>
<span style="font-size:20px;">Happy Birthday.</span>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @p_profile_name,
@recipients = 'admin@xyz.com',
@blind_copy_recipients = @p_recipients,
@body = @p_body,
@body_format = 'HTML',
@subject = @p_subject
I paste the following T-SQL in JOB.
March 26, 2012 at 4:07 am
only problem would be the text as you have sun's you will need to contatinate in a char(39) to avoid using ' and terminating the string
sun'+char(39)+'s
and as always test it to make sure it does what you need it to do before putting it live.
March 26, 2012 at 4:22 am
Yes, I changed that part.
But, one more query, how can I include NAME column and send mail individually?
like:
Dear John Carter
Happy Birthday
OR
Dear Mia Suzki
Happy Birthday
Can you help me on this??
March 26, 2012 at 4:38 am
thats where you need the loop so that it send the email 1 by 1
DECLARE @Name NVARCHAR(100), @Email NVARCHAR(100), @Message NVARCHAR(MAX), @It INT, @Num INT
SELECT @It = COUNT(*) FROM todayBday
SET @Num = 1
WHILE @Num <= @It
BEGIN
WITH CTE AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY Name,Email,DOB) AS RowNum
,Name
FROM
todayBday
)
SELECT
@Name = Name
,@Email = Email
,@Message = 'Dear ' + Name + CHAR(10) + CHAR(13) + 'Happy Birthday' + CHAR(10) + CHAR(13) + 'From Administrator'
FROM
CTE
WHERE
RowNum = @Num
EXEC
msdb.dbo.sp_send_dbmail
@profile_name = ''
,@recipient = @Email
,@body = @Message
,@subject = 'Subject'
SET @Num = @Num + 1
END
March 26, 2012 at 5:39 am
Thanks a lot...:-)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply