ҡդӶ ʵŧ 纺촨շ٨絴͵ ᷹ФѺ

͵Դҷҧ Inbox Ѻ

˹ѡ
-
VB 6/VB.Net
ASP/ASP.Net
Ѻ¤
¹ҹ Flash Movie
ռͺش  
 RSS Feeds
 ǹŴ RSS Reader ...   Download  RSS Reader

Forum - www.g2gnet.com
Webmaster - www.g2gnet.com
Visitors - Session views
 5 6 1 6 1 0 1

7 ѹҤ ..2549
55 Users On-Line.
Visitors - Page views
 8 9 7 3 8 4 4
1 Ҿѹ ..2551

Google   
g2gnet.com
Ҵѡ:  

鴡ä Instance Name ͧ MS SQL Server ҹҧ API ( SQLDMO)

Category »  VB 6/VB.Net
: Webmaster 26/8/2553   : 14:02
(ҹ : 20582) 
ǧѧ ֡Ҽ¹͡ҧҡҡ (Ҩйª ... ԴҹФѺ ) еͧǹͧѹ蹡Ѻ Win32 API (Application Programming Interface) API ѹ繡â¢մöͧ Visual Basic 6 (͵) ¡ҹѾҡк (Resource) ԴҨҡкԺѵԡ MS Windows 蹤 ¡ҹͧش (ū͡Ǻҹ ) Dynamics Link Library ʡѹ DLL ФѺ 繵ͧ DLL ҹ仵Դŧͧ ... ˹ "" ѧѺ Ѵ´ͧ ѹª´ա
    ˵ؼҷ ҧԧ (References ...) ҹҹҧ SQLDMO.DLL
  • ͧѧҹ (ͧ ... ҤѺ 55555+) ŧ SQL 2008 Դ SQL7/SQL 2000 ѧѹ֧ SQLDMO.DLL ҹ ... ֧աҡ ... 仴٢͵
  • óշѺ Client ͧǧҧҹṺ (ѹ˹ѡ) ... ԡ
    1. sqldmo.dll - Distributed Management Objects COM
    2. sqldmo.rll - Distributed Management Objects Resource File
    3. sqlresld.dll - SQL Enterprise Manager Resource DLL Loader
    4. sqlsvc.dll - Database Service Layer
    5. sqlsvc.rll - Database Service Layer Resource DLL
    6. sqlunirl.dll - SQL Server Unicode/ANSI Translation Layer
    7. w95scm.dll - SQL Service Control Manager Abstraction Layer
  • ͺ ҷ ҡͧҢͧšҷӺҧ ... 55555+ ... ¶֧ͧ 3 ͺ ФѺ ҡ
ǹŴ Source Code Ѻҹ Visual Basic 6
ǹŴ Source Code Ѻ MS Visual Basic 6.0 - Service Pack 6
 ǹŴ Visual Basic 6.0 SP5: Run-Time Redistribution Pack
 ǹŴ Microsoft Data Access Object (MDAC) Jet 4.0 Update
 ǹŴ Microsoft Visual Basic Service Pack 6
Ңͧ C MSDN 蹷 2 odbc200.chm
֧ѹ¹ C ͧ͹͡͹ ... Ͷ ҹ C ҧФѺ


"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
Conclusion:
Դ¤ҹҹҧ SQLDMO ҡ ѡɳẺ 礧ԴͧҡԹ ӵͺФѺ ҡͧԹ Сҹ Win32 API ѹդ֡ҡ仡ҹ鹤Ѻ ѹ繡¡ҹѾҡк ֧մöͧкԺѵԡ Microsoft Windows ͡ҹҧ ... Ӷѡж Ҩѡ ¡ѹҹҧ ӵͺ 㨤Ѻ ... ... () ... 55555+

͵ - g2gNet Dot Com
Ţ¹ҳԪ硷͹ԡ 0407314800231
CopyLeft © 2004 - 2099 g2gNet.Com All rights reserved.
Email: [email protected] . 08-6862-6560