Search this blog

Wednesday, June 2, 2010

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!

3 comments:

  1. To know more about flawless internet education technology just give click hurry up!


    Thank you

    ReplyDelete
  2. Hi how to Check the Status of Windows service in remote machine.

    ReplyDelete
  3. This was a great tutorial to get me started, thank you!

    My 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

    ReplyDelete