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
If you face this error, you can enable this feature By following piece of script:
Now if you run the previous code, it will start your services.
Hope it helps!
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 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
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.
To know more about flawless internet education technology just give click hurry up!
ReplyDeleteThank you
Hi how to Check the Status of Windows service in remote machine.
ReplyDeleteThis was a great tutorial to get me started, thank you!
ReplyDeleteMy need was to stop / start a service that will not always have the same name but will always have the same start of the name. Mine was as follows:
STOPPING A SERVICE
------------------
-- Declare all variables for use
declare @CmdLine varchar(1024)
declare @Command1 varchar(100)
declare @Command2 varchar(100)
declare @RetInfo varchar(8000)
declare @TableList table(list varchar(100))
declare @ServiceName varchar(100)
-- Prime the variable @Command1 with a list of running services
select @Command1 = 'net start'
print @Command1
-- Prime the variable @Command2 with the NET STOP command
select @Command2 = 'net stop '
print @Command2
-- Create a table that contains a list of all running services
insert into @TableList
exec @RetInfo = master.dbo.xp_cmdshell @Command1
-- Select the exact Agresso service from the list provided. Note that the Agresso
-- service name will change between organisations and therefore this makes the script
-- more generic. It will not work however if multiple services are running on the
-- same machine i.e. Agresso 5.4 and multi-environment instances.
select @ServiceName = LTRIM(list) from @TableList where list like ('%SERVICE_NAME%')
select @ServiceName
-- Build up the NET STOP command so that it can stop the Agresso services properly.
select @Command2 = @Command2 + '"' + @ServiceName + '"'
exec @RetInfo = master.dbo.xp_cmdshell @Command2
select @Command2
STARTING A SERVICE
------------------
Note that this is much harder as you cant simply use NET STOP to list all stopped services.
-- Declare all variables for use
declare @CmdLine varchar(1024)
declare @Command1 varchar(100)
declare @Command2 varchar(100)
declare @Command3 varchar(100)
declare @RetInfo varchar(8000)
declare @TableList table(list varchar(100))
declare @ServiceName varchar(100)
-- Prime the variable @Command1 with a list of running services
select @Command1 = 'sc query type= service state= inactive'
print @Command1
-- Prime
select @Command3 = 'net start '
print @Command3
-- Create a table that contains a list of all running services
insert into @TableList
exec @RetInfo = master.dbo.xp_cmdshell @Command1
select * from @TableList
-- Select the exact Agresso service from the list provided. Note that the Agresso
-- service name will change between organisations and therefore this makes the script
-- more generic. It will not work however if multiple services are running on the
-- same machine i.e. Agresso 5.4 and multi-environment instances.
select @ServiceName = LTRIM(REPLACE(list,'DISPLAY_NAME:','')) from @TableList where list like ('%SERVICE_NAME%')
select @ServiceName
-- Build up the NET STOP command so that it can stop the Agresso services properly.
select @Command3 = @Command3 + '"' + @ServiceName + '"'
exec @RetInfo = master.dbo.xp_cmdshell @Command3
select @Command3
To karthikeyan, you can check the status of a running service by the following syntax:
sc query type= service state= active