SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL database mail for automated email seding
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ismalee
Starting Member

Saudi Arabia
2 Posts

Posted - 03/29/2014 :  15:27:14  Show Profile  Reply with Quote
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 - 03/31/2014 :  08:13:30  Show Profile  Reply with Quote
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)

Singapore
17601 Posts

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


KH
Time is always against us

Go to Top of Page

ismalee
Starting Member

Saudi Arabia
2 Posts

Posted - 03/31/2014 :  11:17:30  Show Profile  Reply with Quote
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)

Singapore
17601 Posts

Posted - 03/31/2014 :  22:21:22  Show Profile  Reply with Quote

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
Time is always against us

Go to Top of Page

Robowski
Posting Yak Master

101 Posts

Posted - 04/02/2014 :  07:51:38  Show Profile  Reply with Quote
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
Time is always against us



Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000