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
One doubt,why we are using OLAP view tables MSP_TimesheetPeriod_OlapView and not general Publish/reporting tables?