Re: SQL 2000 view current job status best practice

Your asking for something that isn't doable in SQL Server
2000. The functionality in Enterprise Manager is built-in
and you can't really change that. The only other unsupported
way to do this would be to add the users to the TargetServer
role in msdb. Keep in mind that the role is undocumented
and using this is unsupported.

-Sue

On Wed, 11 Oct 2006 14:03:02 -0700, Richard Cote
<RichardCote@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

Sorry, I should have clarified this at the beginning. I am looking for a
solution to make the job status viewable from within SQL Enterprise Manager.


"Warren Brunk" wrote:

There are a thousand different ways to implement this but here are the
basics.

I would create a stored procedure with something like this...
Create Procedure usp_runningjobs_s
as
begin
SELECT
sysp.cpu ,
sysp.spid,
sysp.status,
sysp.hostname,
sysp.program_name,
sysp.cmd,
sysp.cpu,
sysp.physical_io,
convert(sysname, rtrim(sysp.loginame)) as loginname,
sysp.spid as 'spid_sort',
substring( convert(varchar,sysp.last_batch,111) ,6 ,5 ) + ' ' +
substring( convert(varchar,sysp.last_batch,113) ,13 ,8 ) as
'last_batch_char'
from master.dbo.sysprocesses sysp (nolock)
inner join master.dbo.sysdatabases sysd (nolock)
on sysp.dbid = sysd.dbid
end


Then what you could do is grant permissions to execute the procedure to the
users that you need. Or, even better is create a little .net webpage that
calls this procedure and returns the results to a gridview.

What do you think?



--
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/


"Richard Cote" <RichardCote@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F4DCBDD9-7A0F-4F7E-8F21-868AEF7BCCFF@xxxxxxxxxxxxxxxx
We have application support personnel who need to view the current status
of
jobs running on production SQL 2000 (SP4) Servers. The application
support
group has very limited access to these production servers - such that they
do
not have enough permissions to execute these jobs. Therefore, changing
the
job owner is not a viable option. What is the best way to:

- allow the support personnel to view the status of jobs
- not increasing the account permissions within the databases





.

Relevant Pages

  • Zurücksichern einer SQL Server Datenbankdatei auf einen anderen Server
    ... Ich habe mit dem Enterprise Manager eine Sicherung auf meinem SQL ... Servers ist eine andere, ... Next by Date: ...
    (microsoft.public.de.sqlserver)
  • Re: how to remove scheduled backup tasks
    ... You need to refresh the screen in Enterprise manager for them to show up. ... Either close and re-open EM or right click on the Jobs folder and choose ... > still learning how to administer a SQL db using the SQL ... > created 2 backup jobs to backup the db at different times ...
    (microsoft.public.sqlserver.security)
  • .Working with jobs when Agent is stopped.
    ... This could be done in SQL ... 2000 Enterprise manager but does not appear to be available in Management ... I don't want to start the Agent as this will run the jobs. ...
    (microsoft.public.sqlserver.tools)
  • Backup Maintinance
    ... Im trying to set up a Backup Maintiance plan for one of our SQL 2000 Servers. ... Using Enterprise Manager i create the backup job, but when trying to run the ...
    (microsoft.public.sqlserver.programming)
  • Re: sql 2005 active/passive cluster options
    ... that is storing data on a seperate SQL 2005 Standard box. ... SQL server had an hardware fault causing application downtime for ... their product is an active/passive sql cluster. ... terms of active/passive failover I need two identical servers (since ...
    (microsoft.public.sqlserver.clustering)