I've set in my profile the "Receive email alerts" to No.
The emails are always sended.
PS I use the scheduler.
1) no issue on watched
2) assigned resource
1) no
2) yes
Ok, thanks got it now.
Please run the following sql to update a strored procedure to fix the issue:
/*************************************************************
* gemini_getusersforissuealert
*************************************************************/
IF EXISTS(SELECT name FROM sysobjects WHERE name='gemini_getusersforissuealert') DROP PROCEDURE gemini_getusersforissuealert go CREATE PROCEDURE gemini_getusersforissuealert @projectid NUMERIC(10,0), @issueid NUMERIC(10,0) as begin -- issue watchers SELECT userid,1024 AS watchcode into #temp1 FROM gemini_watchissues WHERE projectid=@projectid AND issueid=@issueid AND userid not in (SELECT userid FROM gemini_usersettings WHERE sname=N'ReceiveEmailAlerts' AND svalue=N'N') -- project watchers SELECT userid,watchcode into #temp2 FROM gemini_watchproject WHERE projectid=@projectid AND userid not in (SELECT userid FROM #temp1) AND userid not in (SELECT userid FROM gemini_usersettings WHERE sname=N'ReceiveEmailAlerts' AND svalue=N'N') SELECT a.userid,a.watchcode,b.emailaddress into #temp3 FROM #temp1 a, gemini_users b WHERE a.userid=b.userid UNION ALL SELECT a.userid,a.watchcode,b.emailaddress FROM #temp2 a, gemini_users b WHERE a.userid=b.userid SELECT a.userid,a.watchcode,a.emailaddress,ISNULL(b.svalue,N'H') AS emailformat,ISNULL(c.svalue,N'Y') AS emailmyself FROM #temp3 a LEFT OUTER JOIN (SELECT userid,svalue FROM gemini_usersettings WHERE sname=N'EmailFormat') b on b.userid=a.userid LEFT OUTER JOIN (SELECT userid,svalue FROM gemini_usersettings WHERE sname=N'EmailMyChanges') c on c.userid=a.userid end go
DROP PROCEDURE gemini_getusersforissuealert go CREATE PROCEDURE gemini_getusersforissuealert @projectid NUMERIC(10,0), @issueid NUMERIC(10,0) as begin -- issue watchers SELECT userid,1024 AS watchcode into #temp1 FROM gemini_watchissues WHERE projectid=@projectid AND issueid=@issueid AND userid not in (SELECT userid FROM gemini_usersettings WHERE sname=N'ReceiveEmailAlerts' AND svalue=N'N') -- project watchers SELECT userid,watchcode into #temp2 FROM gemini_watchproject WHERE projectid=@projectid AND userid not in (SELECT userid FROM #temp1) AND userid not in (SELECT userid FROM gemini_usersettings WHERE sname=N'ReceiveEmailAlerts' AND svalue=N'N') SELECT a.userid,a.watchcode,b.emailaddress into #temp3 FROM #temp1 a, gemini_users b WHERE a.userid=b.userid UNION ALL SELECT a.userid,a.watchcode,b.emailaddress FROM #temp2 a, gemini_users b WHERE a.userid=b.userid SELECT a.userid,a.watchcode,a.emailaddress,ISNULL(b.svalue,N'H') AS emailformat,ISNULL(c.svalue,N'Y') AS emailmyself FROM #temp3 a LEFT OUTER JOIN (SELECT userid,svalue FROM gemini_usersettings WHERE sname=N'EmailFormat') b on b.userid=a.userid LEFT OUTER JOIN (SELECT userid,svalue FROM gemini_usersettings WHERE sname=N'EmailMyChanges') c on c.userid=a.userid end go
go
CREATE PROCEDURE gemini_getusersforissuealert @projectid NUMERIC(10,0), @issueid NUMERIC(10,0) as begin -- issue watchers SELECT userid,1024 AS watchcode into #temp1 FROM gemini_watchissues WHERE projectid=@projectid AND issueid=@issueid AND userid not in (SELECT userid FROM gemini_usersettings WHERE sname=N'ReceiveEmailAlerts' AND svalue=N'N') -- project watchers SELECT userid,watchcode into #temp2 FROM gemini_watchproject WHERE projectid=@projectid AND userid not in (SELECT userid FROM #temp1) AND userid not in (SELECT userid FROM gemini_usersettings WHERE sname=N'ReceiveEmailAlerts' AND svalue=N'N') SELECT a.userid,a.watchcode,b.emailaddress into #temp3 FROM #temp1 a, gemini_users b WHERE a.userid=b.userid UNION ALL SELECT a.userid,a.watchcode,b.emailaddress FROM #temp2 a, gemini_users b WHERE a.userid=b.userid SELECT a.userid,a.watchcode,a.emailaddress,ISNULL(b.svalue,N'H') AS emailformat,ISNULL(c.svalue,N'Y') AS emailmyself FROM #temp3 a LEFT OUTER JOIN (SELECT userid,svalue FROM gemini_usersettings WHERE sname=N'EmailFormat') b on b.userid=a.userid LEFT OUTER JOIN (SELECT userid,svalue FROM gemini_usersettings WHERE sname=N'EmailMyChanges') c on c.userid=a.userid end go
@projectid NUMERIC(10,0), @issueid NUMERIC(10,0) as begin -- issue watchers SELECT userid,1024 AS watchcode into #temp1 FROM gemini_watchissues WHERE projectid=@projectid AND issueid=@issueid AND userid not in (SELECT userid FROM gemini_usersettings WHERE sname=N'ReceiveEmailAlerts' AND svalue=N'N') -- project watchers SELECT userid,watchcode into #temp2 FROM gemini_watchproject WHERE projectid=@projectid AND userid not in (SELECT userid FROM #temp1) AND userid not in (SELECT userid FROM gemini_usersettings WHERE sname=N'ReceiveEmailAlerts' AND svalue=N'N') SELECT a.userid,a.watchcode,b.emailaddress into #temp3 FROM #temp1 a, gemini_users b WHERE a.userid=b.userid UNION ALL SELECT a.userid,a.watchcode,b.emailaddress FROM #temp2 a, gemini_users b WHERE a.userid=b.userid SELECT a.userid,a.watchcode,a.emailaddress,ISNULL(b.svalue,N'H') AS emailformat,ISNULL(c.svalue,N'Y') AS emailmyself FROM #temp3 a LEFT OUTER JOIN (SELECT userid,svalue FROM gemini_usersettings WHERE sname=N'EmailFormat') b on b.userid=a.userid LEFT OUTER JOIN (SELECT userid,svalue FROM gemini_usersettings WHERE sname=N'EmailMyChanges') c on c.userid=a.userid end go
@issueid NUMERIC(10,0) as begin -- issue watchers SELECT userid,1024 AS watchcode into #temp1 FROM gemini_watchissues WHERE projectid=@projectid AND issueid=@issueid AND userid not in (SELECT userid FROM gemini_usersettings WHERE sname=N'ReceiveEmailAlerts' AND svalue=N'N') -- project watchers SELECT userid,watchcode into #temp2 FROM gemini_watchproject WHERE projectid=@projectid AND userid not in (SELECT userid FROM #temp1) AND userid not in (SELECT userid FROM gemini_usersettings WHERE sname=N'ReceiveEmailAlerts' AND svalue=N'N') SELECT a.userid,a.watchcode,b.emailaddress into #temp3 FROM #temp1 a, gemini_users b WHERE a.userid=b.userid UNION ALL SELECT a.userid,a.watchcode,b.emailaddress FROM #temp2 a, gemini_users b WHERE a.userid=b.userid SELECT a.userid,a.watchcode,a.emailaddress,ISNULL(b.svalue,N'H') AS emailformat,ISNULL(c.svalue,N'Y') AS emailmyself FROM #temp3 a LEFT OUTER JOIN (SELECT userid,svalue FROM gemini_usersettings WHERE sname=N'EmailFormat') b on b.userid=a.userid LEFT OUTER JOIN (SELECT userid,svalue FROM gemini_usersettings WHERE sname=N'EmailMyChanges') c on c.userid=a.userid end go
as
begin
-- issue watchers SELECT userid,1024 AS watchcode into #temp1 FROM gemini_watchissues WHERE projectid=@projectid AND issueid=@issueid AND userid not in (SELECT userid FROM gemini_usersettings WHERE sname=N'ReceiveEmailAlerts' AND svalue=N'N') -- project watchers SELECT userid,watchcode into #temp2 FROM gemini_watchproject WHERE projectid=@projectid AND userid not in (SELECT userid FROM #temp1) AND userid not in (SELECT userid FROM gemini_usersettings WHERE sname=N'ReceiveEmailAlerts' AND svalue=N'N') SELECT a.userid,a.watchcode,b.emailaddress into #temp3 FROM #temp1 a, gemini_users b WHERE a.userid=b.userid UNION ALL SELECT a.userid,a.watchcode,b.emailaddress FROM #temp2 a, gemini_users b WHERE a.userid=b.userid SELECT a.userid,a.watchcode,a.emailaddress,ISNULL(b.svalue,N'H') AS emailformat,ISNULL(c.svalue,N'Y') AS emailmyself FROM #temp3 a LEFT OUTER JOIN (SELECT userid,svalue FROM gemini_usersettings WHERE sname=N'EmailFormat') b on b.userid=a.userid LEFT OUTER JOIN (SELECT userid,svalue FROM gemini_usersettings WHERE sname=N'EmailMyChanges') c on c.userid=a.userid end go
SELECT userid,1024 AS watchcode into #temp1 FROM gemini_watchissues WHERE projectid=@projectid AND issueid=@issueid AND userid not in (SELECT userid FROM gemini_usersettings WHERE sname=N'ReceiveEmailAlerts' AND svalue=N'N') -- project watchers SELECT userid,watchcode into #temp2 FROM gemini_watchproject WHERE projectid=@projectid AND userid not in (SELECT userid FROM #temp1) AND userid not in (SELECT userid FROM gemini_usersettings WHERE sname=N'ReceiveEmailAlerts' AND svalue=N'N') SELECT a.userid,a.watchcode,b.emailaddress into #temp3 FROM #temp1 a, gemini_users b WHERE a.userid=b.userid UNION ALL SELECT a.userid,a.watchcode,b.emailaddress FROM #temp2 a, gemini_users b WHERE a.userid=b.userid SELECT a.userid,a.watchcode,a.emailaddress,ISNULL(b.svalue,N'H') AS emailformat,ISNULL(c.svalue,N'Y') AS emailmyself FROM #temp3 a LEFT OUTER JOIN (SELECT userid,svalue FROM gemini_usersettings WHERE sname=N'EmailFormat') b on b.userid=a.userid LEFT OUTER JOIN (SELECT userid,svalue FROM gemini_usersettings WHERE sname=N'EmailMyChanges') c on c.userid=a.userid end go
into #temp1 FROM gemini_watchissues WHERE projectid=@projectid AND issueid=@issueid AND userid not in (SELECT userid FROM gemini_usersettings WHERE sname=N'ReceiveEmailAlerts' AND svalue=N'N') -- project watchers SELECT userid,watchcode into #temp2 FROM gemini_watchproject WHERE projectid=@projectid AND userid not in (SELECT userid FROM #temp1) AND userid not in (SELECT userid FROM gemini_usersettings WHERE sname=N'ReceiveEmailAlerts' AND svalue=N'N') SELECT a.userid,a.watchcode,b.emailaddress into #temp3 FROM #temp1 a, gemini_users b WHERE a.userid=b.userid UNION ALL SELECT a.userid,a.watchcode,b.emailaddress FROM #temp2 a, gemini_users b WHERE a.userid=b.userid SELECT a.userid,a.watchcode,a.emailaddress,ISNULL(b.svalue,N'H') AS emailformat,ISNULL(c.svalue,N'Y') AS emailmyself FROM #temp3 a LEFT OUTER JOIN (SELECT userid,svalue FROM gemini_usersettings WHERE sname=N'EmailFormat') b on b.userid=a.userid LEFT OUTER JOIN (SELECT userid,svalue FROM gemini_usersettings WHERE sname=N'EmailMyChanges') c on c.userid=a.userid end go
FROM gemini_watchissues WHERE projectid=@projectid AND issueid=@issueid AND userid not in (SELECT userid FROM gemini_usersettings WHERE sname=N'ReceiveEmailAlerts' AND svalue=N'N') -- project watchers SELECT userid,watchcode into #temp2 FROM gemini_watchproject WHERE projectid=@projectid AND userid not in (SELECT userid FROM #temp1) AND userid not in (SELECT userid FROM gemini_usersettings WHERE sname=N'ReceiveEmailAlerts' AND svalue=N'N') SELECT a.userid,a.watchcode,b.emailaddress into #temp3 FROM #temp1 a, gemini_users b WHERE a.userid=b.userid UNION ALL SELECT a.userid,a.watchcode,b.emailaddress FROM #temp2 a, gemini_users b WHERE a.userid=b.userid SELECT a.userid,a.watchcode,a.emailaddress,ISNULL(b.svalue,N'H') AS emailformat,ISNULL(c.svalue,N'Y') AS emailmyself FROM #temp3 a LEFT OUTER JOIN (SELECT userid,svalue FROM gemini_usersettings WHERE sname=N'EmailFormat') b on b.userid=a.userid LEFT OUTER JOIN (SELECT userid,svalue FROM gemini_usersettings WHERE sname=N'EmailMyChanges') c on c.userid=a.userid end go
AND userid not in (SELECT userid FROM gemini_usersettings WHERE sname=N'ReceiveEmailAlerts' AND svalue=N'N') -- project watchers SELECT userid,watchcode into #temp2 FROM gemini_watchproject WHERE projectid=@projectid AND userid not in (SELECT userid FROM #temp1) AND userid not in (SELECT userid FROM gemini_usersettings WHERE sname=N'ReceiveEmailAlerts' AND svalue=N'N') SELECT a.userid,a.watchcode,b.emailaddress into #temp3 FROM #temp1 a, gemini_users b WHERE a.userid=b.userid UNION ALL SELECT a.userid,a.watchcode,b.emailaddress FROM #temp2 a, gemini_users b WHERE a.userid=b.userid SELECT a.userid,a.watchcode,a.emailaddress,ISNULL(b.svalue,N'H') AS emailformat,ISNULL(c.svalue,N'Y') AS emailmyself FROM #temp3 a LEFT OUTER JOIN (SELECT userid,svalue FROM gemini_usersettings WHERE sname=N'EmailFormat') b on b.userid=a.userid LEFT OUTER JOIN (SELECT userid,svalue FROM gemini_usersettings WHERE sname=N'EmailMyChanges') c on c.userid=a.userid end go
-- project watchers SELECT userid,watchcode into #temp2 FROM gemini_watchproject WHERE projectid=@projectid AND userid not in (SELECT userid FROM #temp1) AND userid not in (SELECT userid FROM gemini_usersettings WHERE sname=N'ReceiveEmailAlerts' AND svalue=N'N') SELECT a.userid,a.watchcode,b.emailaddress into #temp3 FROM #temp1 a, gemini_users b WHERE a.userid=b.userid UNION ALL SELECT a.userid,a.watchcode,b.emailaddress FROM #temp2 a, gemini_users b WHERE a.userid=b.userid SELECT a.userid,a.watchcode,a.emailaddress,ISNULL(b.svalue,N'H') AS emailformat,ISNULL(c.svalue,N'Y') AS emailmyself FROM #temp3 a LEFT OUTER JOIN (SELECT userid,svalue FROM gemini_usersettings WHERE sname=N'EmailFormat') b on b.userid=a.userid LEFT OUTER JOIN (SELECT userid,svalue FROM gemini_usersettings WHERE sname=N'EmailMyChanges') c on c.userid=a.userid end go
SELECT userid,watchcode into #temp2 FROM gemini_watchproject WHERE projectid=@projectid AND userid not in (SELECT userid FROM #temp1) AND userid not in (SELECT userid FROM gemini_usersettings WHERE sname=N'ReceiveEmailAlerts' AND svalue=N'N') SELECT a.userid,a.watchcode,b.emailaddress into #temp3 FROM #temp1 a, gemini_users b WHERE a.userid=b.userid UNION ALL SELECT a.userid,a.watchcode,b.emailaddress FROM #temp2 a, gemini_users b WHERE a.userid=b.userid SELECT a.userid,a.watchcode,a.emailaddress,ISNULL(b.svalue,N'H') AS emailformat,ISNULL(c.svalue,N'Y') AS emailmyself FROM #temp3 a LEFT OUTER JOIN (SELECT userid,svalue FROM gemini_usersettings WHERE sname=N'EmailFormat') b on b.userid=a.userid LEFT OUTER JOIN (SELECT userid,svalue FROM gemini_usersettings WHERE sname=N'EmailMyChanges') c on c.userid=a.userid end go
into #temp2 FROM gemini_watchproject WHERE projectid=@projectid AND userid not in (SELECT userid FROM #temp1) AND userid not in (SELECT userid FROM gemini_usersettings WHERE sname=N'ReceiveEmailAlerts' AND svalue=N'N') SELECT a.userid,a.watchcode,b.emailaddress into #temp3 FROM #temp1 a, gemini_users b WHERE a.userid=b.userid UNION ALL SELECT a.userid,a.watchcode,b.emailaddress FROM #temp2 a, gemini_users b WHERE a.userid=b.userid SELECT a.userid,a.watchcode,a.emailaddress,ISNULL(b.svalue,N'H') AS emailformat,ISNULL(c.svalue,N'Y') AS emailmyself FROM #temp3 a LEFT OUTER JOIN (SELECT userid,svalue FROM gemini_usersettings WHERE sname=N'EmailFormat') b on b.userid=a.userid LEFT OUTER JOIN (SELECT userid,svalue FROM gemini_usersettings WHERE sname=N'EmailMyChanges') c on c.userid=a.userid end go
FROM gemini_watchproject WHERE projectid=@projectid AND userid not in (SELECT userid FROM #temp1) AND userid not in (SELECT userid FROM gemini_usersettings WHERE sname=N'ReceiveEmailAlerts' AND svalue=N'N') SELECT a.userid,a.watchcode,b.emailaddress into #temp3 FROM #temp1 a, gemini_users b WHERE a.userid=b.userid UNION ALL SELECT a.userid,a.watchcode,b.emailaddress FROM #temp2 a, gemini_users b WHERE a.userid=b.userid SELECT a.userid,a.watchcode,a.emailaddress,ISNULL(b.svalue,N'H') AS emailformat,ISNULL(c.svalue,N'Y') AS emailmyself FROM #temp3 a LEFT OUTER JOIN (SELECT userid,svalue FROM gemini_usersettings WHERE sname=N'EmailFormat') b on b.userid=a.userid LEFT OUTER JOIN (SELECT userid,svalue FROM gemini_usersettings WHERE sname=N'EmailMyChanges') c on c.userid=a.userid end go
AND userid not in (SELECT userid FROM gemini_usersettings WHERE sname=N'ReceiveEmailAlerts' AND svalue=N'N') SELECT a.userid,a.watchcode,b.emailaddress into #temp3 FROM #temp1 a, gemini_users b WHERE a.userid=b.userid UNION ALL SELECT a.userid,a.watchcode,b.emailaddress FROM #temp2 a, gemini_users b WHERE a.userid=b.userid SELECT a.userid,a.watchcode,a.emailaddress,ISNULL(b.svalue,N'H') AS emailformat,ISNULL(c.svalue,N'Y') AS emailmyself FROM #temp3 a LEFT OUTER JOIN (SELECT userid,svalue FROM gemini_usersettings WHERE sname=N'EmailFormat') b on b.userid=a.userid LEFT OUTER JOIN (SELECT userid,svalue FROM gemini_usersettings WHERE sname=N'EmailMyChanges') c on c.userid=a.userid end go
SELECT a.userid,a.watchcode,b.emailaddress into #temp3 FROM #temp1 a, gemini_users b WHERE a.userid=b.userid UNION ALL SELECT a.userid,a.watchcode,b.emailaddress FROM #temp2 a, gemini_users b WHERE a.userid=b.userid SELECT a.userid,a.watchcode,a.emailaddress,ISNULL(b.svalue,N'H') AS emailformat,ISNULL(c.svalue,N'Y') AS emailmyself FROM #temp3 a LEFT OUTER JOIN (SELECT userid,svalue FROM gemini_usersettings WHERE sname=N'EmailFormat') b on b.userid=a.userid LEFT OUTER JOIN (SELECT userid,svalue FROM gemini_usersettings WHERE sname=N'EmailMyChanges') c on c.userid=a.userid end go
into #temp3 FROM #temp1 a, gemini_users b WHERE a.userid=b.userid UNION ALL SELECT a.userid,a.watchcode,b.emailaddress FROM #temp2 a, gemini_users b WHERE a.userid=b.userid SELECT a.userid,a.watchcode,a.emailaddress,ISNULL(b.svalue,N'H') AS emailformat,ISNULL(c.svalue,N'Y') AS emailmyself FROM #temp3 a LEFT OUTER JOIN (SELECT userid,svalue FROM gemini_usersettings WHERE sname=N'EmailFormat') b on b.userid=a.userid LEFT OUTER JOIN (SELECT userid,svalue FROM gemini_usersettings WHERE sname=N'EmailMyChanges') c on c.userid=a.userid end go
FROM #temp1 a, gemini_users b WHERE a.userid=b.userid UNION ALL SELECT a.userid,a.watchcode,b.emailaddress FROM #temp2 a, gemini_users b WHERE a.userid=b.userid SELECT a.userid,a.watchcode,a.emailaddress,ISNULL(b.svalue,N'H') AS emailformat,ISNULL(c.svalue,N'Y') AS emailmyself FROM #temp3 a LEFT OUTER JOIN (SELECT userid,svalue FROM gemini_usersettings WHERE sname=N'EmailFormat') b on b.userid=a.userid LEFT OUTER JOIN (SELECT userid,svalue FROM gemini_usersettings WHERE sname=N'EmailMyChanges') c on c.userid=a.userid end go
UNION ALL SELECT a.userid,a.watchcode,b.emailaddress FROM #temp2 a, gemini_users b WHERE a.userid=b.userid SELECT a.userid,a.watchcode,a.emailaddress,ISNULL(b.svalue,N'H') AS emailformat,ISNULL(c.svalue,N'Y') AS emailmyself FROM #temp3 a LEFT OUTER JOIN (SELECT userid,svalue FROM gemini_usersettings WHERE sname=N'EmailFormat') b on b.userid=a.userid LEFT OUTER JOIN (SELECT userid,svalue FROM gemini_usersettings WHERE sname=N'EmailMyChanges') c on c.userid=a.userid end go
SELECT a.userid,a.watchcode,b.emailaddress FROM #temp2 a, gemini_users b WHERE a.userid=b.userid SELECT a.userid,a.watchcode,a.emailaddress,ISNULL(b.svalue,N'H') AS emailformat,ISNULL(c.svalue,N'Y') AS emailmyself FROM #temp3 a LEFT OUTER JOIN (SELECT userid,svalue FROM gemini_usersettings WHERE sname=N'EmailFormat') b on b.userid=a.userid LEFT OUTER JOIN (SELECT userid,svalue FROM gemini_usersettings WHERE sname=N'EmailMyChanges') c on c.userid=a.userid end go
SELECT a.userid,a.watchcode,a.emailaddress,ISNULL(b.svalue,N'H') AS emailformat,ISNULL(c.svalue,N'Y') AS emailmyself FROM #temp3 a LEFT OUTER JOIN (SELECT userid,svalue FROM gemini_usersettings WHERE sname=N'EmailFormat') b on b.userid=a.userid LEFT OUTER JOIN (SELECT userid,svalue FROM gemini_usersettings WHERE sname=N'EmailMyChanges') c on c.userid=a.userid end go
FROM #temp3 a LEFT OUTER JOIN (SELECT userid,svalue FROM gemini_usersettings WHERE sname=N'EmailFormat') b on b.userid=a.userid LEFT OUTER JOIN (SELECT userid,svalue FROM gemini_usersettings WHERE sname=N'EmailMyChanges') c on c.userid=a.userid end go
LEFT OUTER JOIN (SELECT userid,svalue FROM gemini_usersettings WHERE sname=N'EmailFormat') b on b.userid=a.userid LEFT OUTER JOIN (SELECT userid,svalue FROM gemini_usersettings WHERE sname=N'EmailMyChanges') c on c.userid=a.userid end go
LEFT OUTER JOIN (SELECT userid,svalue FROM gemini_usersettings WHERE sname=N'EmailMyChanges') c on c.userid=a.userid end go
end