Get several ways to get lists of SQL Server Server
First, SQL DMO
Description: SQL Distributed Management Objects (SQL Distributed Management Object), exists in the sqldmo.dll file, actually a COM object, obtained by calling the ListAvailableSqlServers method of SQL DMO.
List Type: Enumerates a computer with "client" and "server".
Applicable conditions: With SQL Server, there are SQLDMo.dll files.
Speed:
Call example: getsqlserverlist (listbox1.items);
Code:
Uses
Comobj;
Function GetsqlServerList (VAR Alist: Tstrings): Boolean;
VAR
SQLSERVERAPP: Variant;
ServerList: Variant;
i: integer;
Begin
RESULT: = TRUE;
Try
SQLSerVerapp: = CreateoleObject ('sqldmo.application');
ServerList: = SQLSERVERAPP.ListavailableSqlServers;
For i: = 1 to ServerList.count DO
Alist.add (ServerList.Item (i));
SQLSERVERAPPP: = UNASSIGNED;
ServerList: = UNASSIGNED;
Except
Result: = FALSE;
END;
END;
Second, NetServereNum
Description: Network service function, existing in the NetApi32.dll file; get a list of computer with the SQL Server server through the NetServereNum function, and only the computer with the SQL Server client will not be listed; if a computer's SQL Server The service just started, then this function will take the computer for a long time.
List type: Only a computer with "server" is listed.
Applicable conditions: There are Netapi32.dll files.
high speed
Call example: getsqlserverlist (listbox1.items);
Code:
Type
NET_API_STATUS = DWORD;
PSERVERINFO100 = ^ TSERVERINFO100;
_Server_info_100 = Record
SV100_PLATFORM_ID: DWORD;
SV100_NAME: LPWSTR;
END;
{$ Extealsym _server_info_100}
TSERVERINFO100 = _SERVER_INFO_100;
Server_INFO_100 = _SERVER_INFO_100;
{$ ExternalSym Server_INFO_100}
Const
Nerr_success = 0;
MAX_PREFERRED_LENGTH = DWORD (-1);
SV_TYPE_SQLSERVER = $ 00000004;
Function NetapiFFeralLocate (Bytecount: DWORD; VAR BUFFER: POINTER):
NET_API_STATUS; stdcall; external 'Netapi32.dll' Name 'Netapibuff &Buff &BuffRoccate'; Function NetServereNum (ServerName: lpcwstr; Level: DWORD; var BUFPTR: POINTER
PREFMAXLEN: DWORD; VAR ENTRIESREAD: DWORD; VAR TOTALENTRIES: DWORD
Servertype: DWORD; DOMAIN: LPCWSTR; ResumeHandle: PDWORD: NET_API_STATUS;
STDCALL; External 'Netapi32.dll' Name 'NetServereNum';
Function NetapiFFERFree (Buffer: Pointer): NET_API_STATUS; stdcall; External
'Netapi32.dll' Name 'Netapibufferfree';
Function GetsqlServerList (VAR Alist: Tstrings; PWCServerName: pwchar = nil;
PWCDOMAIN: PWCHAR = NIL): boolean;
VAR
Netapistatus: DWORD;
DWLEVEL: DWORD;
PRETURnsVRINFO: POINTER;
DWPREFMAXLEN: DWORD;
DWENTRIESREAD: DWORD;
DWTOTALENTRIES: DWORD;
DWSERVERTYPE: DWORD;
DWRESUMEHANDLE: PDWORD;
PCURSVRINFO: PSERVERINFO100;
I, J: Integer;
Begin
RESULT: = TRUE;
Try
DWLEVEL: = 100;
PRETURnsvrinfo: = nil;
DWPREFMAXLEN: = max_preferred_length;
DWENTRIESREAD: = 0;
DWTOTALENTRIES: = 0;
DWSERVERTYPE: = SV_TYPE_SQLSERVER; // Server type
DWRESUMEHANDLE: = NIL;
NetapibufferalLocate (SizeOf (Preturnsvrinfo), Preturnsvrinfo;
Try
NetaPistatus: = netserverenum (PWCserverName, dwlevel, preturnsvrinfo,
DWPREFMAXLEN, DWENTRIESREAD, DWTOTALENTRIES, DWSERVERTYPE, PWCDOMAIN,
dwresumehandle;
IF (NetApistatus = Nerr_Success) or (NetaPistatus = Error_More_Data)) and
(preturnsvrinfo <> nil) THEN
Begin
Pcursvrinfo: = PRETURNSVRINFO;
// Loop acquire all SQL Server servers
I: = 0;
J: = DWENTRIESREAD;
While i
Begin
IF pcursvrinfo = nil dam
Break;
WITH ALIST DO
Add (pcursvrinfo ^ .sv100_name);
INC (I);
INC; PCURSVRINFO;
END;
END;
Finally
IF Assigned (Preturnsvrinfo) THEN
Netapibufferfree (PRETURnsVRINFO);
END;
Except
Result: = FALSE;
END;
END;
Third, SQLBROWSECONNECT
Description: ODBC function (Microsoft Open Database Connectivity, open database connection), exists in the odbc32.dll file; returns to the connection string information through the SQLBROWSECONNECT function, including DSN, Driver, Server, UID, PWD, App, WSID, DATABASE , Language and other information. In the function getoDBCINFO, ITSERVER, ITDATABASE, ITLANGUAGE can obtain information lists such as "Server", "Database", "Language", where ITDATABASE, ITLANGUAGE defaults to local information, take remote information, please modify "DRIVER = {SQL" Server}; server = (local); UID = SA; PWD = 'connection string.
List Type: Enumerates a computer with "client" and "server".
Applicable conditions: Due to MDAC 2.6, 2.6 SP1, 2.7 and Microsoft ODBC Driver For SQL Server 2000 2000.80.194 has bugs, this function cannot obtain Microsoft SQL Server 7.0 servers in these versions.
Speed:
Call example: getoDBCINFO (ListBox1.Items, ITSERVER);
Code:
Type
Tinfotype = (Itserver, ITDATABASE, ITLANGUAGE);
SQLHANDLE = POINTER;
Sqlsmallint = short;
SQLINTEGER = Longint;
Psqlhandle = ^ SQLHANDLE;
Sqlhenv = SQLHANDLE;
SQLHDBC = SqlHandle;
SQLRETURN = SQLSMALLINT;
Sqlchar = uchar;
Psqlchar = ^ SQLCHAR;
Sqlpointer = POINTER;
Psqlsmallint = ^ Sqlsmallint;
Function SQLallochandle (Handletype: Sqlsmallint; InputHandle: SqlHandle;
OutputHandle: psqlhandle: SQLRETURN; stdcall; external 'odbc32.dll' Name
'Sqlallochandle';
Function SQLSETENVATTR (EnvironmentHandle: SQLhenV; Attribute: Sqlinteger;
Value: SQLPointer; StringLength: SQLINTEGER: SQLRETURN; stdcall; External
'odbc32.dll' name 'sqlsetenvattr';
Function SQLBROWSECONNECT (HDBC: SQLHDBC; SZConnstrin: psqlchar;
CBConnstrin: sqlsmallint; szconnstrout: psqlchar;
CBConnStrOMax: Sqlsmallint; pcbconnstrout: psqlsmallint): Sqlreturn; stdcall; external 'odbc32.dll' name 'sqlbrowseconnect';
Function Sqldisconnect (ConnectionHandle: SQLHDBC): SQLRETURN; stdcall; External
'odbc32.dll' name 'sqldisconnect';
Function SQLFreeHandle (Handletype: Sqlsmallint; Handle: SQLHANDLE): SQLRETURN;
STDCALL; External 'odbc32.dll' name 'sqlfreehandle';
Const
SQL_HANDLE_ENV = 1;
SQL_HANDLE_DBC = 2;
SQL_NULL_HANDLE = longint (0);
SQL_SUCCESS = 0;
SQL_ERROR = -1;
SQL_ATTR_ODBC_VERSION = 200;
SQL_OV_ODBC3 = ULONG (3);
SQL_NTS = -3;
Function Get 2BCINFO (VAR Alist: Tstrings; Infotype: Tinfotype): Boolean
Const
ConnStrOMax = 4824;
Splitterstr = '= {';
VAR
Henv: sqlhenv;
HDBC: SQLHDBC;
Retcode: sqlreturn;
Connstrout: psqlchar;
CBConnSTrout: sqlsmallint;
Connstrin, TMPSTR: STRING
TMPPOS: Integer;
Begin
Case Infotype of
ITSERVER: CONNSTIN: = 'Driver = {SQL Server}'
ITDATABASE, ITLANGUAGE: Connstrin: = 'driver = {SQL Server}; server = (local); UID = SA; PWD =';
END;
Result: = FALSE;
Try
// Assign an ODBC environment handle
Retcode: = sqlallochandle (SQL_HANDLE_ENV, SQLPOINTER (SQL_NULL_HANDLE), @henv);
if retcode = SQL_ERROR THEN
EXIT;
/ / Set ODBC version
Retcode: = SQLSETENVATTR (HENV, SQL_ATTR_ODBC_VERSION, SQLPOINTER (SQL_OV_ODBC3), 0);
IF RETCODE <> SQL_SUCCESS THEN
EXIT;
// Assign database connection handle
Retcode: = sqlallochandle (SQL_HANDLE_DBC, HENV, @HDBC);
IF RETCODE <> SQL_SUCCESS THEN
EXIT;
GetMem (Connstrout, ConnStrOMax);
Retcode: = SQLBROWSECONNECT (HDBC, Psqlchar (Connstrin), SQL_NTS, ConnStrout, ConnStrOMax, @cbconnstrout;
IF RETCODE <> SQL_ERROR THEN
Begin
Tmpstr: = pchar (connStrout);
IF infictype = itlanguage the
DELETE (Tmpstr, 1, ANSIPOS ('};', tmpstr) 1);
DELETE (Tmpstr, 1, ANSIPOS (Splitterstr, Tmpstr) 1);
Delete (Tmpstr, Ansipos ('}', tmpstr), Length (Tmpstr));
While Tmpstr <> 'DO
Begin
TMPPOS: = ANSIPOS (', "TMPSTR);
IF TMPPOS> 0 THEN
Alist.add (Copy (Tmpstr, 1, TMPPOS - 1))
Else
Begin
Alist.Add (Tmpstr);
Tmpstr: = '';
END;
Delete (Tmpstr, 1, TMPOS)
END;
RESULT: = TRUE;
END;
FreeMem (ConnStrout, ConnStroutmax);
Finally
IF assigned (hdbc) THEN
Begin
Sqldisconnect (HDBC);
SQLFreeHandle (SQL_HANDLE_DBC, HDBC);
HDBC: = NIL;
END;
IF assigned (henv) THEN
Begin
SQLFreeHandle (SQL_HANDLE_ENV, HENV);
Henv: = NIL;
END;
END;
END;
Zhang Wei (alan) alan@cnvcl.org
