Click here to Skip to main content
Click here to Skip to main content

Enumerate SQL Server Instances in C#, Using ODBC

, 18 Apr 2005
Rate this:
Please Sign up or sign in to vote.
An article on enumerating SQL Server instances in C# using ODBC thus removing any dependancy on SQLDMO. Based on the C++ article by Santosh Rao.

Sample Image - C#SQLInfoEnumeratorDemo.jpg

Introduction

This article describes a C# class that utilises ODBC (SQLBrowseConnect) to obtain a list of SQL Servers on a network and returns an array of instances. If an instance is supplied with a valid username/password, then a list of all the databases on the instance is returned.

Background

I needed a way to obtain a list of SQL Server instances without using SQLDMO. I found a C++ implementation of the SQLBrowseConnect by Santosh Rao. This is a C# implementation.

Using the code

In order for the code to work, ODBC must be installed on your machine. Nearly all Microsoft Operating Systems have this installed. I have only tested this on Windows 2000 and XP. In order to use this, add a reference to the SQLEnumerator.cs file. The class is in the Moletrator.SQLDocumentor namespace and is SQLInfoEnumerator. The important work is done in the RetrieveInformation method. This calls the relevant ODBC commands passing in the relevant values. The important item is the value of inputParam. If this is blank then nothing is returned. When it contains a valid driver (DRIVER=SQL SERVER for MS SQL Server), it will check for all instances of this driver on the network returning a string value which is then parsed.

If this string is expanded to include a valid SQL Server instance and a valid username/password, then a list of all the databases on the server instance is returned. If the username/password are not valid then the a list of SQL Server instances is returned:

  1. In order to get a list of SQL Server instances, create an instance of the class and call EnumerateSQLServers. The example below adds the list of SQL Servers to a list box SQLListBox.
    SQLInfoEnumerator sie = new SQLInfoEnumerator();
    SQLListBox.Items.AddRange(sie.EnumerateSQLServers());
  2. To get a list of databases on a SQL Server instance, use the code below. The SQL Server instance is the selected instance from the list box populated in sample A. The username/password are entered by the user.
    SQLInfoEnumerator sie = new SQLInfoEnumerator();
    sie.SQLServer = listboxSQLServerInstances.SelectedItem.ToString(); 
    sie.Username = textboxUserName.Text;
    sie.Password = textboxPassword.Text;
    SQLListBox.Items.AddRange(sie.EnumerateSQLServersDatabases());

The demo code contains a full GUI with an example on how to call each of the above methods.

History

18 April 2005 1:00 p.m. - Initial write.

License

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

Share

About the Author

earmitage
Web Developer
Ireland Ireland
No Biography provided

Comments and Discussions

 
GeneralMy vote of 5 Pinmembertina_nbz12-Feb-13 1:47 
GeneralMy vote of 5 PinmvpKanasz Robert24-Sep-12 5:56 
GeneralMy vote of 2 PinmemberAbir Stolov29-Aug-12 0:39 
QuestionReading a column value in unicode in sql server side Pinmembercancerion13-Jun-12 19:58 
GeneralMy vote of 2 Pinmembergggustafson4-Mar-11 3:26 
GeneralVery good article PinmemberYanela Somdaka12-Jan-10 22:49 
AnswerFinding SQL2005/2008-servers PinmemberJonas Hammarberg16-May-09 2:51 
GeneralEven more simplified Pinmembervsoft22-Oct-08 5:16 
GeneralRe: Even more simplified PinmemberMember 438474528-Dec-08 17:51 
GeneralRe: Even more simplified PinmembervishalMGiri28-Dec-08 17:56 
GeneralGreat... Pinmemberd00_ape31-Jul-08 23:10 
GeneralLicense Pinmemberdavidm9925-Jul-08 19:39 
GeneralRe: License Pinmemberearmitage12-Dec-08 8:36 
QuestionProblem in finding datbases Pinmemberkssknov10-Apr-08 3:59 
GeneralGreat Work indeed ! Pinmemberram kumar 202112-Sep-07 20:37 
GeneralThanks. PinmemberEric Groise23-May-07 5:46 
GeneralSQL-Server 2000 Enterprise ODBC Creation error PinmemberCh. Kaleem5-Sep-06 21:02 
Generaldetecting named msde instances Pinmemberrajeshatsrin22-Aug-06 20:56 
GeneralInstances are sometimes not enumerated PinmemberWenxiang Tao25-May-06 7:49 
GeneralRe: Instances are sometimes not enumerated Pinmemberrajeshatsrin22-Aug-06 21:00 
GeneralRe: Instances are sometimes not enumerated PinmemberMast3rMind17-Jan-07 5:59 
GeneralEnumerate SQL Server Instances in C#, Using ODBC PinmemberGodfather3215-May-06 22:38 
QuestionInstances? Pinmembermirano19-Jan-06 11:25 
The code will return no instance of the server, i.e. it would display SERVER2000 instead of SERVER2002/Production.
Furthuremore, it would also display (local) for the local SQL server, while the local SQL server has a full name, so trying to use (local) in a SQL connection string would not even work.
Any thoughts on that?
 
Thanks.
 
Sarajevo, Bosnia
AnswerRe: Instances? PinmemberAlexander Stromer29-Mar-06 1:18 
JokeEXCELENT!!! PinmemberXTonos16-Jan-06 13:08 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140814.1 | Last Updated 19 Apr 2005
Article Copyright 2005 by earmitage
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid