.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Is there a way to start SQL Agent from Transact-SQL or SSMS?

Posted By:      Posted Date: October 19, 2010    Points: 0   Category :Sql Server

Using SQL Server 2008 is there a way to start the SQL Agent service using T-SQL? I don't have remote desktop permissions to that server or I would try to start it from there. While connected to the server in SSMS, in object explorer, if I right-click on the The SQL Server Agent label I see the start and stop options but they are greyed-out. Why?

precise version: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)   Mar 29 2009 10:11:52   Copyright (c) 1988-2008 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6001: Service Pack 1)

View Complete Post

More Related Resource Links

SQL Server 2008 Agent Fails to start in a Win 2008 Cluster

When I try to bring SQLAGENT Online, I get the following errors: EventID:53 [sqagtres] StartResourceService: Failed to start SQLSERVERAGENT service.  CurrentState: 1 [sqagtres] OnlineThread: ResUtilsStartResourceService failed (status 435) [sqagtres] OnlineThread: Error 435 bringing resource online.   I just did a fresh SQL Cluster install as well.  When I installed the cluster, the service account was good to go.  I cant figure out what is going on.  SQL Server Engine starts fine.  Any ideas?

User proc must start Agent job -- how?

My app must allow users to start a SQL Agent job to do a background operation.  I found a discussion on this and attempted to implement the last solution therein:
use [MyDB];

create PROCEDURE dbo.spStartAgentJob
	@pvJobName	nvarchar(100)
	WITH EXECUTE AS 'agentProxy'
EXEC msdb.dbo.sp_start_job @pvJobName

create certificate agentProxy 
    ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'
    with subject = 'agentProxy'
   , expiry_date='01/01/2100';

      BY CERTIFICATE [agentProxy]
        WITH PASSWORD = 'pGFD4bb925DGvbd2439587y';

alter certificate [agentProxy] 
  remove private key;

backup certificate [agentProxy] 
 to file='c:\temp\agentProxy.cer';

use msdb

create certificate [agentProxy] 
  from file='c:\temp\agentProxy.cer';

create user [agentProxyAuthenticator] 
 from certificate [agentProxy];

grant authenticate to [agentProxyAuthenticator];
grant execute on msdb.dbo.sp_start_job to [agentProxyAuthenticator];

use [MyDB];

exec dbo.spStartAgentJob N'test'
The EXECUTE permission was denied on the object 'sp_start_job', database 'msdb', schema 'dbo'.

Did I miss something?  What is the correct way to accompli

Cannot start SQL Agent (2008)

I have a server with SQL Express 2008 running on it. 

SQL Agent service appears to be disabled, but when I try and start it I get the following error:

the sql server agent (SQLEXPRESS) service on local computer started and the stopped. Some services stop automatically if they have no work to do for example the Performance Logs and Alerts Service.

Anyone know whats causing this?

Couldn't start the distributor agent


Initially i had replication and then i have deleted the replication.When i redo the replication ,distributor agent is not starting.i got following error

SQLServerAgent Error: Request to run job FED-DB1-XXXX-XXXX_Pulbi-FED-DB2-19(From User sa) refused because the job is already running from a request by user sa.
Changed database context to 'XXXXXX'.(Microsoft SQL Server,Error:22022)'.

I have select the distributor agent in same publisher server.Please help me out of this.

SSMS 2005 on Win XP won't start, no errors


Some time in the last couple weeks, SQL Server Management Studio 2005 (NOT Express) stopped working on my Windows XP SP3 system. I made no changes that I'm aware of. When I open the program, it gives me a working in the background cursor for a couple seconds and then nothing happens. Same thing if I change to the directory and try to start SQLWB.EXE. The cursor changes for a couple seconds like it's going to start and then nothing. There's no errors in the event log and no error message or dialog box.

I don't remember how I installed it since it's been a couple years already. I think it was from the standard SQL Server 2005 install but the location of the files may have changed. As typical for SQL Server, any attempt to uninstall without having the exact conditions and files it was installed with is a total nightmare and it fails.



Sqlserver Agent not able to start the JOB.


I am having one pakage which execute one DOS batch file to operate a FTP task(Pulling files).

Its working fine with SSIS.

When Scheduling with SQLSERVER Agent Jobs its not working and status showing as "in progress". tried with different user IDs like "sa", Local User Account etc.


Cannot start SQL Server Agent service on Cluster , 2008 R2



I'm testing a two node cluster installation on windows 2008 r2 and sql server 2008 r2 both enterprise before a production deployment and my only issue so far is that the SQl Server Agent service cannot be started.

The failover works fine, the cluster and SQL services changes nodes without any problem but on both nodes i cannot start that service.

I'm using a domain user for the service, member of the local admin and sysadmin on the SQL server itself.

The error i get when i click on start from SQL management console is:

Unable to start service SQLAGENT$SQLTEST on server SERVERNAME. (mscorlib)

Program Location:

Server stack trace: 
 at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.Service.Start()
 at System.Runtime.Remoting.Messaging.StackBuilderSink._PrivateProcessMessage(IntPtr md, Object[] args,
 Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs) at System.Runtime.Remoting.Messaging.StackBuilderSink.PrivateProcessMessage(RuntimeMethodHandle md,
 Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs) at System.Runtime.Remoting.Messaging.StackBuilderSink.AsyncProcessMessage(IMessage msg, IMessageSink
replySink) Exception rethrown at [0]: at System.Runtime.Remoting.Proxies.R

Unable to start sql server Agent




I am not able to start the Sql server Agent, when I am trying to start it is displaying the error msg "The Dependent services has not started"


Please help me in this issue

Unable to enum ManagedComputer.Services & Stop/Start SQL Agent service from ASP.NET app

Hi guys

I have some code that stops/starts the SQL Server Agent service from within our ASP.NET web application.  Runs fine in development but doesn't in release - I suspect due to rights: Dev has admin rights, whereas Prod is running under a IIS application pool identity which is a domain account (e.g. IUSR_WEBAPP).

In Production it doesn't return any ManagedComputer.Services - ManagedComputer.Services.Count = 0. It doesn't throw any exceptions or anything, just not return any services or allow me to access the specific SQL Agent instance that I want to.

I've tried granting access in various places including WMI but nothing seems to work.  I cannot figure out what rights might be needed.

How can I grant access to my application pool account so that it can enumerate services and stop/start my specific SQL Agent account instance for 2005/2008?

Many thanks

Transact-SQL Built-In Stored Procedures

To assist you with managing Microsoft SQL Server databases, Transact-SQL provides many built-in stored procedures.

Renaming an Object

Sending Email

SharePoint 2007 Workflows - Things to Consider Before You Start

SharePoint 2007 (MOSS) utilizes the Workflow Foundation (WF) to enable workflows on lists and content types. But before you start doing cartwheels there are few things you should know before you get started. The experiences I'll be discussing are not from book smarts but from building real MOSS solutions that are in production today.


Defines the attributes of a Transact-SQL server cursor, such as its scrolling behavior and the query used to build the result set on which the cursor operates. DECLARE CURSOR accepts both a syntax based on the ISO standard and a syntax using a set of Transact-SQL extensions.

Setting the default start page in godaddy hosting?


 How do you set the start page with godaddy hosting? I can see no way of doing this! You do not seem to have access to the IIS settings that allow you to change it!

how to start ironpython in asp.net


i have installed asp.net 3.5. and i have installed IronPython2.6

both wee installed successfull.but when i m creating new web site in the field od language i m not getting ironpython as shown in  


please help me.


Editor's Note: new Thread(ReadEditorsNote).Start(); yourAttention.WaitOne();


Introducing the issue, Howard Dierking points out that you can't simply parallelize your code blindly if you expect to truly reap the benefits that parallelism promises.

Howard Dierking

MSDN Magazine October 2008

Bootstrapper: Use the Visual Studio 2005 Bootstrapper to Kick-Start Your Installation


There are plenty of times when you need to get information on running processes, not the least of which is during performance tuning. Using the techniques in this article and special .NET classes you'll see how to get a process' ID, name, priority, number of threads, kernel handle, and memory consumption, as well as its user-mode, kernel-mode, and total elapsed running time and put them to use in a custom app called AssemblyBrowser.

Sean Draine

MSDN Magazine October 2004

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend