Send Timesheet Status Alert Using SQL Notification

Many a times we donot want to enable the e-mail notification in project server reason being it starts bombarding mails to users inbox and after sometime users get resistant to those mails and don’t even look at those :), so you would want to send emails just for specific purpose, for eg. like sending timesheet status alert so here is one solution, have look

CREATE PROCEDURE [dbo]. [SendMail] AS
BEGIN
DECLARE @to VARCHAR ( 70)
DECLARE @recipient_name VARCHAR ( 50) 
DECLARE @weekdate VARCHAR ( 11) 
DECLARE getUserDetail CURSOR
FOR
SELECT resourceemailaddress, r. resourcename, CONVERT ( VARCHAR ( 11), enddate) enddate

FROM  MSP_TimesheetPeriod_OlapView TP inner join MSP_Timesheet_OlapView T ON TP. PeriodUID  = T. PeriodUID inner join MSP_TimesheetResource_OlapView RT ON T. OwnerResourceNameUID = RT. ResourceNameUID inner join MSP_Epmresource_UserView R on RT. ResourceUID = R. ResourceUID
WHERE TimeSheetStatusId = 0 and   Enddate < GETDATE()
OPEN getUserDetail
FETCH NEXT FROM getUserDetail INTO @to, @recipient_name, @weekdate
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC  msdb. dbo. sp_send_dbmail @recipients = @to, @subject = ‘Timesheet Alert’ , @body = ‘You have not submitted your Timesheet for last week.
Warm Regards PS Admin , @body_format = ‘HTML’ 
FETCH NEXT FROM getUserDetail
INTO @to
END
CLOSE getUserDetail
DEALLOCATE getUserDetail
END

---

Advertisements