How To Stop Or Start Windows Service Thru SQL Script

This Article, helps to start a windows services thru SQL Script. To control the windows services, we can use SQL Server’s Extended Stored Procedure, xp_cmdshell

Through command prompt, we can control the services by using MS-DOS Commands

NET START - Start the specified network or local service.
NET STOP - Stop the specified network or local service.

To Execute MS-DOS Commands, we can use the Extended SP: xp_cmdshell

The following piece of script will helps to stop/Start a windows service. In this code, I planned to start or stop the Services of SQL Server Analysis Services

declare @CmdLine varchar(1024)
declare @Command varchar(100)
declare @RetInfo varchar(8000)
select @Command = 'net start MSSQLServerOLAPService'
print @Command
exec @RetInfo = master.dbo.xp_cmdshell @Command
print @RetInfo

When you Execute this code, you may face the following error like

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.

For security reason, SQL Server blocked access to the Procedure xp_cmdshell


If you face this error, you can enable this feature By following piece of script:

-- To allow advanced options to be changed.

EXEC sp_configure 'show advanced options', 1

GO

-- To update the currently configured value for advanced options.

RECONFIGURE

GO

-- To enable the feature.

EXEC sp_configure 'xp_cmdshell', 1

GO

-- To update the currently configured value for this feature.

RECONFIGURE

GO

Now if you run the previous code, it will start your services.

Hope it helps!