Hot File

Finding SQL Servers on the Network

View: 569    Dowload: 0   Comment: 0   Post by: naruto  
Author: none   Category: SQL Server   Fields: Other

0 point/2 review File has been tested

Download   finding-sql-servers-on-the.rar (32.27 KB)

You need to Sign In to download the file finding-sql-servers-on-the.rar
If you do not have an account then Sign up for free here

Download error   Report copyright violation

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.

Introduction

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.");
}

Finding SQL Servers on the Network

Finding SQL Servers on the Network Posted on 06-05-2014  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. 5/10 569

Comment:

To comment you must be logged in members.

Files with category

  • Insufficient access rights error when doing backup in SQL Server

    View: 321    Download: 0   Comment: 0   Author: none  

    Insufficient access rights error when doing backup in SQL Server

    Category: SQL Server
    Fields: none

    2.25/2 review
    All of sudden SQL Server Management studio gives me the following error message when I'm trying to browse for the backup file location.

  • Migrate MySQL to Microsoft SQL Server

    View: 2373    Download: 0   Comment: 0   Author: none  

    Migrate MySQL to Microsoft SQL Server

    Category: SQL Server
    Fields: none

    0/2 review
    This article describes a few simple steps in order to migrate MySQL into Microsoft SQL Server 2005. The technique is very easy, but useful if you plan to move your data from MySQL and upgrade it finally to a Microsoft SQL Server environment.

  • COUNT of DISTINCT Rows in SQL Server

    View: 1241    Download: 0   Comment: 0   Author: none  

    COUNT of DISTINCT Rows in SQL Server

    Category: SQL Server
    Fields: Other

    0/4 review
    SQL Server does not support COUNT(DISTINCT *). For example, the below query fails.

  • Finding SQL Servers on the Network

    View: 569    Download: 0   Comment: 0   Author: none  

    Finding SQL Servers on the Network

    Category: SQL Server
    Fields: Other

    0/2 review
    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...

  • SQL Server and Database Enumerator

    View: 548    Download: 0   Comment: 0   Author: none  

    SQL Server and Database Enumerator

    Category: SQL Server
    Fields: Other

    0/1 review
    This sample helps you to enumerate the list of SQL Servers and the databases and languages that a particular SQL server has. The class CSQLInfoEnumerator encapsulates this functionality into it.

  • Dynamic Management Views [DMV] – A SQL Server 2005 Feature

    View: 567    Download: 0   Comment: 0   Author: none  

    Dynamic Management Views [DMV] – A SQL Server 2005 Feature

    Category: SQL Server
    Fields: Other

    0/2 review
    The DMVs; newly introduced in SQL Server 2005 gives the database administrator information about the current state of the SQL Server machine. These values will help the administrator to diagnose problems and tune the server for optimal performance....

  • Table Value Parameter in SQL Server 2008

    View: 474    Download: 0   Comment: 0   Author: none  

    Table Value Parameter in SQL Server 2008

    Category: SQL Server
    Fields: Computers - Technology

    0/1 review
    One of the fantastic new features of SQL Server 2008 is the Table value parameter. In previous versions of SQL Server, there wasn’t a native way to pass a table to a Stored Procedure or functions. The usual workaround was to pass a large varchar or...

  • SQL Server DO's and DONT's

    View: 468    Download: 0   Comment: 0   Author: none  

    SQL Server DO's and DONT's

    Category: SQL Server
    Fields: Computers - Technology

    0/0 review
    So, you are now the leader of a SQL Server based project and this is your first one, perhaps migrating from Access. Or maybe you have performance problems with your SQL Server and don't know what to do next. Or maybe you simply want to know of some...

 
Newsletter Email

File suggestion for you

File top downloads

logo codetitle
Codetitle.com - library source code to share, download the file to the community
Copyright © 2015. All rights reserved. codetitle.com Develope by Vinagon .Ltd