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 to autoexec, via sp_procoption.
+1
A:
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
unclepaul84
2010-05-24 16:18:07
the start up proc issues an INSERT INTO...EXEC xp_cmdshell to populate the global temp table because you can not use INPUT or OUTPUT parameters. When the startup proc goes to read the temp table, there is nothing there from the INSERT.
SQL DBA
2010-05-24 17:38:34
A:
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.
gbn
2010-05-24 16:32:12
I tried using this extended proc to populate the global temp table that was created at startup and it didn't work either.
SQL DBA
2010-05-24 17:33:07