CODEWITHSUNDEEP
c#sql-server
aggaton
aggaton

Reputation: 3350

Enumerating sql server instances

I am trying to query odbc for "visible" database instances. Have tried below on multiple different systems. It returns some on all systems, but in most cases not all that should be found. In particular on some systems it does not return the local database instance. What are the limitations of SQLBrowseConnect? I know the database exist, the SQL Browser runs, I can connect to the instance and it shows up in the SQL manager window, however it is invisible to SQLBrowseConnect. I have tried using SqlDataSourceEnumerator instead as well, it too has limitations, in particular when used on a SQLServer 2014 instance it returns an empty list (maybe due to the fact my code in .NET 4.0).

EDIT: it should be mentioned that SqlDataSourceEnumerator.Instance.GetDataSources() finds the local database when odbc does not.

What is the bullet proof way of enumerating SQL server instances?

IntPtr m_environmentHandle = IntPtr.Zero;
IntPtr m_connectionHandle = IntPtr.Zero;
StringBuilder inConnection = new StringBuilder(InputParam);
short stringLength = (short) inConnection.Length;
StringBuilder outConnection = new StringBuilder(4 * 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,
                      4 * DEFAULT_RESULT_SIZE, out stringLength2Ptr))
                  {
                      var sqlState = new StringBuilder(DEFAULT_RESULT_SIZE);
                      var sqlMessage = new StringBuilder(DEFAULT_RESULT_SIZE);
                      var info = SQLGetDiagRec(SQL_HANDLE_ENV, m_connectionHandle, 1, sqlState, IntPtr.Zero, sqlMessage, DEFAULT_RESULT_SIZE, IntPtr.Zero);
                      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
{
    if (m_connectionHandle!= IntPtr.Zero)
        SQLFreeHandle(SQL_HANDLE_DBC, m_connectionHandle);
    if (environmentHandle!= IntPtr.Zero)
        SQLFreeHandle(SQL_HANDLE_DBC, environmentHandle);
}

Upvotes: 0

Views: 277

Answers (1)

Softtech
Softtech

Reputation: 112

I had same issue and after digging into deep, i found the max Outconnection size is less than return stringLength2Ptr and that is a reason was getting truncated.

So check your return stringLength2Ptr size and assigned max size to outconnection. if return stringLength2Ptr size is bigger and you trying to get string data of OutConnection then it will failed to return full string. I do not know how you using your outconnection string data.

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

in my case increasing outconnection got fixed my issue.

Upvotes: 1

Related Questions