Check services at startup of SQL Server

I am trying to check the state of services when SQL Server is started. I am using xp_cmdshell and 'sc query SQLServerAgent | FIND "STATE"' for example to load the output to a global temp table. It works when SQL Server has already started but does not work when the proc is set t开发者_Go百科o autoexec, via sp_procoption.

---

Maybe this has someting to do with it: "Startup procedures must be in the master database and cannot contain INPUT or OUTPUT parameters. Execution of the stored procedures starts when the master database is recovered at startup." http://technet.microsoft.com/en-us/library/ms181720.aspx

---

You could try EXEC master.dbo.xp_servicecontrol 'QueryState', 'SQLSERVERAGENT' instead

I used to use this back on SQL Server 2000 and it's still there in 2005

There is the possibility that SQL Agent is not running when the stored proc runs because it is dependent. Or tempdb is not ready.