May 1, 2012 at 8:47 pm
Hi We have SQL 2005 server and have the following code which quires the database for students that have been absent from school for three consecutive days and then emails the list of these students to the concerned people. The Query is as follows:
/****** Object: StoredProcedure [dbo].[primAbsentees] Script Date: 05/02/2012 12:31:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[primAbsentees]
As
declare @d datetime --input parameter for the procedure
set @d=(SELECT DATEADD(day, DATEDIFF(day, 0, getdate()), 0)) -- get current date
declare @StartDate datetime --first day of a period, has to be calculated and exclude Sundays/Saturdays
declare @EndDate datetime --last day of a period that is Being checked
set @EndDate =@d --or could be calculated.More likely that this would be set @EndDate=dateadd(d,-1,@d)
set @StartDate= case DATEPART(Dw,@EndDate) when 2 then DATEADD(d,-4,@EndDate)--@EndDate is Monday
when 3 then DATEADD(d,-4,@EndDate) --@EndDate is Tuesday
else DATEADD(d,-2,@enddate)
end
select s.stud_code, s.given_name,s.surname,s.year_grp,form_cls,s.sud7_flg AS Follow_up,x.Num_Absences
from Student s
inner join
(select stud_code,count(*) Num_Absences
from StudAbsent
where Absent_date between @StartDate and @EndDate
and absent_Type='AFS'
and cmpy_code='01'
group by stud_code
having count(*) >= 3
)
x on s.stud_code = x.stud_code where s.year_grp < = 6 ----and s.sud7_flg is null
Then I Scheduled the procedure to run every day at 11:00 with the following command
Execute PrimAbsentees
IF @@rowcount > 0
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Email',
@recipients = 'person@mail.net',
@execute_query_database='students',
@query = 'Execute primAbsentees',
@subject ='Absence Alert',
@attach_query_result_as_file = 1
What is happening lately is the staff members are receiving the list of absent students via email twice one at 11:00 AM and the second at 11:01 AM. As far as I am concerned we have not changed any setting and the schedule seems ok. The only change was is we virtualised our SQL server using VMware, I don't believe this has anything to do with the problem.
May 1, 2012 at 10:20 pm
Is the old server still running?
May 1, 2012 at 10:51 pm
No it is nol longer running.
Thanks
May 2, 2012 at 7:30 pm
I just want to thank you all. This issue was resolved. I restored the same database with a different name under the same instance about a month ago, this is just to have access to historical data. anyway the schedule was running the stored procedure in the active database and then running the same procedures in the restored database :hehe:
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply