â¤é´¡Òäé¹ËÒ Instance Name ¢Í§ MS SQL Server ¼èÒ¹·Ò§ API (äÁèãªé SQLDMO)

"DSN=Sales;HOST=red;UID=Smith;PWD=Sesame;DATABASE=SalesOrders"

#define BRWS_LEN 100
SQLHENV  henv;
SQLHDBC  hdbc;
SQLHSTMT   hstmt;
SQLRETURN  retcode;
SQLCHAR  szConnStrIn[BRWS_LEN], szConnStrOut[BRWS_LEN];
SQLSMALLINT  cbConnStrOut;

/* Allocate the environment handle. */
retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);       
   
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
   /* Set the version environment attribute. */
   retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, SQL_OV_ODBC3, 0);

   if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
      /* Allocate the connection handle. */
      retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);

      if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {

      /* Call SQLBrowseConnect until it returns a value other than */
      /* SQL_NEED_DATA (pass the data source name the first time). */
      /* If SQL_NEED_DATA is returned, call GetUserInput (not  */
      /* shown) to build a dialog from the values in szConnStrOut. */
      /* The user-supplied values are returned in szConnStrIn,   */
      /* which is passed in the next call to SQLBrowseConnect.   */

         lstrcpy(szConnStrIn, "DSN=Sales");
         do {
            retcode = SQLBrowseConnect(hdbc, szConnStrIn, SQL_NTS,
                     szConnStrOut, BRWS_LEN, &cbConnStrOut);
            if (retcode == SQL_NEED_DATA)
               GetUserInput(szConnStrOut, szConnStrIn);
         } while (retcode == SQL_NEED_DATA);

         if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO){

            /* Allocate the statement handle. */
            retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);

            if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
            /* Process data after successful connection */
               ...;
               ...;
               ...;
               SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
            }
            SQLDisconnect(hdbc);
         }
      }
      SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
   }
}
SQLFreeHandle(SQL_HANDLE_ENV, henv);

Íѹ´Ñºáá·Õèµéͧ¡ÅèÒǶ֧«èСè͹àÅÂÇèÒ §Ò¹¹Õéà»ç¹¡ÒÃàÃÕ¡ãªé§Ò¹ ËÃ×Í Interface ¼èÒ¹·Ò§ ODBC ËÃ×Í Open Database Connectivity (ODBC) «Ö觶×Íä´éÇèÒà¨éÒ ODBC ¹Õé¤×ÍÊÔ觷Õèà¡èÒ àµèÒ âºÃÒ³ÅéÒÊÁÑÂÍÂèÒ§ÁÒ¡ 55555+ à¾ÃÒÐà»ç¹¡ÒÃàª×èÍÁµèÍÃкº°Ò¹¢éÍÁÙÅ (DBMS) ·Õè¨Ñ´ä´éÇèÒ "ªéÒ·ÕèÊØ´" à·èÒ·ÕèÂѧ¤§ÁÕãªé§Ò¹¡Ñ¹ÍÂÙèã¹ÂؤÊÁѹÕé ... ¹Ñ蹤×Í ¶Ö§Áѹ¨Ðà¡èÒ áµèÂѧäÁèà¡çº àÃÒ¡ç¹ÓÁѹÁÒãªé§Ò¹ãËéà¡Ô´»ÃÐ⪹ìä´éÅèФÃѺ ... ¾Õè¹éͧ

àÁ×èÍ·Ó¡ÒÃÃÐºØ SQL Server Driver (ã¹·Õè¹Õé¤×Í SQL Server à·èÒ¹Ñé¹) áÅÐàÃÕ¡ãªé§Ò¹¿Ñ§¤ìªÑè¹ SQLBrowseConnect µÑÇ Driver Manager ¨Ð·Ó¡Ò÷´Êͺ¡ÒÃàª×èÍÁµèͼèÒ¹·Ò§ ODBC à¾×èÍ·Ó¡Òä׹¤èÒ Attribute µèÒ§æ¡ÅѺÁÒ ´Ñ§¹Õé ¤×Í Server, User name, Password, Application Name áÅÐ Workstation ID (API Áѹ令Ø¡ѹàͧã¹ÃдѺÅèÒ§áÅéǤÃѺ àÃÒʹã¨à©¾ÒФèÒ·ÕèÊ觡ÅѺÁÒãËé¡ç¾Í) ... 㹡óբͧ Server Attribute ¨ÐáÊ´§¼ÅÃÒª×èͧ͢ Server Name ÍÍ¡ÁÒà·èÒ¹Ñ鹤ÃѺ ... ÅѡɳТͧ¡Òä׹¤èÒ¡ÅѺÁÒ¨Ðà»ç¹´Ñ§¹Õé (¡Ã³ÕÁÕ Instance ÁÒ¡¡ÇèÒ 1)
"SERVER:Server={Server1,Server2,Server3};UID:Login ID=?;PWD:Password=?;*APP:AppName=?;*WSID:WorkStation ID=?;"
´Ñ§¹Ñé¹¢éÍÁÙÅ·ÕèàÃÒµéͧ¡Òà (Instance Name) Áѹ¨ÐÍÂÙè㹪èǧ¢Í§Ç§àÅ纻ա¡Òà»Ô´ áÅÐ »Ô´ à·èÒ¹Ñé¹ «Öè§àÃÒ¨Ðãªé¤ÓÊÑè§ (ËÃ×Í ¿Ñ§¤ìªÑè¹) Mid$ à»ç¹µÑǨѴ¡Òà «Ö觨зÓãËéä´éÍÍ¡ÁÒà»ç¹ªØ´´Ñ§¹Õé ¤×Í
Server1,Server2,Server3
´Ñ§¹Ñé¹µéͧãªé¤ÓÊÑè§ (ËÃ×Í ¿Ñ§¤ìªÑè¹) 㹡ÒÃá¡ (Split) Instance Name áµèÅЪش´éÇÂà¤Ã×èͧËÁÒ Comma (,)

»ÃСͺ仴éÇ 2 Machine áµèÁÕ 3 Instance Name
• (local) à»ç¹ MS SQL Server 2008 Professional ã¹à¤Ã×èͧ¢Í§¼Áàͧ (à¤Ã×èͧ¹ÕéÁѹª×è͹ѧËÁÙÊѺ¤ÃѺ ... 55555+)
• SERVER-XP à»ç¹ Default Instance ¢Í§à¤Ã×èͧ·´Êͺ¼Á µÔ´µÑé§ MS SQL Server 2000 Enterprise
• SERVER-XP\PAYROLL2008 à»ç¹ Instance ¢Í§à¤Ã×èͧ·´Êͺ¼Á µÔ´µÑé§ MS SQL Server 2008 Professional

Option Explicit

' ¡ÒÃàÃÕ¡ãªé§Ò¹ Win32 API (Application Programming Inteface) ¼èÒ¹·Ò§ odbc32.dll

' http://msdn.microsoft.com/en-us/library/ms709270%28VS.85%29.aspx
Private Declare Function SQLAllocEnv Lib "odbc32.dll" (phenv As Long) As Integer

' http://msdn.microsoft.com/en-us/library/ms712455%28v=VS.85%29.aspx
Private Declare Function SQLAllocHandle Lib "odbc32.dll" ( _
    ByVal hType As Integer, _
    ByVal hInput As Long, _
    ByRef phOutput As Long _
    ) As Integer

' http://msdn.microsoft.com/en-us/library/ms714565%28VS.85%29.aspx
Private Declare Function SQLBrowseConnect Lib "odbc32.dll" (ByVal hDbc As Long, _
    ByVal szConnStrIn As String, _
    ByVal cbConnStrIn As Integer, _
    ByVal szConnStrOut As String, _
    ByVal cbConnStrOutMax As Integer, _
    pcbconnstrout As Integer _
    ) As Integer

' ¾Ç¡¹Õé·Ó˹éÒ·ÕèµÒÁª×èͿѧ¤ìªÑ蹢ͧÁѹàÅ ... à»Ô´´Ùã¹àÇçº MS ËÃ×Íá¼è¹ MSDN ¡çä´é¤ÃѺ
Private Declare Function SQLDisconnect Lib "odbc32.dll" (ByVal hDbc As Long) As Integer
Private Declare Function SQLFreeConnect Lib "odbc32.dll" (ByVal hDbc As Long) As Integer
Private Declare Function SQLFreeEnv Lib "odbc32.dll" (ByVal hEnv As Long) As Integer

' http://msdn.microsoft.com/en-us/library/ms710123%28VS.85%29.aspx
Private Const SQL_SUCCESS = 0
Private Const SQL_HANDLE_ENV = 1
Private Const SQL_HANDLE_DBC = 2
Private Const SQL_NEED_DATA = 99
Private Const DEFAULT_RESULT_SIZE = 1024

Private Const SQL_DRIVER_STR As String = "DRIVER={SQL Server};"

' àÁ×èÍÁÕ¡ÒÃÊ觤èÒ¼èÒ¹ SQLBrowseConnect  ¡ç¨ÐÊÒÁÒö¤é¹ËÒ
' áÅÐ áÊ´§ÃÒÂÅÐàÍÕ´µèÒ§æ¢Í§¡ÒÃàª×èÍÁµèÍ SQL Servers

' #####################################################
' â»Ãá¡ÃÁÂèÍÂ㹡Òäé¹ËÒ Instance Name ¢Í§ SQL Server ... áÅÐÁÕ¡ÒÃÊ觤èÒ¡ÅѺà»ç¹ String
' #####################################################
Private Function BrowseSQLServer() As String
    
    ' µÑÇá»Ãá¨é§Ê¶Ò¹Ð¢Í§¡ÒÃà¡Ô´ Error ËÃ×ÍäÁè
    Dim Ret As Long
    
    ' Handle ¢Í§¡Òà Connect áÅÐ Environment
    Dim hDbc As Long
    Dim hEnv As Long
    
    ' ¡Ó˹´¡ÒÃàª×èÍÁµèÍẺ ODBC (Open DataBase Connectivity)
    Dim strConnection As String
    
    ' ÃѺ¤èÒ Instance name ¢Í§ SQL Server
    Dim strGetServer As String
    
    ' ¨Ó¹Ç¹¤ÇÒÁÂÒǢͧ¡Òà Connect ·Õèä´é¨Ò¡ SQLBrowseConnect
    Dim LenConnOutput As Integer
    
    ' àÅ×Í¡¡ÒõԴµè͡Ѻ SQL Server à·èÒ¹Ñé¹ áÅÐ à»ç¹¡Ò÷´Êͺ¼èÒ¹ ODBC
    strConnection = SQL_DRIVER_STR
    
    ' ¨Í§¾×é¹·ÕèàÍÒäÇé»ÃÐÁÒ³ 1024 µÑÇÍÑ¡¢ÃÐ (¹èҨоÍÁÑ駤ÃѺ ... ËÒ¡äÁè¾Í¡çËÂÍ´àËÃÕ­à¾ÔèÁààÍÒ¹èÐ ÍÔÍÔÍÔÍÔÍÔ)
    strGetServer = Space(DEFAULT_RESULT_SIZE)
    
    ' »ÃÐ⪹ì¢Í§µÑÇá»Ã Ret ·Õè¼ÁÁÑ¡¨ÐÁÕàÊÁÍ㹡ÒÃãªé§Ò¹ API ¤×Í
    ' ÃѺ¤èÒ¨Ò¡¿Ñ§¤ìªÑ蹢ͧ¡Òû¯ÔºÑµÔµÒÁ¤ÓÊÑè§ áÅéǵÃǨÊͺ¤ÇÒÁ¼Ô´¾ÅÒ´¢Í§¡Ò÷ӧҹ¡è͹·Õè¨Ðä»·Ó§Ò¹Í×è¹µèÍ
    ' ã¹Êèǹ¹Õé¼Á¡àÅÔ¡äÁèµÃǨÊͺ¹èФÃѺ áµèÐÁÕàÍÒäÇéà¾ÃÒÐà»ç¹¤ÇÒÁà¤ÂªÔ¹ã¹¡ÒÃàÅè¹¡Ñºä¿ API 55555+
    
    ' àÃÔèÁµé¹¡ÒõÑ駤èÒÊÀÒ¾áÇ´ÅéÍÁ (Environment Attribute) ãËé¡Ñº SQL Server ¼èÒ¹·Ò§ ODBC
    Ret = SQLAllocEnv(hEnv)
    ' ËÃ×ÍẺ¹Õé¡çä´é Call SQLAllocEnv(hEnv)
    ' »¡µÔàÃÒµéͧÁÒà¢Õ¹´Ñ¡ Error ¡è͹´éÇ ËÒ¡à¡Ô´¢éͼԴ¾ÅÒ´¢Öé¹ÁÒ àªè¹
    'If Ret <> SQL_SUCCESS Then ... ËÒ¡ÁѹäÁèä´éÃѺ¤èÒ¡ÅѺà»ç¹ 0 áÊ´§ÇèÒà¡Ô´ Error
    
    ' ¨Ñº¨Í§µÓá˹觢ͧ¡ÒÃàª×èÍÁµèͧ͢ ODBC (ËÒ¡¤èÒ Ret <> 0 áÊ´§ÇèÒà¡Ô´»Ñ­ËÒ)
    Ret = SQLAllocHandle(SQL_HANDLE_DBC, ByVal hEnv, hDbc)
    
    ' àÃÕ¡ SQLBrowseConnect à¾×è͢͢éÍÁÙÅ㹡ÒÃàª×èÍÁµèͼèÒ¹·Ò§ ODBC
    Ret = SQLBrowseConnect( _
        ByVal hDbc, _
        strConnection, _
        Len(strConnection), _
        strGetServer, _
        Len(strGetServer) + 2, _
        LenConnOutput _
        )
    ' »¡µÔàÃÒµéͧÁÒà¢Õ¹´Ñ¡ Error ¡è͹´éÇ ËÒ¡à¡Ô´¢éͼԴ¾ÅÒ´¢Öé¹ÁÒ àªè¹
    'If Ret <> SQL_NEED_DATA Then ... ËÒ¡ÁѹäÁèä´éÃѺ¤èÒ¡ÅѺà»ç¹ 99 áÊ´§ÇèÒà¡Ô´ Error
    
    ' àÁ×èÍÊÑè§ SQLBrowseConnect ¨ÐÊ觤èÒ Instance name áÅÐ ¤ÇÒÁÂÒÇ (LenConnOutput)
    strGetServer = Left(strGetServer, LenConnOutput)
    
    ' µÑÇá»Ã·Õèµéͧ·Ó¡ÒõѴ¤èÒÍ͡仺ҧÊèǹ
    Dim chrFirst As Integer
    Dim chrLast As Integer
    
    ' µÑÇÍÂèÒ§¢Í§¡ÒÃÃѺ¤èÒÁÒ¨Ò¡ strGerServer ... Åͧ Debug ´Ù·Õèà¤Ã×èͧ¤Ø³àͧ´éǹèФÃѺ
    ' SERVER:Server={(local),SERVER-XP,SERVER-XP\PAYROLL2008}; -->µèÍ·éÒºÃ÷Ѵ´éÒ¹ÅèÒ§
    ' UID:Login ID=?;PWD:Password=?;*APP:AppName=?;*WSID:WorkStation ID=?
    ' Debug.Print strGetServer
    
    ' àÃÒµéͧ¡ÒâéÍÁÙÅ੾ÒеÑÇÍÑ¡¢ÃеÒÁËÅѧà¤Ã×èͧËÁÒ»ա¡Òà»Ô´ ... ã¹·Õè¹Õé¤×Í ¡è͹ǧàÅçº (local)
    ' à¨Íà¤Ã×èͧËÁÒ»ա¡Òà»Ô´·ÕèÅӴѺ·Õè 15 (ãËéºÇ¡¢Öé¹ 1 à»ç¹¨Ø´àÃÔèÁµé¹¢Í§¡ÒùѺ)
    chrFirst = InStr(1, strGetServer, "{") + 1
    
    ' µÑÇÊØ´·éÒ·Õèµéͧ¡Òà ¤×Í ÊÔé¹ÊØ´·Õèà¤Ã×èͧËÁÒ»ա¡Ò»Ô´ µÃ§ 2008}
    ' à¨Íà¤Ã×èͧËÁÒ»ա¡Ò»Ô´·ÕèÅӴѺ 55
    chrLast = InStr(1, strGetServer, "}")
    
    ' àµÃÕÂÁÊ觤èҤ׹¡ÅÑºä» ... â´Â¼Áà¢Õ¹â¤é´áººãËéÁͧàË繧èÒÂæ
    'BrowseSQLServer = Mid$(strGetServer, µÓá˹觻ա¡Òà»Ô´, µÓá˹觻ա¡Ò»Ô´ - µÓá˹觻ա¡Òà»Ô´)
    BrowseSQLServer = Mid$(strGetServer, chrFirst, chrLast - chrFirst)
    ' ËÒ¡àÍÒẺÂÒ¡ ... à¢Ò¨Ðä´éàÃÕ¡àÃÒÇèÒ½ÕÁ×Í àÍÔê¡ææææ
    'BrowseSQLServer = Mid$(strGetServer, InStr(1, strGetServer, "{") + 1, _
        InStr(1, strGetServer, "}") - InStr(1, strGetServer, "{") - 1)
    
    ' ¿Ñ§¤ìªÑè¹ SQLDisconnect àÃÕ¡ãªé§Ò¹àÁ×èÍ SQLBrowseConnect ·Ó§Ò¹àÊÃç¨ÊÔé¹
    ' à¾×è͵Ѵ¡ÒÃàª×èÍÁµèÍ·Ò§ ODBC ·Ñé§ËÁ´ áÅéǤ׹¤èÒ˹èǤÇÒÁ¨Ó¡ÅѺ¤×¹Ãкº»¯ÔºÑµÔ¡ÒÃ
    Call SQLDisconnect(hDbc)
    Call SQLFreeEnv(hEnv)
    Call SQLFreeConnect(hDbc)

End Function

Private Sub cmdBrowse_Click()
    Dim Count As Integer
    
    ' µÑÇá»Ãá¡ Instance name ÍÍ¡¨Ò¡¡Ñ¹à¢éÒÊÙè Array
    Dim strField() As String
    
    ' 仿ѧ¤ìªÑè¹µÃǨÊͺ¡Òà Connect ¢Í§ SQL Server
    ' áÅéÇá¡áµèÅÐÊèǹ ËÃ×Í SQL Server Instance ¹Ñè¹àͧ ÍÍ¡¨Ò¡à¤Ã×èͧËÁÒ Comma (,)
    strField() = Split(BrowseSQLServer, ",")

    cmbSQLServer.Clear
    
    For Count = 0 To UBound(strField)
        cmbSQLServer.AddItem Trim$(strField(Count))
    Next
    
    cmbSQLServer.ListIndex = 0
    
    MsgBox "¤é¹ËÒ SQL Server Instance ¾º " & cmbSQLServer.ListCount & _
        " ÃÒ¡ÒÃ.", vbOKOnly + vbInformation, "ÃÒ§ҹʶҹÐ"

End Sub

Private Sub Form_Load()
    Me.Move (Screen.Width - Me.Width) \ 2, (Screen.Height - Me.Height) \ 2
    cmbSQLServer.Clear
End Sub