1

I am creating a Windows form application that uses SQL Server 2008. In the setup project that I have I check for and install SQL Server 2008 if necessary on the target machine. What I need is a way to get a SQL Server 2008 - and ONLY 2008 - instance name.

Right now I am using the SmoApplication.EnumAvailableSqlServers function and checking the versions returned, but what I have found is that it does not display the default instance. This is problematic for me because if this program is being run after my installer setup SQL Server 2008 on the target machine, there are no named instances available - only the default instance.

I don't want to just blindly check and see if the traditional MSSQLSERVER default instance works for two reasons:

  1. It could be the default instance left over from a SQL Server 2008 install, which won't be able to access the DB used by my application. Skipping it and then checking for other named instances would most likely return me nothing.

  2. It could also be the default instance from a newer version of SQL Server 2012. While this instance would be able to access my DB, my experience is that it then "upgrades" the DB to the new SQL Server version, and earlier versions won't be able to access it. Since my application is intended to work with SQL Server 2008, this presents me with some problems.

SO I need to use any SQL Server 2008 named instance, or a default instance IF it is 2008.

Is there any way to do this?

Improve this question
2
  • I thought I had a solution with using SqlDataSourceEnumerator. By using the GetDataSources() method of the Instance member of this, it looks like Default instances show up, but only the name of the machine that default instanc eis running on is given. No version informaiton is provided, which creates a problem for me. Commented Jul 27, 2012 at 19:34
  • Take a look to my answer, try it! Commented Aug 28, 2012 at 12:11

1 Answer 1

Reset to default
2

Use should use the class SqlDataSourceEnumerator of the System.Data.Sql namespace (assembly System.Data.dll), this way:

SqlDataSourceEnumerator sdsEnumerator = SqlDataSourceEnumerator.Instance;

DataTable sqlServerInstances = sdsEnumerator.GetDataSources();

These class will not require to have Sql Server installed, but can retrieve informations about any Sql Server instance. Remind this:

"

Due to the nature of the mechanism used by SqlDataSourceEnumerator to locate data sources on a network, the method will not always return a complete list of the available servers, and the list might not be the same on every call. If you plan to use this function to let users select a server from a list, make sure that you always also supply an option to type in a name that is not in the list, in case the server enumeration does not return all the available servers. In addition, this method may take a significant amount of time to execute, so be careful about calling it when performance is critical.

"

The method GetDataSource is what you need: here the documentation, returns a DataTable with all the informations you need:

ServerName Name of the server.

InstanceName Name of the server instance. Blank if the server is running as the default instance.

IsClustered Indicates whether the server is part of a cluster.

Version Version of the server (8.00.x for SQL Server 2000, and 9.00.x for SQL Server 2005, 10.00.x for SQL Server 2008, 10.50.x for SQL Server 2008 R2 and 11.00.x for SQL Server 2012).

You can find the full reference of the class SqlDataSourceEnumeratorhere.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.