code 128 checksum c# Starting SQL Server and supporting services in a command batch in C#

Maker USS Code 128 in C# Starting SQL Server and supporting services in a command batch

cls echo on rem sc start w3svc sc start mssql$ss2k8 sc start reportserver$ss2k8 sc start sqlagent$ss2k8 sc start sqlbrowser sc start mssql$sqlexpress start msdtsServer start sqlwriter pause

It s also possible to start SQL Server (or any service) using .NET factory classes, and I ll show you how to do that a bit later (in listing 4).

Okay, so the network is available (at least as far as your application can tell) and the server hosting your SQL Server instance is visible on the network. Next, you can query the .NET Framework to see what SQL Server instances are visible. This is a two-step process that s simplified somewhat because we re interested only in SQL Server instances (and not other services like Reporting Services or Exchange). In summary, the code shown in listing 3 performs the following steps:

First, use the ADO.NET (2.0) System.Data.Common.DbProviderFactories object s GetFactoryClasses method to harvest the .NET data providers installed on the system. This method returns a DataTable. Pick out the SqlClient data provider row and pass it to the DbProviderFactories.GetFactory method. In this case you get a DbDataSourceEnumerator object that can be inspected via the GetDataSources method to find the visible SQL Server instances.

This is the same technique used by the Data Connection dialog box in Visual Studio and SSMS (you know, the dialog box that takes 10 seconds or so to enumerate the visible servers). This means you need to expect a similar delay before the GetFactory method completes. A code segment to perform these operations is shown in listing 3.

Dim factory As DbProviderFactory = _ DbProviderFactories.GetFactory(drProvider) Dim dsE As DbDataSourceEnumerator = _ factory.CreateDataSourceEnumerator() If dsE Is Nothing Then DataGridView1.DataSource = Nothing MsgBox("No instances visible for this provider(" _ & drProvider(0).ToString & ")") Else DataGridView1.DataSource = dsE.GetDataSources() End If Catch exNS As NotSupportedException MsgBox("This provider does not support data source enumeration...") Catch exCE As System.Configuration.ConfigurationException MsgBox("The " & drProvider(0).ToString & " could not be loaded.") Finally Me.Cursor = Cursors.Default End Try End Sub

This method exposes only those instances that can be referenced by the SqlClient .NET data provider. This means that only SQL Server instances are shown; Reporting Services, Analysis Services, and other related services are not included.

If everything has gone well, you can see the target SQL Server instance so you know the service is being exposed by the SQL Browser. Remember that the code shown previously searches the registry for installed instances, but you still don t know if the SQL Server instance has been started or if, perhaps, a DBA has paused the instance. The code to determine the instance state is quicker and simpler than searching for visible server instances. In this case, your code calls the System.ServiceProcess.ServicesController class to test the current service status. This same class can also be used to set the service status. This means you ll be able to start, stop, or pause a specific SQL Server instance (if you have sufficient rights). The trick here is to pass the correct arguments to the ServicesController class to properly identify the SQL Server instance. When the industry transitioned from SQL Server 2000 (version 8.0) to SQL Server 2005 (version 9.0), the method of referencing instances changed. SQL Server 2000 uses the service name of MSSQLSERVER. From SQL Server 2005 on, the service name changed to MSSQL followed by the instance name (separated with a $). For example, on my web site I have an instance of SQL Server 2005 named SS2K8, which shows up in services.msc as SQL Server (SS2K8) with a service name of MSSQL$SS2K8. Unfortunately, the .NET factory classes require you to pass in the same string that appears in services.msc when asked for the service name. It can be a bit confusing. Perhaps the example in listing 4 will make this easier. For purposes of this exercise, let s assume we re working with SQL Server 2005 or later. Listing 4 illustrates a routine that starts a selected SQL Server instance on a