Get several ways to get lists of SQL Server Server

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