Click here to Skip to main content
11,490,566 members (38,222 online)
Click here to Skip to main content

Finding SQL Servers on the Network

, 22 Dec 2003 Public Domain 249.6K 11.7K 122
Rate this:
Please Sign up or sign in to vote.
Locating MS SQL Servers by using ODBC through C# PInvoke calls.

Sample Image - DBGrep.jpg

Introduction

I am a TSQL fanatic. The programs I code are highly dependent upon MS SQL stored procedures. I pay for this love of TSQL when a major overhaul of the system is necessary. Sometimes my code needs to be updated heavily in two places; client and server. To facilitate these updates, I created a database searching program I call DB Grep. It uses Regex to search out every reference to words or phrases in an entire database. The program has saved many hours of research and gives me the warm fuzzy "I didn’t miss anything" feeling.

When developing DB Grep, I ran into an interesting problem. How do I find the SQL Servers on my network?

Options

After many hours of web research, I came up with the following alternatives:

Option Pro Con
No Location Services. No code to write. Typing in SQL server names by memory is a pain.
Use Windows OS Services. Using NetServerEnum is very fast. Does not always return the desired results. It returns Windows server names, not SQL Server names, and they are not always the same. It also does not work well on a non-domain based network. Couldn't find my local MSDE server.
Use the SQLDMO objects. Returns the desired results. Installation headaches. Installing COM objects is always problematic, not to mention possible license issues.
Use ODBC Returns the desired results. Should already be installed. Haven't found any yet.

Needless to say, I picked the ODBC solution. This required a bit of research with a lot of PInvoke trial and error.

I should state up front that this has not been tested on Windows 95/98/ME. I have decided that these operating systems are no longer necessary for my new development. The code has been tested on Windows 2000 and XP using Framework 1.1.

The Process

In order to acquire the names of the available SQL servers from ODBC, we have to allocate an environment, set the ODBC style and connect to the ODBC service. SQLAllocHandle() is used to get the environment and connection handles. In between the calls, it is necessary to specify what version of ODBC is to be used, by calling SQLSetEnvAttr(). I choose ODBC 3.0 using a system constant. Of course, you must always play nice with the ODBC resources by releasing both the environment and connection handles with matching calls to SQLFreeHandle().

I wrapped the allocation calls in a try block and the free calls in the finally section to ensure the release of the ODBC resources. The necessary PInvoke declarations to setup and tear down the ODBC environment are as follows:

private const short SQL_HANDLE_ENV = 1;
private const short SQL_HANDLE_DBC = 2;
private const int SQL_ATTR_ODBC_VERSION = 200;
private const int SQL_OV_ODBC3 = 3;
private const short SQL_SUCCESS = 0;


[DllImport("odbc32.dll")]
private static extern short SQLAllocHandle(
    short hType, 
    IntPtr inputHandle, 
    out IntPtr outputHandle);
[DllImport("odbc32.dll")]
private static extern short SQLSetEnvAttr(
    IntPtr henv, 
    int attribute, 
    IntPtr valuePtr, 
    int strLength);
[DllImport("odbc32.dll")]
private static extern short SQLFreeHandle(
    short hType, 
    IntPtr handle);

Once the connection has been established, we can use a trick of the SQL ODBC driver to locate the advertising MS SQL servers. I attempt to open a MS SQL database connection using SQLBrowseConnect() by specifying only the SQL driver. The driver accommodates my request by building a connection string with the required parameters filled in with the possible values it can derive (like the available MS SQL Servers). It then returns a value stating that it needs more information. The server names can be easily parsed out of this connection string.

For speed, I pre-allocate a StringBuilder with a capacity of 1024 characters. In case of an extra large list of available servers, I test an out parameter to see if a larger string is necessary. I recall the SQLBrowseConnect() function with the newly resized string if necessary. The PInvoke for SQLBrowseConnect() call follows:

private const short SQL_NEED_DATA = 99;
private const string SQL_DRIVER_STR = "DRIVER=SQL SERVER";
 
[DllImport("odbc32.dll",CharSet=CharSet.Ansi)]
private static extern short SQLBrowseConnect(
    IntPtr hconn, 
    StringBuilder inString, 
    short inStringLength, 
    StringBuilder outString, 
    short outStringLength, 
    out short outLengthNeeded);

For example, I pass "DRIVER=SQL SERVER" into SQLBrowseConnect() and get something like "SERVER:Server={(local),SQL_SERVER1,SQL_BKSVR};UID:Login ID=?; PWD:Password=?; *APP:AppName=?; *WSID:WorkStation ID=?" returned. It is a simplistic task to pull out the comma delimited substring between the two curly braces. To simplify the use of the server list, I call the Split() method on the substring to return a string array that can be used in a foreach statement.

For reuse, I encapsulated the PInvoke declarations and the static method within a class. Since this is just a helper method, I took precautions to hide any failures from release code and return a null string[] value in that case. A null return value indicates that no servers were found. Here is an example of calling the resultant code:

string[] theAvailableSqlServers = SqlLocator.GetServers();
if (theAvailableSqlServers != null)
{
    myListBox.DataSource = theAvailableSqlServers;
} 
else
{
    MessageBox.Show("No SQL servers found.");
}

Summary

This code is more research than skill derived. Hopefully, I am able to save you a few hours of trudging through MSDN on a future project. If you do a lot of MS SQL work, you may be interested in my DB Grep program, which is freely available (with source) on GotDotNet. It currently works very well in my environment but, I am sure it could use a good workout elsewhere.

License

This article, along with any associated source code and files, is licensed under A Public Domain dedication

Share

About the Author

Michael Potter
Chief Technology Officer
United States United States
No Biography provided

Comments and Discussions

 
GeneralLink for DB Grep Tool Pin
Member 142514920-Jan-11 2:51
memberMember 142514920-Jan-11 2:51 
QuestionCan you send me the form related to this program? Pin
lathavarma29-Sep-10 23:24
memberlathavarma29-Sep-10 23:24 
GeneralSuggested improvement using Regex Pin
kjhduyudmhmxc31-Jul-10 18:42
memberkjhduyudmhmxc31-Jul-10 18:42 
GeneralLink for DB Grep Pin
Member 39203771-Oct-09 18:34
memberMember 39203771-Oct-09 18:34 
QuestionLicense Pin
Sparkley Doodads14-Jul-08 8:37
memberSparkley Doodads14-Jul-08 8:37 
AnswerRe: License Pin
Michael Potter14-Jul-08 8:59
memberMichael Potter14-Jul-08 8:59 
GeneralVery Bad Pin
DevelopmentTech6-Jul-08 5:49
memberDevelopmentTech6-Jul-08 5:49 
GeneralThanks Pin
Geethanga Amarasinghe9-Oct-07 19:19
memberGeethanga Amarasinghe9-Oct-07 19:19 
Questionconnect to database in sql? Pin
lildiapaz5-Jul-07 5:11
memberlildiapaz5-Jul-07 5:11 
Generalusing your GetSqlClass Pin
RepliCrux19-Jun-07 16:12
memberRepliCrux19-Jun-07 16:12 
GeneralUse with Sybase 9 Pin
Chris_McGrath6-Apr-06 16:06
memberChris_McGrath6-Apr-06 16:06 
QuestionCannot see local sql server (MSDE) when .NET Framework 2.0 is installed Pin
CaJuDo23-Jan-06 1:03
memberCaJuDo23-Jan-06 1:03 
AnswerRe: Cannot see local sql server (MSDE) when .NET Framework 2.0 is installed Pin
Tommi G25-Oct-06 16:13
memberTommi G25-Oct-06 16:13 
GeneralApp Failed to Initialize Pin
dna199012-Jan-06 7:28
memberdna199012-Jan-06 7:28 
GeneralRe: App Failed to Initialize Pin
Michael Potter12-Jan-06 9:04
memberMichael Potter12-Jan-06 9:04 
GeneralHelp me,please. Pin
SinbaQL29-Dec-05 18:07
memberSinbaQL29-Dec-05 18:07 
QuestionHow get the Infomation about the stored procedures and so on. Pin
SinbaQL29-Dec-05 15:45
memberSinbaQL29-Dec-05 15:45 
GeneralNew link for DB Grep Pin
Enrico Detoma27-Dec-05 0:29
memberEnrico Detoma27-Dec-05 0:29 
GeneralRe: New link for DB Grep Pin
SinbaQL27-Dec-05 23:05
memberSinbaQL27-Dec-05 23:05 
GeneralNew Class support Pin
wschlichtman10-Sep-05 15:00
memberwschlichtman10-Sep-05 15:00 
GeneralNice work! Pin
kris.mackintosh14-Jul-05 13:43
memberkris.mackintosh14-Jul-05 13:43 
Questionmanipulating server? Pin
Unruled Boy19-Jun-05 18:41
memberUnruled Boy19-Jun-05 18:41 
QuestionI need to help: how to get sever name, database name to update connection string? Pin
heovissan2-Jun-05 18:23
memberheovissan2-Jun-05 18:23 
AnswerRe: I need to help: how to get sever name, database name to update connection string? Pin
SinbaQL28-Dec-05 3:54
memberSinbaQL28-Dec-05 3:54 
GeneralProblem ...please help Pin
pubududilena8-May-05 17:56
memberpubududilena8-May-05 17:56 
GeneralRe: Problem ...please help Pin
Michael Potter9-May-05 3:43
memberMichael Potter9-May-05 3:43 
GeneralCorrection Pin
David M. Kean23-Feb-05 18:41
memberDavid M. Kean23-Feb-05 18:41 
GeneralRe: Correction Pin
David M. Kean23-Feb-05 19:24
memberDavid M. Kean23-Feb-05 19:24 
GeneralRe: Correction Pin
Michael Potter24-Feb-05 4:35
memberMichael Potter24-Feb-05 4:35 
GeneralGreat Help - Note on multiple instances Pin
John Albright1-Jan-05 15:34
memberJohn Albright1-Jan-05 15:34 
GeneralRe: Great Help - Note on multiple instances Pin
John Albright2-Jan-05 6:26
memberJohn Albright2-Jan-05 6:26 
QuestionCan not find the local MSDE whit XP Prof SP2 an active firewall Pin
Markus Eurisch14-Dec-04 4:08
memberMarkus Eurisch14-Dec-04 4:08 
AnswerRe: Can not find the local MSDE whit XP Prof SP2 an active firewall Pin
Markus Eurisch15-Dec-04 5:41
memberMarkus Eurisch15-Dec-04 5:41 
GeneralDataTable question.. Pin
meyi26-Sep-04 5:50
membermeyi26-Sep-04 5:50 
QuestionHow to run Pin
MattFritzOO79-Sep-04 8:53
memberMattFritzOO79-Sep-04 8:53 
GeneralBrilliant Pin
CodeGimp19-Aug-04 1:27
memberCodeGimp19-Aug-04 1:27 
GeneralRe: Brilliant Pin
Michael Potter21-Aug-04 6:28
memberMichael Potter21-Aug-04 6:28 
GeneralRe: Brilliant Pin
Alexander Ruscle25-Jan-05 9:07
memberAlexander Ruscle25-Jan-05 9:07 
GeneralRe: Brilliant Pin
HyperX1-Aug-05 21:10
memberHyperX1-Aug-05 21:10 
GeneralRe: Brilliant Pin
SinbaQL27-Dec-05 15:28
memberSinbaQL27-Dec-05 15:28 
Generalisql Pin
alanshen20929-Jul-04 18:00
memberalanshen20929-Jul-04 18:00 
GeneralRe: isql Pin
Anonymous30-Jul-04 16:43
sussAnonymous30-Jul-04 16:43 
GeneralGreate but work only if a network is present Pin
Querton8-Jun-04 23:27
memberQuerton8-Jun-04 23:27 
GeneralRe: Greate but work only if a network is present Pin
Michael Potter9-Jun-04 4:08
memberMichael Potter9-Jun-04 4:08 
GeneralRe: Greate but work only if a network is present Pin
Jorge Da Silva8-Jul-04 6:16
memberJorge Da Silva8-Jul-04 6:16 
GeneralRe: Greate but work only if a network is present Pin
Stefan Wey17-Sep-04 5:11
memberStefan Wey17-Sep-04 5:11 
GeneralRe: Greate but work only if a network is present Pin
Dom24722-Nov-04 7:32
memberDom24722-Nov-04 7:32 
GeneralServer Databases List Pin
nlaham11-May-04 13:07
membernlaham11-May-04 13:07 
GeneralRe: Server Databases List Pin
Michael Potter11-May-04 18:28
memberMichael Potter11-May-04 18:28 
GeneralRe: Server Databases List Pin
nlaham12-May-04 6:08
membernlaham12-May-04 6: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 | Terms of Use | Mobile
Web02 | 2.8.150520.1 | Last Updated 23 Dec 2003
Article Copyright 2003 by Michael Potter
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid