Tech Off Thread

6 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

MSDE on Win2k not showing up in client listing.

Back to Forum: Tech Off
  • User profile image
    qwert231

    I'm using a class called SQLInfo that gives me an enumeration of SQL servers on the network. However, some of my customers are reporting that the clients cannot see the server that is running MSDE. In one instance, MSDE is on Win2k.

    Here's some of the SQLInfo code:

    #region ODBC32 external function definitions
    [
    DllImport("odbc32.dll")]private static extern short SQLAllocHandle( short handleType, IntPtr inputHandle, out IntPtr outputHandlePtr );
    [
    DllImport("odbc32.dll")]private static extern short SQLSetEnvAttr( IntPtr environmentHandle, int attribute, IntPtr valuePtr, int stringLength );
    [
    DllImport("odbc32.dll")]private static extern short SQLFreeHandle( short hType, IntPtr Handle );
    [
    DllImport("odbc32.dll",CharSet=CharSet.Ansi)]private static extern short SQLBrowseConnect( IntPtr handleConnection, StringBuilder inConnection, short stringLength, StringBuilder outConnection, short bufferLength, out short stringLength2Ptr );
    #endregion

    public string[] EnumerateSQLServers()
    {
     return RetrieveInformation(SQL_DRIVER_STR);
    }

    private string[] RetrieveInformation(string InputParam)
    {

    IntPtr m_environmentHandle=IntPtr.Zero;
    IntPtr m_connectionHandle = IntPtr.Zero;
    StringBuilder inConnection = new StringBuilder(InputParam);
    short stringLength= (short)inConnection.Length;
    StringBuilder outConnection = new StringBuilder(DEFAULT_RESULT_SIZE);
    short stringLength2Ptr= 0;

    try
    {
     if (SQL_SUCCESS == SQLAllocHandle(SQL_HANDLE_ENV, m_environmentHandle, out m_environmentHandle))

    {
     if (SQL_SUCCESS == SQLSetEnvAttr(m_environmentHandle,SQL_ATTR_ODBC_VERSION,(IntPtr)SQL_OV_ODBC3,0))

    {
     if (SQL_SUCCESS == SQLAllocHandle(SQL_HANDLE_DBC, m_environmentHandle, out m_connectionHandle))

    {
     if (SQL_NEED_DATA == SQLBrowseConnect(m_connectionHandle, inConnection, stringLength, outConnection, DEFAULT_RESULT_SIZE, out stringLength2Ptr))

    {

    if (SQL_NEED_DATA != SQLBrowseConnect(m_connectionHandle, inConnection, stringLength, outConnection, DEFAULT_RESULT_SIZE, out stringLength2Ptr))

    {
     throw new ApplicationException("No Data Returned.");
    }
    }
    }
    }
    }
    }

    catch //(Exception ex)
    {
     throw new ApplicationException("Cannot Locate SQL Server.");
    }

    finally
    {
     FreeConnection(m_connectionHandle);
     FreeConnection(m_environmentHandle);
    }

    if (outConnection.ToString()!="")
     {
    return ParseSQLOutConnection(outConnection.ToString());}
    else{return null;}
    }



  • User profile image
    qwert231

    No ideas?

  • User profile image
    RichardRudek

    qwert231 wrote:
    No ideas?


    Search engine ?

    KB article: 841771


  • User profile image
    qwert231

    Sorry, the page you requested is not available.

    on kb 841771.

  • User profile image
    W3bbo

    qwert231 wrote:
    Sorry, the page you requested is not available.

    on kb 841771.


    Works for me, but in case it goes down again:

    All the instances of SQL Server or SQL Server Desktop Engine are not enumerated when you use an application or a method that uses SQLBrowseConnect
    View products that this article applies to.
    Article ID    :    841771
    Last Review    :    July 12, 2004
    Revision    :    2.0
    SYMPTOMS
    If you try to enumerate the instances of Microsoft SQL Server 2000 or SQL Server Desktop Engine (also known as MSDE 2000) by using an application or a method in a program, all the instances of SQL Server or SQL Server Desktop Engine are not enumerated. This problem may occur when the application or the method uses the SQLBrowseConnect Microsoft Open Database Connectivity (ODBC) API to enumerate the instances of SQL Server or SQL Server Desktop Engine.
    Back to the top    Back to the top
    MORE INFORMATION
    You can use an application, such as Osql.exe, or a method, such as the ListInstalledInstances SQL Distributed Management Objects (SQL-DMO) method, to enumerate the instances of SQL Server or SQL Server Desktop Engine. The application and the method use the SQLBrowseConnect ODBC API to enumerate the instances of SQL Server or SQL Server Desktop Engine.

    The SQLBrowseConnect ODBC API uses the User Datagram Protocol (UDP) listener to determine the instances of SQL Server or SQL Server Desktop Engine that are running on a remote computer. An instance of SQL Server or SQL Server Desktop Engine that has any one of the network protocols that are enabled and running on the remote computer becomes the UDP listener.

    The following behaviors are expected when you use the SQLBrowseConnect ODBC API to enumerate the instances of SQL Server or SQL Server Desktop Engine:
    •    An instance of SQL Server or SQL Server Desktop Engine is qualified for enumeration if the following conditions are true:
    •    The instance has at least one network protocol enabled.
    •    The instance is not a hidden instance.
    •    For a local computer, all the qualified instances are enumerated, even if the instances are not running.
    •    For a remote computer, all the qualified instances are enumerated only if one or more of the qualified instances are running.
    By reading the information from the registry, you can enumerate all the instances of SQL Server and SQL Server Desktop Engine that are installed on a computer, even when the instances are not running or when the protocols are not enabled for the instances. The list of instances of SQL Server and SQL Server Desktop Engine that are installed on a computer is stored in the following registry key on the computer:
    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\InstalledInstances
    Back to the top    Back to the top
    REFERENCES
    For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
    814064 (http://support.microsoft.com/kb/814064/) FIX: SQLBrowseConnect may cause an access violation in the client application
    Back to the top    Back to the top


  • User profile image
    RichardRudek

    And this KB Article  explains that it's a firewall/timeout issue Wink



    You may not be able to enumerate all the instances of SQL Server 2000 on your network by using an application or a program

    Article ID    :    885345
    Last Review    :    September 14, 2004
    Revision    :    1.0


    SYMPTOMS
    If you try to enumerate the instances of Microsoft SQL Server 2000 that are currently active on your network by using an application or a method in a program, the application or the program may not list all the instances of SQL Server 2000. The application or the program that enumerates the instances of SQL Server 2000 on a network can be your own custom application or a SQL Server tool, such as SQL Server Enterprise Manager, SQL Server Service Manager, or SQL Query Analyzer.

    Note This problem occurs on computers that are running Microsoft Windows XP Service Pack 2 (SP2).

    CAUSE
    To determine and enumerate the instances of SQL Server that are running on remote computers, the application or the program uses the APIs, such as SQLBrowseConnect, that send User Datagram Protocol (UDP) packets to all the computers on your network from a network port. The remote computers then send the details of the instances of SQL Server to the application or the program by using the same network port.

    By default, Windows Firewall permits incoming unicast response packets to a port for only 3 seconds after a multicast packet or a broadcast packet is sent from the port. If the remote computers send the details of the instances of SQL Server after 3 seconds, the information is blocked by the Windows Firewall. Therefore, the enumeration may not contain all the instances of SQL Server that are currently active on your network.

    WORKAROUND
    To work around this problem, you must add the application or the program that enumerates the instances of SQL Server 2000 on a network to the Windows Firewall exception list on the computer that is running Windows XP SP2.

    For more information about how to add an application or a program to the Windows Firewall exception list, see the "Allowing exceptions despite the risks" section on the following Microsoft Web site:
    http://www.microsoft.com/windowsxp/using/security/internet/sp2_wfexceptions.mspx (http://www.microsoft.com/windowsxp/using/security/internet/sp2_wfexceptions.mspx)

    STATUS
    This behavior is by design.

    REFERENCES For additional information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
    841771 (http://support.microsoft.com/kb/841771/) All the instances of SQL Server or SQL Server Desktop Engine are not enumerated when you use an application or a method that uses SQLBrowseConnect
    843090 (http://support.microsoft.com/kb/843090/) Description of the Windows Firewall feature in Windows XP Service Pack 2
    875357 (http://support.microsoft.com/kb/875357/) Troubleshooting Windows Firewall settings in Windows XP Service Pack 2

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.