martes, 21 de febrero de 2012 20:17
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 IfVery simple (assuming you have SMO objects installed). Yet, it does not change the mode.
Todas las respuestas
martes, 21 de febrero de 2012 20:24By 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.
miércoles, 22 de febrero de 2012 5:06Moderador
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.
TechNet Community Support
miércoles, 22 de febrero de 2012 20:22It 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.