Re: SQL Server Agent thro' SP

From: Hari (hari_prasad_k_at_hotmail.com)
Date: 07/01/04 

Date: Thu, 1 Jul 2004 16:27:29 +0530

Hi Shweta,

The below was the procedure mentioned by Vyas. This procedure will check the
status of SQLAgent first and
insert the status to a temptable (#sqlstatus). If the status returned is
stopped then this procedure will
fire the start service to start the SQL Agent service.
the last line sp_procoption 'sp_autostartagent', 'startup', 'true' will set
the below procedure in startup. This will start the sql agent when sql
server starts.

----- procedure
use master
go

create proc sp_autostartagent
as
create table #sqlstatus(status nvarchar(20))
insert into #sqlstatus (status) exec master.dbo.xp_servicecontrol
'QUERYSTATE', 'SQLServerAgent'
if exists (select * from #sqlstatus where status='Stopped.')
    exec xp_servicecontrol 'START', 'SQLServerAgent'
drop table #sqlstatus
go
exec sp_procoption 'sp_autostartagent', 'startup', 'true' 

--
Thanks
Hari
MCDBA
"Shweta" <Shweta@discussions.microsoft.com> wrote in message
news:177149C4-C3B2-4BCE-8537-457A18AD542E@microsoft.com...
> Thanks a lot, Narayana for your prompt reply.
> I saw this XP in Master DB, but could not get any documentation @ it.
Actually there are a few more undocumented extended stored procedures
realted to Agent. Anyways, your post is a great help to me.
>
> Thanks again,
> Shweta
>
> "Narayana Vyas Kondreddi" wrote:
>
> > You can use master.dbo.xp_servicecontrol, to query the state of SQLAgent
and
> > to start and stop it. For example:
> >
http://groups.google.com/groups?selm=udhB6NMKAHA.215%40cppssbbsa02.microsoft
.com
> >
> > Note that this is undocumented.
> > --
> > HTH,
> > Vyas, MVP (SQL Server)
> > http://vyaskn.tripod.com/
> > Is .NET important for a database professional?
> > http://vyaskn.tripod.com/poll.htm
> >
> >
> > "Shweta" <Shweta@discussions.microsoft.com> wrote in message
> > news:E50CE3AD-83EA-4B1A-917B-55A12EA61B72@microsoft.com...
> > Hi there,
> >
> > I have a requirement in which I need to check if SQL Server Agent
Service is
> > running. If it is not running, then I need to start the service using
stored
> > procedure. Since we have a SQL Server 2000, I can not use SQL-SCM API.
> >
> > The other option is to use Command line utility - sqlagent. But, it is
> > independent of the Windows NT Service Control Manager & BOL says that
use
> > 'sqlagent' application for SQL Agent diagnosis purpose. So this option
is
> > also out.
> >
> > My last option is to use SQL DMO, create a application & use it. But
before
> > this I would like to know if there is any way to achieve this thro' SQL
> > Server 2000 using SPs etc.
> >
> > Any help in this matter is welcome.
> >
> > Thanks in advance
> > -Shweta
> >
> >
> >
> >

Relevant Pages

  • Re: Job owned by a non-sysadmin fails to run
    ... Have you restarted the server since you added the sqlservice account to ... are there any related messages in the SQL Agent log files? ... SQL Server service and SQL Server Agent service run under the same ...
    (microsoft.public.sqlserver.security)
  • Re: Error running DTS package on a schedule (only!)
    ... To determine wich account was used by SQL Agent: ... |> | When I try to schedule a job for this DTS package under SQL Server ...
    (microsoft.public.sqlserver.dts)
  • Re: Scheduled Backup for SQL Database
    ... Only problem is when the SQL server ... it doesn't restart the agent. ... For some reason the SQL agent under server management ... Look in the SQL Server ...
    (microsoft.public.sqlserver.server)
  • Re: Adding 2nd node fails
    ... When you say that you're using the local admin, ... using it for your SQL Server and SQL Agent services? ...
    (microsoft.public.sqlserver.clustering)
  • Re: SQL Server Agent thro SP
    ... Vyas, MVP (SQL Server) ... I have a requirement in which I need to check if SQL Server Agent Service is ... The other option is to use Command line utility - sqlagent. ...
    (microsoft.public.sqlserver.server)