>
>thanks for your answer. I was hoping there was a more simple way of doing it ... I need it because we have msde running in about 130 shops and I need to start the server agent in each shop.

Hi Hans,

It looks complicated because of combersome OLE interface and error handling in T-SQL. It's easier to see what it's doing by temporary removing error handling code.

DECLARE @hr int, @source varchar(255), @description varchar(255)
DECLARE @RetCode int, @oServer int, @oAgent int, @Status int, @Err int
EXECUTE @RetCode = sp_OACreate 'SQLDMO.SQLServer', @oServer OUTPUT
PRINT 'Use Windows login'
EXECUTE @RetCode = sp_OASetProperty @oServer, 'LoginSecure', 1
PRINT 'Connect'
EXECUTE @RetCode = sp_OAMethod @oServer, 'Connect'
PRINT 'Get Agent object'
EXECUTE @RetCode = sp_OAGetProperty @oServer, 'JobServer', @oAgent OUTPUT
PRINT 'Get Agent Status and Stop/Start it based on the current status'
EXECUTE @RetCode = sp_OAGetProperty @oAgent, 'Status', @Status OUTPUT
IF @Status = 1 BEGIN
	PRINT 'Agent is running, Stop it'
	EXECUTE @RetCode = sp_OAMethod @oAgent, 'Stop'
END	
ELSE 
IF @Status = 3 BEGIN
	PRINT 'Agent is stopped, Start it'
	EXECUTE @RetCode = sp_OAMethod @oAgent, 'Start'
END	
-- Cleanup
	IF @oServer IS NOT NULL
		EXECUTE @RetCode = sp_OADestroy @oServer
	IF @oAgent IS NOT NULL
		EXECUTE @RetCode = sp_OADestroy @oAgent
RETURN