October 1, 2012 at 7:24 am
Hello every one
I have one job schedule everyday since Last 2 years.
everyday it runs fine but yesterday its failed, below is the error lob
[SQLSTATE 01000] (Message 0) Processing School: 136 [SQLSTATE 01000] (Message 0) Processing School: 137 [SQLSTATE 01000] (Message 0) Processing School: 140 [SQLSTATE 01000] (Message 0) Processing School: 141 [SQLSTATE 01000] (Message 0) Processing School: 142 [SQLSTATE 01000] (Message 0) Processing School: 147 [SQLSTATE 01000] (Message 0) Processing School: 148 [SQLSTATE 01000] (Message 0) Processing School: 149 [SQLSTATE 01000] (Message 0) Processing School: 151 [SQLSTATE 01000] (Mess... The step failed.
I have no idea what i need to Fix. Please help me to Resolve this
Thanks
October 1, 2012 at 7:50 am
Find out what the job runs and debug it, could be SSIS packages, stored procs etc.
October 1, 2012 at 7:54 am
Does the job create an output file with more information?
October 1, 2012 at 8:12 am
anthony.green (10/1/2012)
Find out what the job runs and debug it, could be SSIS packages, stored procs etc.
Yes, The Job running with Stored Proc
Here is that sql command in that job.
Please help me to troubleshoot
-- MK 02/22/10 Changed to run missing membership months, also runs attendance for the same months
declare @iSchoolYearCode int,
@iMonth int,
@iRerun int, --0=new
@DateMonth int
select @DateMonth = month(dateadd(d,-1,getdate())) -- month code for yesterday
select @iSchoolYearCode = iCurrentSchoolYearCode from tblSysVariables
set @iRerun = 1
-- get the list of months with no membership data
declare cCursor cursor for
select iMonthCode
from tblMembershipDates d with (nolock)
left join tblMembership m with (nolock)
on d.iSchoolYearCode = m.iSchoolYearCode
and d.iMonthCode = m.iMonth
where d.iSchoolYearCode = @iSchoolYearCode
and d.dtEndDate < cast(floor(cast(getdate() as float)) as datetime) -- end date is before today
and m.iMonth is null -- no membership data
order by dtEndDate
-- run membership and attendance for each month in the list
open cCursor
fetch next from cCursor into @iMonth
while (@@fetch_status = 0)
begin
exec uspReports_RunMembership @iSchoolYearCode, @iMonth, @iRerun
exec uspReports_RunAttendanceAYP @iSchoolYearCode
-- attendance; don't run for 99 and don't run for current month
if @iMonth != 99 and @iMonth != month(getdate())
begin
exec uspReports_RunAttendanceMonthly @iSchoolYearCode, @iMonth, 0
end
fetch next from cCursor into @iMonth
end
close cCursor
deallocate cCursor
-- MK 02/22/10 this is left in because the membership dates are mid-month
--if yesterday was the last day of the month, the monthly attendance will run for last month
if (month(getdate()) != @DateMonth)
begin
exec uspReports_RunAttendanceMonthly @iSchoolYearCode, @DateMonth, 0
end
October 1, 2012 at 8:14 am
Without data it is hard to trouble shoot, run the procedure with the parameters that where passed in and it should error at the same point, and then you can debug the problems with that particulat subset of data.
October 1, 2012 at 8:41 am
anthony.green (10/1/2012)
Without data it is hard to trouble shoot, run the procedure with the parameters that where passed in and it should error at the same point, and then you can debug the problems with that particulat subset of data.
OK, Thanks for Your Help.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply