Find the Service associated with the SQL Server Instance

Question

0.00/5 (No votes)

See more:

I have a form where a user can select a local or server SQL Server database. I can get a list of network servers fine using SqlDataSourceEnumerator, but it doesn't seem to work well on getting the local SQL Server instances. It has 2 drawbacks when passing the local param;
1. It's dependent on "SQL Server Browser" service to be running to see the SQL Server instances.
2. It returns an empty string for the instance name being it's the default Sql Server instance.

Using ManagedComputer seems to be a better option as it picks up all the local SQL Server Instance names, even 2008 r2, whether "SQL Server Browser" service is running or not. Both solutions display the Sql Server Instances that exist even if they're not up; that is the respective Service isn't running. I want to display the Sql Server Instance along with its associated Service State.

The code below just lists the local Sql Server Instances and the Services in separate listboxes. Interesting note is that the ManagedComputer.Services isn't picking up the Service for my MSSQLEXPRESS 2012. Also there really isn't a clear association, as far as I can tell, linking the Instance to the Service as the naming convention is different.

There has got to be a way to tie the data together. Any ideas?

C#

ManagedComputer mc = new ManagedComputer();
if (System.Environment.Is64BitOperatingSystem)
{
    mc.ConnectionSettings.ProviderArchitecture = ProviderArchitecture.Use64bit;
}
else
{
    mc.ConnectionSettings.ProviderArchitecture = ProviderArchitecture.Use32bit;
}

foreach (Service svc in mc.Services)
{
    listBoxSQL.Items.Add(svc.Name + "  : \t " + svc.DisplayName);
}

foreach (ServerInstance si in mc.ServerInstances)
{
    listBoxLocal.Items.Add(si.Name);
}

Thanks,

Rick

Posted 16-Aug-13 15:29pm

RickiB

Updated 16-Aug-13 16:33pm

SoMad

v2

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Tiaan Lombard (TiLO) · Answer 1 · 2013-08-19T16:29:00

Solution 1

The Service you are looking for is MSSQL.
If you have a named service installed the named instance will be appended to MSSQL with a '$' sign such as: MSSQL$EXPRESS

My question to you would be... why are you trying to find the SQL Server Instances through mc...

Have a look at EnumSqlServersAvailable(computername)

http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.smoapplication.enumavailablesqlservers.aspx
or
http://msdn.microsoft.com/en-us/library/ms210425.aspx

In addition to the above... the Microsoft.SqlServer.Management.Smo.Server class has a information property that could be of great use.

Posted 19-Aug-13 16:29pm

Tiaan Lombard (TiLO)

Comments

RickiB 20-Aug-13 15:40pm

Tiaan,
Thanks for your reply. I also tried EnumSqlServersAvailable which I should have mentioned. I commented out the code with the reasons I didn't use it. It is also dependent on the "SQL Server Browser" service to be running. The section below is from MS EnumSqlServerAvailable description:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The computer that is running the instance SQL Server might not receive responses to the EnumAvailableSqlServers method in a timely manner. The returned list might not show all the available instances of SQL Server on the network. When you call the EnumAvailableSqlServers method in subsequent tries, more servers might become visible on the network.

This method fails to list the local instance if a firewall is operating on the server. The firewall blocks any broadcast traffic issued when the method is called, even if the sender is from local machine.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

This still doesn't help with tying the Server Instance with Services. I also tried using MSSQL$.contains, which only works on Express releases, but the MSSQLEXPRESS service isn't showing up when I extract the Services even though it's running. I chose to use ManagedComputer because it looked like the best option to tie the Instance to the Service.
Thanks,
Rick

RickiB · Answer 2 · 2013-08-20T13:05:00

I saw someone else use ServiceController and I just modified it, so this gets all the local SQL Server Services and their current State.

ServiceController[] myServices = ServiceController.GetServices();
foreach (ServiceController serv in myServices.Where(x => x.ServiceName.Contains("MSSQL$") ||
x.ServiceName.Contains("MSSQLSERVER")))
{
if (serv.DisplayName.Contains("SQL Server"))
listBoxSQL.Items.Add(serv.ServiceName + " : \t" + serv.DisplayName + " : \t" + serv.Status);
}

OR you can use WMI which seems to be faster.

try
{
ManagementObjectSearcher searcher =
new ManagementObjectSearcher("root\\CIMV2",
"SELECT * FROM Win32_Service WHERE Name LIKE 'MSSQL%' AND Caption LIKE 'SQL Server (%' ");

foreach (ManagementObject queryObj in searcher.Get())
{
Console.WriteLine("Name: {0}", queryObj["Name"]);
Console.WriteLine("Caption: {0}", queryObj["Caption"]);
Console.WriteLine("State: {0}", queryObj["State"]);
}
}
catch (ManagementException e)
{
MessageBox.Show("An error occurred while querying for WMI data: " + e.Message);
}

Tiaan Lombard (TiLO) · Answer 3 · 2013-08-20T11:47:00

If you are looking only on the local PC and The firewall is a problem you can have a look at the registry.... although this practice is not recommended.
This should do the trick:

private void LoadRegKey()        
{            
    RegistryKey key = Registry.LocalMachine.OpenSubKey(@"SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names");            
    foreach (string sk in key.GetSubKeyNames())            
    {                
      RegistryKey rkey = key.OpenSubKey(sk);                
      foreach (string s in rkey.GetValueNames())                
      {                    
         MessageBox.Show("Sql instance name:"+s);                
      }            
    }        
}

Find the Service associated with the SQL Server Instance

3 solutions

Solution 1

Solution 3

Solution 2

Add your solution here

Preview 0