Requirement: I have to create an
automatic email notifier based on three functional conditions.this tool
has to run every one hr form 0600 to 1800
1) check if there are any
communication errors in the log file.
2) check if there are any unprocessed assignments
3) check the time
difference between the last assignment recieved and the current date
time and if the difference is more than 1 hr.
----if there are
no errors and no unprocessed assignments and time diff is more than 1 hr
an external mail has to be sent to the customers.
----if there are errors in comm logs or any unprocessed assignments left
then an internal mail has to be sent.
my questions:
Based
on the requirement which one is better , creating a windows service or
using sql job agent and schedule it for every one hr?
I felt that a windows service can do it but, we already have a
email notifier stored proc which is scheduled using sql jobs.
i
have already created a stored proc for the second and the third
requirement ( checkin for hte unprocessed and calculating the time
difference), but im unable to do the first one
the comm logs are generated by some tool called mercator, it
generates a txt file (name of file is G_20100319144219.txt) .
It
generates a new log file every 5 minutes.
but the servvice or the job
i have to do has to run every one hr.
so i need to pick the latest log text file and scan the string for
"<ExecutionSummary MapStatus="Valid" mapreturn="0"
ElapsedSec="0.0119" BurstRestartCount="0"> " this line
if the file has valid then no errors or else it has errors.
so
1)
how can i pick the most recent file? and scan if for the text?
2)
can i do it through a stored proc or sql ? if so i can schedule it in
jobs. or
3)Can i do it throug .BAT files? i found the code for bat file which
actually shows the most recent file. but i dont kno how to properly use
it?
"@rem NEWESTF.BAT -
@rem Whatever.bat -
@echo
off
cd C:\
setlocal enabledelayedexpansion
set d=00
set t=0
for
/f "tokens=*" %%a in ('dir /b /a-d /s') do (
set dt=%%~ta
set dtmp=!dt:~6,4!!dt:~3,2!!dt:~0,
2!
set
ttmp=!dt:~-5,2!!dt:~-2,2!
if !dtmp! GEQ !d! (
if !dtmp! GTR !d! (
set
d=!dtmp!
set t=!ttmp!
set l=%%a
) else (
if !ttmp! GTR !t! (
set d=!dtmp!
set
t=!ttmp!
set l=%%a
)
)
)
)
endlocal
& set lastfile=%l%
echo %lastfile%
echo.
pause
exit"
Thats
the bat file.u can execute it and see, it shows the recent file with
the path but i dont know how to use it? and this code works for the
first time and i feel it has a time lag, i dont kno wat it does as i
donot understand a bit of dos commands. if i modify a different file and
execut it, it takes some time.i mean initially it shows the recent one
but after any modifications it dosent, if i execute it after about an
hr, it again shows new one.
3) if i can know the name of the next file using the bat file above can i
insert the txt file into the some table in sql and scan it by using
some keyword 'LIKE' .
4) My lead wants me to do using sql jobs agent,
where i can use bat files or any stored procs, When i asked him, he
said try to do it in sql as thers already one process running scheduled
in sql agent, he doesnt prefer windows services, he if it cannot be done
then go with windows services and as far as i kno he is an expert in
sql, and i dont kno even little bit in sql, so i dont want him to know
that im a fresher.
And Yea the already existing process dosent check anything except
for the time difference a sql job is scheduled to run a stored proc
which checks for the time and sends a mail if the time diff is more than
some configurable time