I have a user who needs to check whether 2 jobs have run on a SQL Server
2000 instance, and be able to view logs.
Is there a way of doing this, and not allowing them access to anything
else?
--
JamesNZ
---
Posted via http://www.webservertalk.com
---
View this thread: http://www.webservertalk.com/message1430463.htmlHi
Does the use have EM on his/her workstation?
Does the user have permissions to run the job?
See this example written by Neil
/*** Scheduled task reporter for SQL 2000
**** Copyright Neil Boyle 2003.
**** Use entirely at your own risk
***/
use MSDB
go
select convert(varchar(22), j.name) as job_name
, case freq_type -- Daily, weekly, Monthly
when 1 then 'Once'
when 4 then 'Daily'
when 8 then 'Wk ' -- For weekly, add in the days of the week
+ case freq_interval & 2 when 2 then 'M' else '' end -- Monday
+ case freq_interval & 4 when 4 then 'Tu' else '' end -- Tuesday
+ case freq_interval & 8 when 8 then 'W' else '' end -- etc
+ case freq_interval & 16 when 16 then 'Th' else '' end
+ case freq_interval & 32 when 32 then 'F' else '' end
+ case freq_interval & 64 when 64 then 'Sa' else '' end
+ case freq_interval & 1 when 1 then 'Su' else '' end
when 16 then 'Mthly on day ' + convert(varchar(2), freq_interval) --
Monthly on a particular day
when 32 then 'Mthly ' -- The most complicated one, "every third Friday
of the month" for example
+ case freq_relative_interval
when 1 then 'Every First '
when 2 then 'Every Second '
when 4 then 'Every Third '
when 8 then 'Every Fourth '
when 16 then 'Every Last '
end
+ case freq_interval
when 1 then 'Sunday'
when 2 then 'Monday'
when 3 then 'Tuesday'
when 4 then 'Wednesday'
when 5 then 'Thursday'
when 6 then 'Friday'
when 7 then 'Saturday'
when 8 then 'Day'
when 9 then 'Week day'
when 10 then 'Weekend day'
end
when 64 then 'Startup' -- When SQL Server starts
when 128 then 'Idle' -- Whenever SQL Server gets bored
else 'Err' -- This should never happen
end as schedule
, case freq_subday_type -- FOr when a job funs every few seconds, minutes
or hours
when 1 then 'Runs once at:'
when 2 then 'every ' + convert(varchar(3), freq_subday_interval) + '
seconds'
when 4 then 'every ' + convert(varchar(3), freq_subday_interval) + '
minutes'
when 8 then 'every ' + convert(varchar(3), freq_subday_interval) + '
hours'
end as frequency
-- All the subsrings are because the times are stored as an integer with no
leading zeroes
-- i.e. 0 means midnight, 13000 means half past one in the morning
(01:30:00)
, substring (right (stuff (' ', 1, 1, '000000') +
convert(varchar(6),active_start_time), 6), 1, 2)
+ ':'
+ substring (
right (stuff (' ', 1, 1, '000000') + convert(varchar(6),
active_start_time), 6) ,3 ,2)
+ ':'
+ substring (
right (stuff (' ', 1, 1, '000000') + convert(varchar(6),active_start_time),
6) ,5 ,2) as start_at
,case freq_subday_type
when 1 then NULL -- Ignore the end time if not a recurring job
else substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6),
active_end_time), 6), 1, 2)
+ ':'
+ substring (
right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_end_time),
6) ,3 ,2)
+ ':'
+ substring (
right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_end_time),
6) ,5 ,2) end as end_at
from sysjobs j
join sysJobSchedules s
on j.job_id = s.job_id
order by j.name, start_at
"JamesNZ" <JamesNZ.24lo0y@.mail.webservertalk.com> wrote in message
news:JamesNZ.24lo0y@.mail.webservertalk.com...
> I have a user who needs to check whether 2 jobs have run on a SQL Server
> 2000 instance, and be able to view logs.
> Is there a way of doing this, and not allowing them access to anything
> else?
>
> --
> JamesNZ
> ---
> Posted via http://www.webservertalk.com
> ---
> View this thread: http://www.webservertalk.com/message1430463.html
>|||Here is one solution which should work for you:
1. Create a SP using the following script. Just mention your job names in
the following line of the script:
--sj.name in ('Job1', 'Job2') and
CREATE PROCEDURE dbo.read_jobhistory
as
select sjh.server, sj.name, case sjh.run_status
when 0 then 'Failed'
when 1 then 'Succeeded'
when 2 then 'Retry'
when 3 then 'Canceled'
when 4 then 'In progress'
end as 'Job Status',
sjh.sql_message_id, sjh.sql_severity,sjh.message,
convert(datetime, cast (sjh.run_date as char(8)) + ' ' +
left(right('000000' + cast (sjh.run_time as varchar(6)),6),2) + ':' +
substring(right('000000' + cast (sjh.run_time as varchar(6)),6),3,2) + ':' +
right('000000' + cast (sjh.run_time as varchar(6)),2))as 'Run Date',
sjh.run_duration, sjh.operator_id_emailed,
sjh.operator_id_netsent, sjh.operator_id_paged, sjh.retries_attempted
from
msdb..sysjobhistory sjh
join msdb..sysjobs sj
on sjh.job_id = sj.job_id
where
sj.name in ('Job1', 'Job2') and
step_id = 0
and sjh.run_date >= convert(char(10),getdate(),112)
order by sjh.server, sj.name, sjh.run_date, sjh.run_time
2. Grant Execution on this SP to the concerned user.
3. The concerned user can get the report for the today's date just by
executing the SP.
"JamesNZ" wrote:
> I have a user who needs to check whether 2 jobs have run on a SQL Server
> 2000 instance, and be able to view logs.
> Is there a way of doing this, and not allowing them access to anything
> else?
