61604

Starting SQL Server via C#

Question:

When I start my PC, Sql Server (SQLExpress) isn´t running, it starts when I try to compile my program in Visual Studios 2010.

Is it possible to start it via C#? My problem is, that if I use the .exe without Visual Studios, it tells me that Sql Server isn´t running.

Answer1:

I would change the start mode of the Sql Server Windows Service to automatic. But you can do this in c# too, but i dont recommend it. There are other problems like access security and more.

System.Diagnostics.Process process = new System.Diagnostics.Process(); process.StartInfo.FileName = "net start \"Sql Server (SQLEXPRESS)\""; process.Start();

Sql Server (SQLEXPRESS) is the name of your service.

Answer2:

Here's how to do it with SMO in C#:

using System; using System.Collections; using System.Collections.Generic; using System.Data; using System.Diagnostics; using Microsoft.SqlServer.Management.Smo; using Microsoft.SqlServer.Management.Smo.Wmi; using Microsoft.SqlServer.Management.Common; static class SQLStart { public static void StartSQLService() { //Declare and create an instance of the ManagedComputer object that represents the WMI Provider services. ManagedComputer mc = default(ManagedComputer); mc = new ManagedComputer(); //Iterate through each service registered with the WMI Provider. Service svc = default(Service); foreach ( svc in mc.Services) { Console.WriteLine(svc.Name); } //Reference the Microsoft SQL Server service. svc = mc.Services("MSSQLSERVER"); //Stop the service if it is running and report on the status continuously until it has stopped. if (svc.ServiceState == ServiceState.Running) { svc.Stop(); Console.WriteLine(string.Format("{0} service state is {1}", svc.Name, svc.ServiceState)); while (!(string.Format("{0}", svc.ServiceState) == "Stopped")) { Console.WriteLine(string.Format("{0}", svc.ServiceState)); svc.Refresh(); } Console.WriteLine(string.Format("{0} service state is {1}", svc.Name, svc.ServiceState)); //Start the service and report on the status continuously until it has started. svc.Start(); while (!(string.Format("{0}", svc.ServiceState) == "Running")) { Console.WriteLine(string.Format("{0}", svc.ServiceState)); svc.Refresh(); } Console.WriteLine(string.Format("{0} service state is {1}", svc.Name, svc.ServiceState)); } else { Console.WriteLine("SQL Server service is not running."); } } }

This is just a conversion of MS's VB.net example. It's all explained here: <a href="http://msdn.microsoft.com/en-us/library/ms162139(v=sql.90).aspx" rel="nofollow">http://msdn.microsoft.com/en-us/library/ms162139(v=sql.90).aspx</a>

Answer3:

Don't do it in code. It is very rare that this would be necessary/useful. And do not reinvent the wheel:

Look into your computer's Services manager in the Administrative Tools. You should see your instance of SQL Server Express there. You can start it from there manually and you can also set it up to start automatically from a setting in the service properties. Set it to Automatic in the Startup Type combo box.

Follow up: As has been suggested there are many different ways to start the server/service. The issue I see with the non-Services suggestion is that you would be introducing security, configuration and other management overhead unnecessarily. SQL Server running as a service is a well-understood paradigm. Again, do not reinvent the wheel.

Answer4:

You could use a batch script that you store in your task bar in Win7 or on the desktop that you click. That assumes you only wanted to do it via C# to avoid going into another app. If you need it in app your writing in C# than this probably won't be the solution your looking for.

Look here <a href="http://www.bidn.com/blogs/briankmcdonald/bidn-blog/1011/restarting-sql-server-using-a-batch-script" rel="nofollow">http://www.bidn.com/blogs/briankmcdonald/bidn-blog/1011/restarting-sql-server-using-a-batch-script</a>

Answer5:

There are quite a few ways to tackle this. Overall, unless you need this at runtime on another person's box, I would just set the service to automatic, as dknaack has suggested. Even on another person's box, I would set the "set the service to automagic" in the installer and not as part of the application proper.

To control services in C# code in SQL Server, the Microsoft.SqlServer.Management.Smo.Wmi.Service class is one option. For a more generic option, there is System.ServiceProcess.ServiceController. You see WMI in the SQL object? That is a good indicator scripting is another decent option (and perhaps even more reusable?)

Answer6:

Here is a SMO WMI C# code sample which lists SQL services, check service state, start SQL service, etc.

<a href="http://sqlbeyond.blogspot.com/2010/06/smo-wmi-managedcomputer.html" rel="nofollow">How to use SMO WMI in C#</a>

<hr />

<a href="http://www.sqlmgmt.com" rel="nofollow">Useful SQL Tools</a>

Recommend

  • Change MS SQL Reporting service account to built-in “Network Service”
  • Wmi ManagedComputer SetServiceAccount gives “Set service account failed”
  • Python: Cross-platform solution to detect physical non-HT CPUs?
  • Detect Removable Media Ejection Request in Windows Service
  • How to get OS version and Device Name of an Android device connected via USB with C#.net (VS2010/4.0
  • For a np.array([1, 2, 3]) why is the shape (3,) instead of (3,1)? [duplicate]
  • c#.NET USB device persistent identifier
  • Equivalent for np.add.at in tensorflow
  • Memory usage and time for execution for another process using C#?
  • Phalcon\\Mvc\\Model::validation() and non-model validators
  • Lua string.gsub() by '%s' or '\\n' pattern
  • How do I configure Maven Cargo to use an embedded Tomcat server?
  • Can't connect Entity Framework to local SQL Server Express
  • how to remove a div with same ids but display='block' and display='none' in JAVa
  • Dynamically set LESS variables from user settings
  • Relative paths. baseUrl and paths not working on ionic2 - angular2
  • ConnectivityManager.CONNECTIVITY_ACTION deprecated
  • Prevent Tomcat from caching request during starup
  • UIAlertController button function not working
  • Filter strings with regex before casting to numeric
  • msbuild create itemgroup from property group
  • Updating both a ConcurrentHashMap and an AtomicInteger safely
  • Center align outputs in ipython notebook
  • Create Instant using a negative year
  • Diff between two dataframes in pandas
  • Parse a date string in a specific locale (not timezone!)
  • How to get Eclipse Oxygen to run on Java 9
  • PHP CURL timing out but CLI CURL works
  • With Hadoop, can I create a tasktracker on a machine that isn't running a datanode?
  • Chrome doesn't support silverlight anymore? How to solve this?
  • Syntax for setting draggablecursor property in google maps api
  • Paperclip, set path outside of rails root folder
  • Why HTML5 Canvas with a larger size stretch a drawn line?
  • Spray.io: When (not) to use non-blocking route handling?
  • Modifying destination and filename of gulp-svg-sprite
  • Rearranging Cells in UITableView Bug & Saving Changes
  • GridView Sorting works once only
  • Rails 2: use form_for to build a form covering multiple objects of the same class
  • How do I configure my settings file to work with unit tests?
  • Is it possible to post an object from jquery to bottle.py?