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'
exec @RetInfo = master.dbo.xp_cmdshell @Command
When you Execute this code, you may face the following error like
Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1For security reason, SQL Server blocked access to the Procedure xp_cmdshell
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.
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
-- To update the currently configured value for advanced options.
-- To enable the feature.EXEC sp_configure 'xp_cmdshell', 1
-- To update the currently configured value for this feature.
Now if you run the previous code, it will start your services.
Hope it helps!