Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL database mail for automated email seding

Author  Topic 

ismalee
Starting Member

2 Posts

Posted - 2014-03-29 : 15:27:14
Dears,

I am newb to SQL. Last day i have migrated my ACCESS database backend to SQL (2008 r2 standard ). My application is basically for employee management purpose.

I have tables

1. "employee_inf" with fields "Name", "Email", "EmpID" and "WorkID" ect.

2. "Workpermit" with fields "workID", "EmplID" "Issuedate", "Expirydate"

I want sent auto email to employee before 15 days of "Expirydate" of

"workID"


can somebody help me with a solution.

Regards.

Robowski
Posting Yak Master

101 Posts

Posted - 2014-03-31 : 08:13:30
quote:
Originally posted by ismalee

Dears,

I am newb to SQL. Last day i have migrated my ACCESS database backend to SQL (2008 r2 standard ). My application is basically for employee management purpose.

I have tables

1. "employee_inf" with fields "Name", "Email", "EmpID" and "WorkID" ect.

2. "Workpermit" with fields "workID", "EmplID" "Issuedate", "Expirydate"

I want sent auto email to employee before 15 days of "Expirydate" of

"workID"


can somebody help me with a solution.

Regards.




Best way will be to first setup a stored procedure:
http://technet.microsoft.com/en-us/library/ms187926.aspx

in the procedure us the DATEDIFF function to calculated the expirydate being 15 days:
http://technet.microsoft.com/en-us/library/ms189794.aspx

us the sp_dbsendmail in the proc to actually send the mail
http://technet.microsoft.com/en-us/library/ms190307.aspx

once you have tested your proc works. set up a new sql agent job to run every night, say 1 min past midnight for example and use the proc in it
http://technet.microsoft.com/en-us/library/ms186273.aspx



Have a go, see where you get to and post your code if you get stuck :)
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-03-31 : 10:24:50
you will need to configure database mail http://technet.microsoft.com/en-us/library/hh245116.aspx first before able to use it


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

ismalee
Starting Member

2 Posts

Posted - 2014-03-31 : 11:17:30
Hi i have figured it as below and i am able to send email

"
EXEC sp_send_dbmail
@profile_name = 'MailProfile1',
@recipients = 'hr@mycompany.com' ,
@subject = 'T-SQL Query Result',
@body = 'Please Find atached list of work permit card which are expiring in next 15 days',
@query = 'use hrmiksasql
SELECT [employee_inf].[Email],[Employee_Info].[Name],[Employee_Info].[EmpID], [Workpermit].[workID], [Workpermit].[ExpiryDate]
FROM [employee_inf],[Workpermit]
where [Workpermit].[EmpID] = [Employee_Info].[EmpID] and
[Workpermit].[Notification date] = Convert(Date, GetDate(), 101)',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'Notification.csv',
@query_result_header = 1,
@body_format = 'HTML';
"

Now i want to select mail recipient cc as [employee_inf].[Email]. how can i select this.

Thanks in advance.

Regards.
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-03-31 : 22:21:22

declare @copy_recipients varchar(1000)

select @copy_recipients = isnull(@copy_recipients + ';', '') + Email
from employee_inf

pass that to the send_dbmail

exec sp_send_dbmail ... @copy_recipients = @copy_recipients ....




KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

Robowski
Posting Yak Master

101 Posts

Posted - 2014-04-02 : 07:51:38
Alternatively if you are in a company that has your own mailing server, create an e-mail group and hard code it into the proc. You can then maintain the group which would probably be easier if you need to add/remove recipients through time to the group.

quote:
Originally posted by khtan


declare @copy_recipients varchar(1000)

select @copy_recipients = isnull(@copy_recipients + ';', '') + Email
from employee_inf

pass that to the send_dbmail

exec sp_send_dbmail ... @copy_recipients = @copy_recipients ....




KH
[spoiler]Time is always against us[/spoiler]



Go to Top of Page
   

- Advertisement -