Title: Listing MS SQL Servers using LanManager API
Question: How do I list all MS SQL Servers in the network?
Approach #1
Answer:
There are several approaches to enumerating Microsoft SQL Servers in a network, in this site there are several articles on the subject, each uses an interesting technique article 3812 uses SQL ping, a technique that will get all MS SQL servers within a broadcast domain (ie those inside the enterprise network), this method is by far the best way to get most sql servers, another technique is through the use of SQL-DMO as illustrated by article 4052, SQL-DMO is part of MS SQL Server native API, the problem of using this technique is that if you run the program in a machine that doesnt have MS SQL server, MSDE (SQL Desktop Edition) or SQL Express you cant use SQL DMO, what this article purposes relies on the so called LAN Manager API (wich is a part of Windows itself)
The proyect form contains one button and one Memo, all API functions, records and constants needed are declared in the implementation part of the unit, before someone complains about the function not listing SQL server X, the LanManager API searches only within the domain, so the function will only search all SQL Servers within the windows domain, as a final note there is even yet another way to list MS SQL servers in a network: Using the SQLBrowseConnect api wich is part of the ODBC api, It uses something similar to SQL Ping behind the scenes
unit Unit1;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, StdCtrls;
type
TForm1 = class(TForm)
Memo1: TMemo;
Button1: TButton;
procedure Button1Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;
var
Form1: TForm1;
implementation
{$R *.dfm}
const
// Property TNTServerInfo.ServerType Mask of following values
SV_TYPE_UNKNOWN = 0;
SV_TYPE_WORKSTATION = $00000001;
SV_TYPE_SERVER = $00000002;
SV_TYPE_SQLSERVER = $00000004;
SV_TYPE_DOMAIN_CTRL = $00000008;
SV_TYPE_DOMAIN_BAKCTRL = $00000010;
SV_TYPE_TIME_SOURCE = $00000020;
SV_TYPE_AFP = $00000040;
SV_TYPE_NOVELL = $00000080;
SV_TYPE_DOMAIN_MEMBER = $00000100;
SV_TYPE_PRINTQ_SERVER = $00000200;
SV_TYPE_DIALIN_SERVER = $00000400;
SV_TYPE_SERVER_UNIX = $00000800;
SV_TYPE_NT = $00001000;
SV_TYPE_WFW = $00002000;
SV_TYPE_SERVER_MFPN = $00004000;
SV_TYPE_SERVER_NT = $00008000;
SV_TYPE_POTENTIAL_BROWSER = $00010000;
SV_TYPE_BACKUP_BROWSER = $00020000;
SV_TYPE_MASTER_BROWSER = $00040000;
SV_TYPE_DOMAIN_MASTER = $00080000;
SV_TYPE_SERVER_OSF = $00100000;
SV_TYPE_SERVER_VMS = $00200000;
SV_TYPE_WINDOWS = $00400000;
SV_TYPE_DFS = $00800000;
SV_TYPE_ALTERNATE_XPORT = $20000000;
SV_TYPE_LOCAL_LIST_ONLY = $40000000;
SV_TYPE_DOMAIN_ENUM = $80000000;
SV_TYPE_ALL = $FFFFFFFF;
type
PServerInfo101 = ^TServerInfo101;
_SERVER_INFO_101 = record
sv101_platform_id: DWORD;
sv101_name: LPWSTR;
sv101_version_major: DWORD;
sv101_version_minor: DWORD;
sv101_type: DWORD;
sv101_comment: LPWSTR;
end;
TServerInfo101 = _SERVER_INFO_101;
function NetServerEnum(servername: LPCWSTR; level: DWORD; var bufptr: Pointer;
prefmaxlen: Integer; var entriesread: DWORD; var totalentries: DWORD;
servertype: DWORD; domain: LPCWSTR; var resume_handle: DWORD): DWORD;
stdcall; external 'netapi32.dll' name 'NetServerEnum';
procedure ListSQLServers( aList: TStrings );
var
i,E,ResumeHandle,
TotalEntries,EntriesRead : DWORD;
pServerBuffer : pointer;
pIncBuff : PServerInfo101;
begin
pServerBuffer := nil;
ResumeHandle := 0;
E := NetServerEnum( nil, 101, pServerBuffer, -1, EntriesRead, TotalEntries,
SV_TYPE_SQLSERVER, nil, ResumeHandle);
if E = 0 then
begin
aList.Clear;
pIncBuff := pServerBuffer;
for i := 1 to EntriesRead do
begin
aList.Add( WideCharToString( pIncBuff.sv101_name) );
inc(pIncBuff);
end;
end;
end;
procedure TForm1.Button1Click(Sender: TObject);
begin
ListSQLServers( Memo1.Lines );
end;
end.