Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 12 of 12
  1. #1
    Senior Coder alykins's Avatar
    Join Date
    Apr 2011
    Posts
    1,742
    Thanks
    41
    Thanked 191 Times in 190 Posts

    SQL Server 08R2 Express cmd line properties

    I was wondering if there is a way to set the startup properties of the sql server agent via command line... it would save me a ton of time managing all the machines I have if I didn't have to RDP into each one... rightclick-manage-sql server properties-startup mode auto > to manual

    any ideas?
    Last edited by alykins; 11-21-2012 at 09:09 PM.

    I code C hash-tag .Net
    Reference: W3C W3CWiki .Net Lib
    Validate: html CSS
    Debug: Chrome FireFox IE

  • #2
    Regular Coder
    Join Date
    Apr 2012
    Location
    St. Louis, MO
    Posts
    985
    Thanks
    7
    Thanked 101 Times in 101 Posts
    ^_^

    If anyone knows of a website that can offer ColdFusion help that isn't controlled by neurotic, pedantic jerks* (stackoverflow.com), please PM me with a link.
    *
    The neurotic, pedantic jerks are not the owners; just the people who are in control of the "popularity contest".

  • #3
    Senior Coder alykins's Avatar
    Join Date
    Apr 2011
    Posts
    1,742
    Thanks
    41
    Thanked 191 Times in 190 Posts
    almost... those are commands to start up SQL if you are doing it from command prompt (I have used those in the past bc these machines do not have SQL Management Studio)- anyways what I was looking for was more of a fix to change the start-up mode (there is 'Automatic', 'Manual', as well as 'Disabled')

    I am wondering if it is a registry setting maybe... I was hoping for a script I could just shoot @ it and flip the startup method

    I code C hash-tag .Net
    Reference: W3C W3CWiki .Net Lib
    Validate: html CSS
    Debug: Chrome FireFox IE

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    Quote Originally Posted by alykins View Post
    almost... those are commands to start up SQL if you are doing it from command prompt (I have used those in the past bc these machines do not have SQL Management Studio)- anyways what I was looking for was more of a fix to change the start-up mode (there is 'Automatic', 'Manual', as well as 'Disabled')

    I am wondering if it is a registry setting maybe... I was hoping for a script I could just shoot @ it and flip the startup method
    Services?
    If so then yep, it can be done by the registry or by issuing an sc. Lets see, I believe it would be sc config [ServiceName] demand (I think its demand :P). You can also put the server name/ip between the sc and config to force it on another machine. Requires administrative user of course.
    To command the services from the registry, you should be able to find that under: HKLM\System\CurrentControlSet\services\* and the setting is under the "start" dword. Its values are 0x2 = auto, 0x3 = manual, and 0x4 = disabled. 0 and 1 are boot and system, you don't typically need to use those.
    Check for the SQLServeAgent key. Not sure exactly what its called with 2008R2 express.
    Hopefully that's what you need :P
    Last edited by Fou-Lu; 11-20-2012 at 09:18 PM.

  • Users who have thanked Fou-Lu for this post:

    alykins (11-21-2012)

  • #5
    Senior Coder alykins's Avatar
    Join Date
    Apr 2011
    Posts
    1,742
    Thanks
    41
    Thanked 191 Times in 190 Posts
    It's not an issue of not knowing how to do it the 'normal way' it's an issue of having to remote into every single machine when I could do something like

    Code:
    PsExec.exe \\machine cmd.exe ??? Script to modify SQL   /k shutdown -t 0
    in essence letting me one shot from my machine's cmd prompt rather than RDP'ing into a ton of machines

    Edit: and there are a lot

    I code C hash-tag .Net
    Reference: W3C W3CWiki .Net Lib
    Validate: html CSS
    Debug: Chrome FireFox IE

  • #6
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    I hear you I'm all for scripting; manually rdp'ing so many machines is a pain (I've 450ish active machines that I worry about, and if I sure wouldn't manually change something on all of them either).
    If it is the services, you can effectively create a batch and iterate a list in a text file if you want. So long as your user account has administrative access on the machines, you can simply execute an sc MyServerName config ServiceName type=demand I think it would be. So you likely have a list of all the machine name/ips available, you can create a text file and iterate that. My batch skills are always rusty until I need to use them so I can't write that up for you atm, but the idea would be simply:
    Code:
    # your servernames (I've always wanted server names based on virus' :)):
    EBOLA
    MALARIA
    TYPHOID
    CHOLERA
    And then when you iterate the read of the file, you'd have a call similar to sc !a! config SERVICENAME type= demand.

  • Users who have thanked Fou-Lu for this post:

    alykins (11-21-2012)

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,537
    Thanks
    77
    Thanked 4,381 Times in 4,346 Posts
    You could also write a ".js" or ".vbs" script file to do it, if you hate MS command language as much as I do.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    alykins (11-21-2012)

  • #8
    Senior Coder alykins's Avatar
    Join Date
    Apr 2011
    Posts
    1,742
    Thanks
    41
    Thanked 191 Times in 190 Posts
    Sweet! Thanks guys for all the help so far! pushed me far forward to where I need to be. So I am halfway through writing the vbs for it when I stumbled across a warning in another post to never use vbs for logon purposes; it is a huge security risk... So I was wondering if this was a security risk or not? also I am not the greatest w/ vbs and I am having trouble attaching to the runas process I start... I commented it so you can see what I am trying to do; is what I am doing wrong glaringly obvious? (I know that the syntax is wrong and that it will kick off a process of cmd.exe; but idk the proper syntax)

    Code:
    machine = InputBox("Target Machine...")
    UserInput = InputBox( "Password..." )
    
    
    set WshShell = CreateObject("WScript.Shell")
    set WshEnv = WshShell.Environment("Process")
    WinPath = WshEnv("SystemRoot") & "\System32\runas.exe"
    
    rc=WshShell.Run("runas /user:domain\username cmd.exe",2,FALSE)
    Wscript.Sleep 30
    WshShell.AppActivate(WinPath)
    WshShell.SendKeys UserInput & chr(10) & chr(13)
    'WinPath = WshEnv("SystemRoot") & "\System32\cmd.exe"
    'Wscript.Sleep 30
    'WshShell.AppActivate(WinPath)
    'WshShell.SendKeys "cd\" & chr(10) & chr(13) & "PsExec.exe \\ & machine &_
    '                    "cmd.exe sc config MSSQL$SQLEXPRESS start= demand"
    *I also though of trying to attach to the process but when looking for it I kept getting the same inane threads posted across many site by the same person & I don't have a ref book

    I then thought that something like this
    Code:
    rc=WshShell.Run("runas /user:domain\username cmd.exe | " & UserInput)
    would be better than runas but I am not sure of the syntax for 'where' to put my commands after i execute cmd
    ie would it be
    Code:
    rc=WshShell.Run("runas /user:cwopa\dhsvcwic03 cmd.exe | " & UserInput & " some command")
    or
    rc=WshShell.Run("runas /user:cwopa\dhsvcwic03 cmd.exe some command | " & UserInput)

    I code C hash-tag .Net
    Reference: W3C W3CWiki .Net Lib
    Validate: html CSS
    Debug: Chrome FireFox IE

  • #9
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    Ugh, vbs.
    You're a C# developer yeah? I would think that the C# would probably be a better solution for this; AFAIK you cannot thread vbs, and if you are talking about many machines to run against it would probably make sense to thread it up and send them off.
    I would assume that C# would have easy interaction with WMI, but unless you specify username/password combinations somewhere, you would need to rely on a user that's an administrator on all machines or a domain/enterprise admin to execute it.

  • #10
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,537
    Thanks
    77
    Thanked 4,381 Times in 4,346 Posts
    I simply don't have a system where I can try that stuff any more.

    I'm doing contract work from home and while I have VPN to a system where this could be done I don't have high enough privileges there to do it.

    I do think it *could* be done via VBS, but what FouLu says re threading makes sense.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #11
    Senior Coder alykins's Avatar
    Join Date
    Apr 2011
    Posts
    1,742
    Thanks
    41
    Thanked 191 Times in 190 Posts
    yeah I think a C# shell would be faster- and that I can bang out no problem (stupid little throw away form, or even console)

    Thanks for the input guys!

    I code C hash-tag .Net
    Reference: W3C W3CWiki .Net Lib
    Validate: html CSS
    Debug: Chrome FireFox IE

  • #12
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    Quote Originally Posted by Old Pedant View Post
    I simply don't have a system where I can try that stuff any more.

    I'm doing contract work from home and while I have VPN to a system where this could be done I don't have high enough privileges there to do it.

    I do think it *could* be done via VBS, but what FouLu says re threading makes sense.
    Definitely doable in vbs; I have apps that were written for cluster management in (of all things) vbs and WMI. The "threading" concept is actually a relay of a spawned sub process that detects its completion state before attempting to poll for current information. Ingenious, I have to admit, but far cry from the threading model. Unfortunately, the way its built (and due to the limitation of vbs), if I execute my own WMI management at the same time it will result in the script crashing since it'll never detect that the WMI has completed. Oh well.

    If I were you alykins, I'd write it as an actual manager. Given a list of machines, you can do something like select a drop down for the service then have it populate in a table below the current status and start up type for the service. Choose the service (add some select/desselect all type options), and submit those for the WMI to update. Neat.
    This way you could also handle if you get something new as well and want to add it to the management. Just use some files to construct the dynamic data you need.

    Now I get to make an awesome mspaint picture
    Lol that's awesome.
    Attached Thumbnails Attached Thumbnails SQL Server 08R2 Express cmd line properties-awesomeform.png  


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •