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.
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.aspxin the procedure us the DATEDIFF function to calculated the expirydate being 15 days:http://technet.microsoft.com/en-us/library/ms189794.aspxus the sp_dbsendmail in the proc to actually send the mailhttp://technet.microsoft.com/en-us/library/ms190307.aspxonce 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 ithttp://technet.microsoft.com/en-us/library/ms186273.aspxHave a go, see where you get to and post your code if you get stuck :) |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
|
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. |
|
|
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 + ';', '') + Emailfrom employee_infpass that to the send_dbmail exec sp_send_dbmail ... @copy_recipients = @copy_recipients .... KH[spoiler]Time is always against us[/spoiler] |
|
|
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 + ';', '') + Emailfrom employee_infpass that to the send_dbmail exec sp_send_dbmail ... @copy_recipients = @copy_recipients .... KH[spoiler]Time is always against us[/spoiler]
|
|
|
|
|
|
|
|