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

Question

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

Answer 1

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

Answer 2

 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

Answer 3

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