获取SQL Server服务器列表的几种方法

时间：2021-03-09 17:37:17

获取SQL Server服务器列表的几种方法

 

一、      SQL DMO

描述：SQL Distributed Management Objects（SQL分布式管理对象），存在于SQLDMO.dll文件中，实际上是一个COM 对象，通过调用SQL DMO的ListAvailableSQLServers方法取得。

列表类型：列举装有“客户端”和“服务端”的计算机。

适用条件：装有 SQL Server，且有SQLDMO.dll文件。

速度：中

调用示例：GetSQLServerList(ListBox1.items);

代码：

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));

    SQLServerApp := Unassigned;

    ServerList := Unassigned;

  except

    Result := False;

  end;

end;

 

二、      NetServerEnum

描述：网络服务函数，存在于NetApi32.dll文件中；通过NetServerEnum函数可取得装有SQL Server服务端的计算机列表，只装有SQL Server客户端的计算机将不会被列举其中；如果一台计算机的SQL Server服务刚刚启动，那么此函数将会过很久才能取到该计算机。

列表类型：仅列举装有“服务端”的计算机。

适用条件：有NetApi32.dll文件。

速度：快

调用示例：GetSQLServerList(ListBox1.items);

代码：

type

  NET_API_STATUS = DWORD;

 

  PServerInfo100 = ^TServerInfo100;

  _SERVER_INFO_100 = record

    sv100_platform_id: DWORD;

    sv100_name: LPWSTR;

  end;

  {$EXTERNALSYM _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 NetApiBufferAllocate(ByteCount: DWORD; var Buffer: Pointer):

  NET_API_STATUS; stdcall; external 'netapi32.dll' name 'NetApiBufferAllocate';

 

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 NetApiBufferFree(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;    //服务器类型

    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;

 

        // 循环取得所有SQL Server服务器

        i := 0;

        j := dwEntriesRead;

        while i < j do

        begin

          if pCurSvrInfo = nil then

            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;

 

三、      SQLBrowseConnect

描述：ODBC函数（Microsoft Open Database Connectivity，开放式数据库连接），存在于odbc32.dll文件中；通过SQLBrowseConnect函数可返回连接字符串信息，包括DSN、DRIVER、SERVER、UID、PWD、APP、WSID、DATABASE、LANGUAGE等信息。在函数GetODBCInfo 中传入itServer、itDatabase、itLanguage可分别取得“服务器”、“数据库”及“语言”等信息列表，其中itDatabase、itLanguage默认取本地信息，取远程信息请自行修改“'Driver={SQL Server};SERVER=(local);UID=sa;PWD='”连接字符串。

列表类型：列举装有“客户端”和“服务端”的计算机。

适用条件：由于MDAC 2.6 、2.6 SP1、2.7和Microsoft ODBC Driver for SQL Server 2000 2000.80.194有Bug，因此在这些版本中此函数无法取得Microsoft SQL Server 7.0的服务器。

速度：中

调用示例：GetODBCInfo(ListBox1.items, itServer);

代码：

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;

  cbConnStrOutMax: 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 GetODBCInfo(var AList: TStrings; InfoType: TInfoType): Boolean;

const

  ConnStrOutMax = 4824;

  SplitterStr = '={';

var

  HENV: SQLHENV;

  HDBC: SQLHDBC;

  RetCode: SQLRETURN;

  ConnStrOut: PSQLCHAR;

  cbConnStrOut: SQLSMALLINT;

 

  ConnStrIn, TmpStr: string;

  TmpPos: Integer;

begin

  case InfoType of

    itServer: ConnStrIn := 'Driver={SQL Server}';

    itDatabase, itLanguage: ConnStrIn := 'Driver={SQL Server};SERVER=(local);UID=sa;PWD=';

  end;

 

  Result := False;

  try

    // 分配 ODBC 环境句柄

    RetCode := SQLAllocHandle(SQL_HANDLE_ENV, SQLPOINTER(SQL_NULL_HANDLE), @HENV);

    if RetCode = SQL_ERROR then

      Exit;

 

    // 设置 ODBC 版本

    RetCode := SQLSetEnvAttr(HENV, SQL_ATTR_ODBC_VERSION, SQLPointer(SQL_OV_ODBC3), 0);

    if RetCode <> SQL_SUCCESS then

      Exit;

 

    // 分配数据库连接句柄

    RetCode := SQLAllocHandle(SQL_HANDLE_DBC, HENV, @HDBC);

    if RetCode <> SQL_SUCCESS then

      Exit;

 

    GetMem(ConnStrOut, ConnStrOutMax);

    RetCode := SQLBrowseConnect(HDBC, PSQLCHAR(ConnStrIn), SQL_NTS, ConnStrOut,

      ConnStrOutMax, @cbConnStrOut);

 

    if RetCode <> SQL_ERROR then

    begin

      TmpStr := PChar(ConnStrOut);

 

      if InfoType = itLanguage then

        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, TmpPos)

      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;

                                                        张伟(Alan) Alan@cnvcl.org

标签：

相关文章