年代久遠,我自己也查不到了。:( 重新發一個:) ***記得在VB中引用SQL-DMO對像。*** Dim i As Integer 'Use the SQL DMO Application Object to find the available SQL Servers Set oSQLServerApp = New SQLDMO.Application
Dim namX As NameList Set namX = oSQLServerApp.ListAvailableSQLServers
For i = 1 To namX.Count Debug.Print namX.Item(i) Next
Set oSQLServerApp = Nothing
从高人处获得方法,与大家分享!谢谢Bardo(巴顿)Bardo(巴顿) 回复于2002-1-12 15:58:51 得20分 上次有人问,列出网上邻居中所有计算机的名称。上次所抄给大家的代码有些乱, 现在给出新的代码:Option Explicit'============================================================================== '类模块名称:clsListServer '模块功能:用来列出所有的、或用户要求的网络服务器。 '============================================================================== ' All workstations Private Const SV_TYPE_WORKSTATION As Long = &H1 ' All servers Private Const SV_TYPE_SERVER As Long = &H2 ' Any server running with Microsoft SQL Server Private Const SV_TYPE_SQLSERVER As Long = &H4 ' Primary domain controller Private Const SV_TYPE_DOMAIN_CTRL As Long = &H8 ' Backup domain controller Private Const SV_TYPE_DOMAIN_BAKCTRL As Long = &H10 ' Server running the Timesource service Private Const SV_TYPE_TIME_SOURCE As Long = &H20 ' Apple File Protocol servers Private Const SV_TYPE_AFP As Long = &H40 ' Novell servers Private Const SV_TYPE_NOVELL As Long = &H80 ' LAN Manager 2.x domain member Private Const SV_TYPE_DOMAIN_MEMBER As Long = &H100 ' Server sharing print queue Private Const SV_TYPE_PRINTQ_SERVER As Long = &H200 ' Server running dial-in service Private Const SV_TYPE_DIALIN_SERVER As Long = &H400 ' Xenix server Private Const SV_TYPE_XENIX_SERVER As Long = &H800 ' Unix server Private Const SV_TYPE_SERVER_UNIX As Long = SV_TYPE_XENIX_SERVER ' Windows NT/Windows 2000 workstation or server Private Const SV_TYPE_NT As Long = &H1000 ' Server running Windows for Workgroups Private Const SV_TYPE_WFW As Long = &H2000 ' Microsoft File and Print for NetWare Private Const SV_TYPE_SERVER_MFPN As Long = &H4000 ' Windows NT/Windows 2000 server that is not a domain controller Private Const SV_TYPE_SERVER_NT As Long = &H8000 ' Server that can run the browser service Private Const SV_TYPE_POTENTIAL_BROWSER As Long = &H10000 ' Server running a browser service as backup Private Const SV_TYPE_BACKUP_BROWSER As Long = &H20000 ' Server running the master browser service Private Const SV_TYPE_MASTER_BROWSER As Long = &H40000 ' Server running the domain master browser Private Const SV_TYPE_DOMAIN_MASTER As Long = &H80000
Private Const SV_TYPE_SERVER_OSF As Long = &H100000
Private Const SV_TYPE_SERVER_VMS As Long = &H200000 ' Windows 95 or later Private Const SV_TYPE_WINDOWS As Long = &H400000 ' Root of a DFS tree Private Const SV_TYPE_DFS As Long = &H800000 'SV_TYPE_CLUSTER_NT Server clusters available in the domain Private Const SV_TYPE_CLUSTER_NT As Long = &H1000000 ' IBM DSS (Directory and Security Services) or equivalent Private Const SV_TYPE_DCE As Long = &H10000000 ' return list for alternate transport Private Const SV_TYPE_ALTERNATE_XPORT As Long = &H20000000 ' Servers maintained by the browser.Return local list only Private Const SV_TYPE_LOCAL_LIST_ONLY As Long = &H40000000 ' Primary domain Private Const SV_TYPE_DOMAIN_ENUM As Long = &H80000000 ' All servers. Handy for NetServerEnum2 Private Const SV_TYPE_ALL As Long = &HFFFFFFFF
Private Const SIZE_SI_101 = 24 Private Type SERVER_INFO_101 dwPlatformID As Long lpszServerName As Long dwVersionMajor As Long dwVersionMinor As Long dwType As Long lpszComment As Long End Type Private Declare Function NetServerEnum Lib "netapi32.dll" ( _ ByVal ServerName As Long, _ ByVal level As Long, _ Buffer As Long, _ ByVal prefmaxlen As Long, _ entriesread As Long, _ totalentries As Long, _ ByVal ServerType As Long, _ ByVal domain As Long, _ resumehandle As Long) As Long Private Declare Function NetApiBufferFree Lib "netapi32.dll" ( _ bufptr As Any) As Long
Private Declare Sub CopyMemory Lib "kernel32" _ Alias "RtlMoveMemory" _ (hpvDest As Any, hpvSource As Long, _ ByVal cbCopy As Long)
Private Declare Function lstrlenW Lib "kernel32" _ (ByVal lpString As Long) As Long
Public Function GetNetServers(Optional ByVal nServerType _ As E_SVR_TYPE = SV_TYPE_ALL&, Optional ByVal szDomainName As String, Optional ErrStr As String) As Variant '------------------------------------------------------------------------------ '函数名称:GetServers '函数作用:根据条件获得Net上的计算机名(或服务器名)。 '参数描述:nServerType:需要获得某计算机的类型。 ' szDomainName: 指定域或工作组名 ' ErrStr: 失败时返回错误信息 '返回值: 变体字串数组,获得的网络计算机名称。 '------------------------------------------------------------------------------ Dim pszServer As Long, pszDomain As Long Dim nLevel As Long, i As Long, bufptr As Long, TempBufPtr As Long Dim nPrefMaxLen As Long, nEntriesRead As Long, nTotalEntries As Long Dim nResumeHandle As Long, nRes As Long Dim ServerInfo As SERVER_INFO_101 Dim RetValueStr As String Dim lszDoMainb() As Byte, lngDoMainLen As Long
pszServer = 0& If Len(szDomainName) = 0 Then pszDomain = 0& Else lngDoMainLen = BSTRtoLPWSTR(szDomainName, lszDoMainb, pszDomain) End If nLevel = 101 nPrefMaxLen = &HFFFFFFFF
Do nRes = NetServerEnum(pszServer, nLevel, bufptr, _ nPrefMaxLen, nEntriesRead, nTotalEntries, _ nServerType, pszDomain, nResumeHandle) If ((nRes = ERROR_SUCCESS) Or (nRes = ERROR_MORE_DATA)) And _ (nEntriesRead > 0) Then TempBufPtr = bufptr For i = 0 To nEntriesRead - 1 If Len(RetValueStr) <> 0 Then RetValueStr = RetValueStr & "," End If CopyMemory ServerInfo, ByVal TempBufPtr, SIZE_SI_101 RetValueStr = RetValueStr & _ GetPointerToByteStringW(ServerInfo.lpszServerName) TempBufPtr = TempBufPtr + SIZE_SI_101 Next i Else ErrStr = "NetServerEnum failed: " & nRes End If NetApiBufferFree (bufptr) Loop While nEntriesRead < nTotalEntries If Len(RetValueStr) <> 0 Then GetNetServers = Split(RetValueStr, ",") Else GetNetServers = vbNullString End If
End Function Private Function GetPointerToByteStringW(ByVal dwData As Long) As String
Dim tmp() As Byte Dim tmplen As Long If dwData <> 0 Then tmplen = lstrlenW(dwData) * 2 If tmplen <> 0 Then ReDim tmp(0 To (tmplen - 1)) As Byte CopyMemory tmp(0), ByVal dwData, tmplen GetPointerToByteStringW = tmp End If End If
End Function Function BSTRtoLPWSTR(sBSTR As String, b() As Byte, lpwsz As Long) As Long ' Input: a nonempty BSTR string ' Input: **undimensioned** byte array b() ' Output: Fills byte array b() with Unicode char string from sBSTR ' Output: Fills lpwsz with a pointer to b() array ' Returns byte count, not including terminating 2-byte Unicode null character ' Original BSTR is not affected
Dim cBytes As Long
cBytes = LenB(sBSTR)
' ReDim array, with space for terminating null ReDim b(1 To cBytes + 2) As Byte
'在窗体中可增加以下代码: 'Private Sub Command1_Click() ' Dim ClsLstSvr As New ClsListServer ' Dim StrNetSvrs As Variant ' Dim ErrStr As String ' Dim i As Integer ' Dim sDomainName As String ' ' sDomainName = "YourDoMainName" ' ' StrNetSvrs = ClsLstSvr.GetNetServers(, sDomainName, ErrStr) ' ' If IsArray(StrNetSvrs) Then ' For i = 0 To UBound(StrNetSvrs) ' Combo1.AddItem StrNetSvrs(i) ' Next i ' End If 'End Sub致于要获取数据库 则要用缺省密码打开 Master 库 然后用 “Select Name from databases” 列出所有数据库。 但如不知密码仍不能访问 所以这无必要!
掉了一打頭發,終於找到了一個簡單的方法。:-)Create table tmp (servername varchar(300)) --得到Server列表 insert into tmp Exec master..xp_cmdshell 'osql -L' --篩選資料 Select ltrim(servername) as servername From tmp where servername is not null and servername<>'Servers:'
重新發一個:)
***記得在VB中引用SQL-DMO對像。***
Dim i As Integer
'Use the SQL DMO Application Object to find the available SQL Servers
Set oSQLServerApp = New SQLDMO.Application
Dim namX As NameList
Set namX = oSQLServerApp.ListAvailableSQLServers
For i = 1 To namX.Count
Debug.Print namX.Item(i)
Next
Set oSQLServerApp = Nothing
上次有人问,列出网上邻居中所有计算机的名称。上次所抄给大家的代码有些乱,
现在给出新的代码:Option Explicit'==============================================================================
'类模块名称:clsListServer
'模块功能:用来列出所有的、或用户要求的网络服务器。
'==============================================================================
' All workstations
Private Const SV_TYPE_WORKSTATION As Long = &H1
' All servers
Private Const SV_TYPE_SERVER As Long = &H2
' Any server running with Microsoft SQL Server
Private Const SV_TYPE_SQLSERVER As Long = &H4
' Primary domain controller
Private Const SV_TYPE_DOMAIN_CTRL As Long = &H8
' Backup domain controller
Private Const SV_TYPE_DOMAIN_BAKCTRL As Long = &H10
' Server running the Timesource service
Private Const SV_TYPE_TIME_SOURCE As Long = &H20
' Apple File Protocol servers
Private Const SV_TYPE_AFP As Long = &H40
' Novell servers
Private Const SV_TYPE_NOVELL As Long = &H80
' LAN Manager 2.x domain member
Private Const SV_TYPE_DOMAIN_MEMBER As Long = &H100
' Server sharing print queue
Private Const SV_TYPE_PRINTQ_SERVER As Long = &H200
' Server running dial-in service
Private Const SV_TYPE_DIALIN_SERVER As Long = &H400
' Xenix server
Private Const SV_TYPE_XENIX_SERVER As Long = &H800
' Unix server
Private Const SV_TYPE_SERVER_UNIX As Long = SV_TYPE_XENIX_SERVER
' Windows NT/Windows 2000 workstation or server
Private Const SV_TYPE_NT As Long = &H1000
' Server running Windows for Workgroups
Private Const SV_TYPE_WFW As Long = &H2000
' Microsoft File and Print for NetWare
Private Const SV_TYPE_SERVER_MFPN As Long = &H4000
' Windows NT/Windows 2000 server that is not a domain controller
Private Const SV_TYPE_SERVER_NT As Long = &H8000
' Server that can run the browser service
Private Const SV_TYPE_POTENTIAL_BROWSER As Long = &H10000
' Server running a browser service as backup
Private Const SV_TYPE_BACKUP_BROWSER As Long = &H20000
' Server running the master browser service
Private Const SV_TYPE_MASTER_BROWSER As Long = &H40000
' Server running the domain master browser
Private Const SV_TYPE_DOMAIN_MASTER As Long = &H80000
Private Const SV_TYPE_SERVER_OSF As Long = &H100000
Private Const SV_TYPE_SERVER_VMS As Long = &H200000
' Windows 95 or later
Private Const SV_TYPE_WINDOWS As Long = &H400000
' Root of a DFS tree
Private Const SV_TYPE_DFS As Long = &H800000
'SV_TYPE_CLUSTER_NT Server clusters available in the domain
Private Const SV_TYPE_CLUSTER_NT As Long = &H1000000
' IBM DSS (Directory and Security Services) or equivalent
Private Const SV_TYPE_DCE As Long = &H10000000
' return list for alternate transport
Private Const SV_TYPE_ALTERNATE_XPORT As Long = &H20000000
' Servers maintained by the browser.Return local list only
Private Const SV_TYPE_LOCAL_LIST_ONLY As Long = &H40000000
' Primary domain
Private Const SV_TYPE_DOMAIN_ENUM As Long = &H80000000
' All servers. Handy for NetServerEnum2
Private Const SV_TYPE_ALL As Long = &HFFFFFFFF
Public Enum E_SVR_TYPE
ST_SV_TYPE_WORKSTATION = SV_TYPE_WORKSTATION
ST_SV_TYPE_SERVER = SV_TYPE_SERVER
ST_SV_TYPE_SQLSERVER = SV_TYPE_SQLSERVER
ST_SV_TYPE_DOMAIN_CTRL = SV_TYPE_DOMAIN_CTRL
ST_SV_TYPE_DOMAIN_BAKCTRL = SV_TYPE_DOMAIN_BAKCTRL
ST_SV_TYPE_TIME_SOURCE = SV_TYPE_TIME_SOURCE
ST_SV_TYPE_AFP = SV_TYPE_AFP
ST_SV_TYPE_NOVELL = SV_TYPE_NOVELL
ST_SV_TYPE_DOMAIN_MEMBER = SV_TYPE_DOMAIN_MEMBER
ST_SV_TYPE_PRINTQ_SERVER = SV_TYPE_PRINTQ_SERVER
ST_SV_TYPE_DIALIN_SERVER = SV_TYPE_DIALIN_SERVER
ST_SV_TYPE_XENIX_SERVER = SV_TYPE_XENIX_SERVER
ST_SV_TYPE_SERVER_UNIX = SV_TYPE_XENIX_SERVER
ST_SV_TYPE_NT = SV_TYPE_NT
ST_SV_TYPE_WFW = SV_TYPE_WFW
ST_SV_TYPE_SERVER_MFPN = SV_TYPE_SERVER_MFPN
ST_SV_TYPE_SERVER_NT = SV_TYPE_SERVER_NT
ST_SV_TYPE_POTENTIAL_BROWSER = SV_TYPE_POTENTIAL_BROWSER
ST_SV_TYPE_BACKUP_BROWSER = SV_TYPE_BACKUP_BROWSER
ST_SV_TYPE_MASTER_BROWSER = SV_TYPE_MASTER_BROWSER
ST_SV_TYPE_DOMAIN_MASTER = SV_TYPE_DOMAIN_MASTER
ST_SV_TYPE_SERVER_OSF = SV_TYPE_SERVER_OSF
ST_SV_TYPE_SERVER_VMS = SV_TYPE_SERVER_VMS
ST_SV_TYPE_WINDOWS = SV_TYPE_WINDOWS
ST_SV_TYPE_DFS = SV_TYPE_DFS
ST_SV_TYPE_CLUSTER_NT = SV_TYPE_CLUSTER_NT
ST_SV_TYPE_DCE = SV_TYPE_DCE
ST_SV_TYPE_ALTERNATE_XPORT = SV_TYPE_ALTERNATE_XPORT
ST_SV_TYPE_LOCAL_LIST_ONLY = SV_TYPE_LOCAL_LIST_ONLY
ST_SV_TYPE_DOMAIN_ENUM = SV_TYPE_DOMAIN_ENUM
ST_SV_TYPE_ALL = SV_TYPE_ALL
End Enum ' General definitions
Private Const ERROR_SUCCESS = 0
Private Const ERROR_MORE_DATA = 234
Private Const SIZE_SI_101 = 24 Private Type SERVER_INFO_101
dwPlatformID As Long
lpszServerName As Long
dwVersionMajor As Long
dwVersionMinor As Long
dwType As Long
lpszComment As Long
End Type Private Declare Function NetServerEnum Lib "netapi32.dll" ( _
ByVal ServerName As Long, _
ByVal level As Long, _
Buffer As Long, _
ByVal prefmaxlen As Long, _
entriesread As Long, _
totalentries As Long, _
ByVal ServerType As Long, _
ByVal domain As Long, _
resumehandle As Long) As Long Private Declare Function NetApiBufferFree Lib "netapi32.dll" ( _
bufptr As Any) As Long
Private Declare Sub CopyMemory Lib "kernel32" _
Alias "RtlMoveMemory" _
(hpvDest As Any, hpvSource As Long, _
ByVal cbCopy As Long)
Private Declare Function lstrlenW Lib "kernel32" _
(ByVal lpString As Long) As Long
Public Function GetNetServers(Optional ByVal nServerType _
As E_SVR_TYPE = SV_TYPE_ALL&, Optional ByVal szDomainName As String, Optional ErrStr As String) As Variant
'------------------------------------------------------------------------------
'函数名称:GetServers
'函数作用:根据条件获得Net上的计算机名(或服务器名)。
'参数描述:nServerType:需要获得某计算机的类型。
' szDomainName: 指定域或工作组名
' ErrStr: 失败时返回错误信息
'返回值: 变体字串数组,获得的网络计算机名称。
'------------------------------------------------------------------------------ Dim pszServer As Long, pszDomain As Long
Dim nLevel As Long, i As Long, bufptr As Long, TempBufPtr As Long
Dim nPrefMaxLen As Long, nEntriesRead As Long, nTotalEntries As Long
Dim nResumeHandle As Long, nRes As Long
Dim ServerInfo As SERVER_INFO_101
Dim RetValueStr As String
Dim lszDoMainb() As Byte, lngDoMainLen As Long
pszServer = 0&
If Len(szDomainName) = 0 Then
pszDomain = 0&
Else
lngDoMainLen = BSTRtoLPWSTR(szDomainName, lszDoMainb, pszDomain)
End If nLevel = 101
nPrefMaxLen = &HFFFFFFFF
Do
nRes = NetServerEnum(pszServer, nLevel, bufptr, _
nPrefMaxLen, nEntriesRead, nTotalEntries, _
nServerType, pszDomain, nResumeHandle)
If ((nRes = ERROR_SUCCESS) Or (nRes = ERROR_MORE_DATA)) And _
(nEntriesRead > 0) Then
TempBufPtr = bufptr
For i = 0 To nEntriesRead - 1
If Len(RetValueStr) <> 0 Then
RetValueStr = RetValueStr & ","
End If
CopyMemory ServerInfo, ByVal TempBufPtr, SIZE_SI_101
RetValueStr = RetValueStr & _
GetPointerToByteStringW(ServerInfo.lpszServerName)
TempBufPtr = TempBufPtr + SIZE_SI_101
Next i
Else
ErrStr = "NetServerEnum failed: " & nRes
End If
NetApiBufferFree (bufptr)
Loop While nEntriesRead < nTotalEntries
If Len(RetValueStr) <> 0 Then
GetNetServers = Split(RetValueStr, ",")
Else
GetNetServers = vbNullString
End If
End Function Private Function GetPointerToByteStringW(ByVal dwData As Long) As String
Dim tmp() As Byte
Dim tmplen As Long
If dwData <> 0 Then
tmplen = lstrlenW(dwData) * 2
If tmplen <> 0 Then
ReDim tmp(0 To (tmplen - 1)) As Byte
CopyMemory tmp(0), ByVal dwData, tmplen
GetPointerToByteStringW = tmp
End If
End If
End Function
Function BSTRtoLPWSTR(sBSTR As String, b() As Byte, lpwsz As Long) As Long ' Input: a nonempty BSTR string
' Input: **undimensioned** byte array b()
' Output: Fills byte array b() with Unicode char string from sBSTR
' Output: Fills lpwsz with a pointer to b() array
' Returns byte count, not including terminating 2-byte Unicode null character
' Original BSTR is not affected
Dim cBytes As Long
cBytes = LenB(sBSTR)
' ReDim array, with space for terminating null
ReDim b(1 To cBytes + 2) As Byte
' Point to BSTR char array
lpwsz = StrPtr(sBSTR)
' Copy the array
CopyMemory b(1), ByVal lpwsz, cBytes + 2
' Point lpsz to new array
lpwsz = VarPtr(b(1))
' Return byte count
BSTRtoLPWSTR = cBytes
End Function
'在窗体中可增加以下代码:
'Private Sub Command1_Click()
' Dim ClsLstSvr As New ClsListServer
' Dim StrNetSvrs As Variant
' Dim ErrStr As String
' Dim i As Integer
' Dim sDomainName As String
'
' sDomainName = "YourDoMainName"
'
' StrNetSvrs = ClsLstSvr.GetNetServers(, sDomainName, ErrStr)
'
' If IsArray(StrNetSvrs) Then
' For i = 0 To UBound(StrNetSvrs)
' Combo1.AddItem StrNetSvrs(i)
' Next i
' End If
'End Sub致于要获取数据库
则要用缺省密码打开
Master
库
然后用
“Select Name from databases”
列出所有数据库。
但如不知密码仍不能访问
所以这无必要!
--得到Server列表
insert into tmp
Exec master..xp_cmdshell 'osql -L'
--篩選資料
Select ltrim(servername) as servername From tmp where servername is not null and servername<>'Servers:'
这句中的“..”和xp_cmdshell 还有'osql -L'个是什么意思!?