none
Microsoft.SqlServer.Management.Smo.Wmi Server.StartMode

    Pertanyaan

  • I have an app that needs to change a SQL Server configuration.  We have SQL disabled on a computer and my code needs to set it to automatic, start it and then restore a database.  The code looks easy enough but, it does not work.  I don't see a lot of examples and the help file is bare.  Here is the code:

            MyManagedComputer = New ManagedComputer()
            oService = MyManagedComputer.Services("SQLSERVERAGENT")
            If (oService.StartMode <> ServiceStartMode.Auto) Then
                oService.StartMode = ServiceStartMode.Auto
                oService.Refresh()
                While oService.StartMode <> ServiceStartMode.Auto
                    oService.Refresh()
                    Debug.Print("Waiting on SQL Server startup mode to change...")
                End While
            End If
    
    Very simple (assuming you have SMO objects installed).  Yet, it does not change the mode.


    Steve

    21 Februari 2012 20:17

Jawaban

  •  Hi Steve,

    To change the start mode and start a SQL Server service on a SQL Server instance, it is required to specify the instance name, service type and service name. Please pay attention to the follow code which is used to change the start mode to Automatic for SQL Agent service of the default instance ‘MSSQLSERVER’, and then start the service.

     'Declare and create an instance of the ManagedComputer object that represents the WMI Provider services.
            Dim mc As ManagedComputer
            mc = New ManagedComputer()
            'Iterate through each service registered with the WMI Provider.
            Dim svc As Service
    
            'Specify the SQL Server instance name which is the service belonged to.
            svc = mc.Services("MSSQLSERVER")
    
            For Each svc In mc.Services
                'If it is a SQL Agent service, the service name equals to SQLSERVERAGENT, and the start mode is not Automatic or it is not started,
                'then change the start mode to Automatic, start the service.
                If svc.Type = 2 And svc.Name = "SQLSERVERAGENT" And (svc.StartMode <> 2 Or svc.State = 2) Then
                    svc.StartMode = 2
                    svc.Alter()
                    svc.Start()
                End If
            Next

    TechNet Subscriber Support
    If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.


    Stephanie Lv

    TechNet Community Support



    22 Februari 2012 5:06

Semua Balasan

  • By the way, I tried with "MSSQLSERVER" first.  The reference to SQL Server Agent above was just another service I tried to change the mode on.

    Steve

    21 Februari 2012 20:24
  •  Hi Steve,

    To change the start mode and start a SQL Server service on a SQL Server instance, it is required to specify the instance name, service type and service name. Please pay attention to the follow code which is used to change the start mode to Automatic for SQL Agent service of the default instance ‘MSSQLSERVER’, and then start the service.

     'Declare and create an instance of the ManagedComputer object that represents the WMI Provider services.
            Dim mc As ManagedComputer
            mc = New ManagedComputer()
            'Iterate through each service registered with the WMI Provider.
            Dim svc As Service
    
            'Specify the SQL Server instance name which is the service belonged to.
            svc = mc.Services("MSSQLSERVER")
    
            For Each svc In mc.Services
                'If it is a SQL Agent service, the service name equals to SQLSERVERAGENT, and the start mode is not Automatic or it is not started,
                'then change the start mode to Automatic, start the service.
                If svc.Type = 2 And svc.Name = "SQLSERVERAGENT" And (svc.StartMode <> 2 Or svc.State = 2) Then
                    svc.StartMode = 2
                    svc.Alter()
                    svc.Start()
                End If
            Next

    TechNet Subscriber Support
    If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.


    Stephanie Lv

    TechNet Community Support



    22 Februari 2012 5:06
  • It is the "Alter" function I was missing.  I was looking for "Update" or "Refresh".  I did not see "Alter" in any example that I looked at.  Thanks for the help, that did the trick.

    Steve

    22 Februari 2012 20:22