2,722
社区成员
发帖
与我相关
我的任务
分享
枚举局域网内所有的 SQL Server 服务器实例名
*---------------------------------------
by:dkfdtf
出处:http://blog.csdn.net/dkfdtf/archive/2009/02/02/3859150.aspx
如果希望检查一个指定名称的 SQL Server 服务器是否存在,或希望给用户一个自己挑选需要连接的 SQL 服务器的话,就需要先枚举出局域网内所有的 SQL Server 服务器实例名称。我所知的方法有三种:
-----------------------------------------------------------------------------------------
1. 使用 SQLDMO 对象的 ListAvailableSQLServers 方法
loSqlDMO=Createobject("SQLDMO.Application")
loSvrs=loSqlDMO.ListAvailableSQLServers()
lnSvrsCount=loSvrs.Count
If lnSvrsCount=0
?"没有找到可用的 SQL Server 服务器。"
Else
?"可连接的 SQL Server 服务器:"+Transform(lnSvrsCount)+"个,分别为:"
For Each lcSvr In loSvrs
?lcSvr
Endfor
Endif
Release loSqlDMO
此方法最简单,功能也完整,可以枚举出缺省和非缺省实例名的所有 SQL Server 实例。缺点是运行代码的机器上必须安装了 sqldmo 对象组件,如果机器上已安装了 sql server,或者你自行安装了这个组件自然没问题,然而如果是一普通的客户端,它只需要连接到 SQL 服务器上取数据,或者你只是想在安装 SQL Server 前检查安装环境,则可能因为没有这个组件而无法达到目的。
-----------------------------------------------------------------------------------------
2. 使用 api 函数 NetServerEnum 枚举指定类型的服务器
#Define MAX_PREFERRED_LENGTH -1
#Define SV_TYPE_SQLSERVER 0x00000004
Declare Long NetServerEnum In netapi32 ;
string ServerName, Long nlevel, Long @ bufptr, Long prefmaxlen, ;
long @ entriesread, Long @ totalentries, Long servertype, ;
string domain, Long resume_handle
Declare Long NetApiBufferFree In netapi32 Long bufptr
Declare Long lstrlenW In win32api String @ lpString
Store 0 To lnBuff, lnReadNums, lnTotNums
If 0 == NetServerEnum( Null, 100, @ lnBuff, MAX_PREFERRED_LENGTH, ;
@ lnReadNums, @ lnTotNums, SV_TYPE_SQLSERVER, Null, 0) ;
and lnReadNums > 0
? '找到的 SQL Server 实例:'
For ii = 1 To lnReadNums
m.lnAddr = CToBin(Sys(2600, m.lnBuff + (m.ii-1)*8 + 4, 4), 'rs')
m.lcSrv = Sys(2600, m.lnAddr, 256)
? Left(Strconv(m.lcSrv, 6), lstrlenW(m.lcSrv))
Endfor
Else
? '没有找到可用的 SQL Server 实例。'
Endif
If lnBuff > 0
NetApiBufferFree(lnBuff)
Endif
这只是一段简单的代码,没有任何错误处理。这个 api 函数也可以检测出所有可用(服务启动)的 SQL Server 服务器,优点是速度快,不需要安装任何附加的组件,缺点是无法列出非缺省安装时的实例名,而且有时 SQL 服务启动后要很久才能探测到。
-----------------------------------------------------------------------------------------
3. 使用 ODBC api 函数枚举指定类型的服务器
#Define SQL_HANDLE_ENV 1
#Define SQL_HANDLE_DBC 2
#Define SQL_NULL_HANDLE 0
#Define SQL_SUCCESS 0
#Define SQL_SUCCESS_WITH_INFO 1
#Define SQL_NEED_DATA 99
#Define SQL_ERROR -1
#Define SQL_INVALID_HANDLE -2
#Define SQL_ATTR_ODBC_VERSION 200
#Define SQL_OV_ODBC3 3
#Define SQL_NTS -3
#Define ENUM_SERVERS_MAX_RET_LENGTH -1
lcDrvName = 'Driver={SQL Server}'
lcLookUpKey = 'SERVER:Server='
Declare short SQLAllocHandle In odbc32 ;
short HandleType, Long InputHandle, Long @ OutputHandlePtr
Declare short SQLSetEnvAttr In odbc32 ;
long EnvironmentHandle, Long nAttribute, Long ValuePtr, Long StringLength
Declare short SQLBrowseConnect In odbc32 ;
long ConnectionHandle, String InConnectionString, short StringLength1, ;
String @ OutConnectionString, short BufferLength, short @ StringLength2Ptr
Declare short SQLDisconnect In odbc32 As _SQLDisconnect ;
long ConnectionHandle
Declare short SQLFreeHandle In odbc32 ;
long HandleType, Long Handle
* Allocate the environment handle
hSQLEnv = 0
lnResult = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, @ hSQLEnv)
If (lnResult == SQL_SUCCESS Or lnResult == SQL_SUCCESS_WITH_INFO)
* Set the environment attribute to SQL_OV_ODBC3
lnResult = SQLSetEnvAttr(hSQLEnv, SQL_ATTR_ODBC_VERSION, SQL_OV_ODBC3, 0)
If (lnResult == SQL_SUCCESS Or lnResult == SQL_SUCCESS_WITH_INFO)
* Allocate a connection handle
hSQLHdbc = 0
lnResult = SQLAllocHandle(SQL_HANDLE_DBC, hSQLEnv, @ hSQLHdbc)
If (lnResult == SQL_SUCCESS Or lnResult == SQL_SUCCESS_WITH_INFO)
szConnStrOut = Replicate(Chr(0), 256)
lnLen = 0
* Call SQLBrowseConnect for additional information
lnResult = SQLBrowseConnect( ;
hSQLHdbc, lcDrvName, SQL_NTS, ;
@ szConnStrOut, 256, @ lnLen)
If (lnResult == SQL_SUCCESS Or lnResult == SQL_NEED_DATA)
ListSqlServersList(Left(szConnStrOut, lnLen), lcLookUpKey)
Endif
_SQLDisconnect(hSQLHdbc)
Endif
SQLFreeHandle(SQL_HANDLE_DBC, hSQLHdbc)
Endif
Endif
SQLFreeHandle(SQL_HANDLE_ENV, hSQLEnv)
Function ListSqlServersList(tcConnectString, tcLookUpKey)
Local ii, lcSvrs, lcSvr, lcMsg, laSvrs[1]
For m.ii = 1 To Getwordcount(tcConnectString, ';') + 1
lcSvrs = Getwordnum(tcConnectString, m.ii, ';')
If Atc(tcLookUpKey, lcSvrs) > 0
Exit
Else
lcSvrs = ''
Endif
Endfor
lcSvrs = Strextract(lcSvrs, '{', '}')
If Alines(laSvrs, lcSvrs, 1+4, ',') > 0
lcMsg = '找到下面的 SQL Server 实例:'
For m.ii = 1 To Alen(laSvrs)
lcMsg = lcMsg + Chr(13) + Transform(m.ii) + Chr(9) + laSvrs[m.ii]
Endfor
Messagebox(lcMsg)
Else
Messagebox('没有找到可用的 SQL Server 实例。')
Endif
Endfunc
#Define SQL_HANDLE_ENV 1
#Define SQL_HANDLE_DBC 2
#Define SQL_NULL_HANDLE 0
#Define SQL_SUCCESS 0
#Define SQL_SUCCESS_WITH_INFO 1
#Define SQL_NEED_DATA 99
#Define SQL_ERROR -1
#Define SQL_INVALID_HANDLE -2
#Define SQL_ATTR_ODBC_VERSION 200
#Define SQL_OV_ODBC3 3
#Define SQL_NTS -3
#Define ENUM_SERVERS_MAX_RET_LENGTH -1
lcDrvName = 'Driver={SQL Server}'
lcLookUpKey = 'SERVER:Server='
Declare short SQLAllocHandle In odbc32 ;
short HandleType, Long InputHandle, Long @ OutputHandlePtr
Declare short SQLSetEnvAttr In odbc32 ;
long EnvironmentHandle, Long nAttribute, Long ValuePtr, Long StringLength
Declare short SQLBrowseConnect In odbc32 ;
long ConnectionHandle, String InConnectionString, short StringLength1, ;
String @ OutConnectionString, short BufferLength, short @ StringLength2Ptr
Declare short SQLDisconnect In odbc32 As _SQLDisconnect ;
long ConnectionHandle
Declare short SQLFreeHandle In odbc32 ;
long HandleType, Long Handle
* Allocate the environment handle
hSQLEnv = 0
lnResult = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, @ hSQLEnv)
If (lnResult == SQL_SUCCESS Or lnResult == SQL_SUCCESS_WITH_INFO)
* Set the environment attribute to SQL_OV_ODBC3
lnResult = SQLSetEnvAttr(hSQLEnv, SQL_ATTR_ODBC_VERSION, SQL_OV_ODBC3, 0)
If (lnResult == SQL_SUCCESS Or lnResult == SQL_SUCCESS_WITH_INFO)
* Allocate a connection handle
hSQLHdbc = 0
lnResult = SQLAllocHandle(SQL_HANDLE_DBC, hSQLEnv, @ hSQLHdbc)
If (lnResult == SQL_SUCCESS Or lnResult == SQL_SUCCESS_WITH_INFO)
szConnStrOut = Replicate(Chr(0), 256)
lnLen = 0
* Call SQLBrowseConnect for additional information
lnResult = SQLBrowseConnect( ;
hSQLHdbc, lcDrvName, SQL_NTS, ;
@ szConnStrOut, 256, @ lnLen)
If (lnResult == SQL_SUCCESS Or lnResult == SQL_NEED_DATA)
ListSqlServersList(Left(szConnStrOut, lnLen), lcLookUpKey)
Endif
_SQLDisconnect(hSQLHdbc)
Endif
SQLFreeHandle(SQL_HANDLE_DBC, hSQLHdbc)
Endif
Endif
SQLFreeHandle(SQL_HANDLE_ENV, hSQLEnv)
Function ListSqlServersList(tcConnectString, tcLookUpKey)
Local ii, lcSvrs, lcSvr, lcMsg, laSvrs[1]
For m.ii = 1 To Getwordcount(tcConnectString, ';') + 1
lcSvrs = Getwordnum(tcConnectString, m.ii, ';')
If Atc(tcLookUpKey, lcSvrs) > 0
Exit
Else
lcSvrs = ''
Endif
Endfor
lcSvrs = Strextract(lcSvrs, '{', '}')
If Alines(laSvrs, lcSvrs, 1+4, ',') > 0
lcMsg = '找到下面的 SQL Server 实例:'
For m.ii = 1 To Alen(laSvrs)
lcMsg = lcMsg + Chr(13) + Transform(m.ii) + Chr(9) + laSvrs[m.ii]
Endfor
Messagebox(lcMsg)
Else
Messagebox('没有找到可用的 SQL Server 实例。')
Endif
Endfunc
这个 ODBC api 函数族兼顾了方法1和2的部分优点,同样不需要任何附加的安装组件,同时又可以列出所有缺省和非缺省安装的实例名,只是速度跟方法 1 差不多。