SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Sp using command shell to start/stop services
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

phrankbooth
Posting Yak Master

USA
158 Posts

Posted - 12/09/2012 :  14:27:44  Show Profile  Reply with Quote
I've written the sp below to start/stop windows services.

Can it be optimized any further and is the use of cmshell OK for this?


alter procedure spServiceController @Server varchar(250), @Command varchar(250), @Service varchar(250)
as
SET NOCOUNT ON

CREATE table #result(data varchar(100))


-- MSSQLServerOLAPService
declare 
@SCCmd VARCHAR(1000) = ''' sc \\' + @Server + ' ' + @Command + ' ' + @Service + '''',
@State varchar(250) = CASE WHEN @Command = 'start' then 'RUNNING'
						   WHEN @Command = 'stop' then 'STOPPED'
					  END 

EXEC ('exec master..xp_cmdshell ' + @SCCmd + ', ''no_output''' )

--Get Service Status
DECLARE @STATUS VARCHAR(100) = 'OTHER'
DECLARE @TimeOutInit datetime = current_timestamp

SET @SCCmd = ''' sc \\' + @Server + ' query ' + @Service + ' |find "STATE"'''

WHILE @STATUS <> @State AND DATEDIFF(SECOND, @TimeOutInit, current_timestamp) <= 120
BEGIN
	
	EXEC ('INSERT INTO #result exec master..xp_cmdshell ' + @SCCmd)
	
	SELECT TOP 1 @STATUS = CASE WHEN data like '%'+ @State +'%' THEN @State
					       ELSE @STATUS
					       END
	FROM #result WHERE data IS NOT NULL AND data like '%'+ @State +'%'
				   
END

SELECT 'SERVICE: ' + @Service + ' ON SERVER: ' + @Server + ' IS ' + @STATUS



--PhB
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000