December 15, 2009 at 11:10 am
I'd like an autoproc routine to start up the Agent in case SQL Server was stopped and started. I cannot use xp_cmdshell as we're not allowing this per auditors. I've seen some permissions issues/questions from people if you try to use
EXEC xp_servicecontrol N'START',N'SQLServerAGENT'
What is a good method to get the Agent started as part of an autoproc?
TIA,
Doug
December 15, 2009 at 11:30 am
Right click on the SQL Server Agent and choose properties. Choose the option as shown in the attachment. (See the attachment.)
Does that help?
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 15, 2009 at 12:15 pm
Bru,
No. If you stop and start the sql service, the system doesn't treat that as an unexpected stop so those settings do not take effect.
Doug
December 17, 2009 at 3:39 pm
This is what I use, beleieve I got it either here or another forum site:
USE [master]
GO
/****** Object: StoredProcedure [dbo].[AutoStart_SQLAgent] Script Date: 12/17/2009 15:37:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[AutoStart_SQLAgent]
AS
BEGIN
DECLARE @Err int, @Msg varchar(100), @ServiceName sysname
SET @ServiceName = 'SQLServerAgent'
EXEC master.dbo.xp_servicecontrol 'START', @ServiceName
SET @Err = @@ERROR
IF @Err = 0
BEGIN
RAISERROR ('Successfully started SQL Server Agent', 1, 1) WITH LOG
END
ELSE
BEGIN
SET @Msg = 'Error occured while starting SQL Server Agent. Error code: ' + STR(@Err)
RAISERROR (@Msg, 18, 1) WITH LOG
END
END
exec sp_procoption
@ProcName = 'AutoStart_SQLAgent',
@OptionName = 'startup',
@OptionValue = 'on'
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply