Get list of available SQL Servers? 1

[SteveDingle]

Heya All,

Working to add more functionality to my "login form" and I wanted to provide a list of SQL Servers (Server names that I can use in a connection string) that are available to the PC.

Can anyone point me to a method to do that?

Also, not sure if it's possible to get without actually connecting, but taking it a bit further, maybe get a list of available users/logins..



Toodles,
Steve Dingle

http://www.dsbusinesssolutions.co.uk

D&S Business Solutions Ltd

 

[bborissov]

Check NetServerEnum() API function in MSDN and depending of your FrontEnd use it.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[Zografski]

Check
-

http://sqldev.net/misc/ListSQLSvr.htm

-

http://sqldev.net/misc/OleDbEnum.htm

-

http://sqldev.net/misc/EnumSQLSvr.htm

 

[AndyKr]

Hi Steve

Try this one....

********************************************************************
*** Name.....: ENUMERATESERVERS.PRG
*** Author...: Andy Kramek & Marcia Akins
*** Date.....: 04/27/2006
*** Notice...: Copyright (c) 2006 Tightline Computers, Inc
*** Compiler.: Visual FoxPro 09.00.0000.3504 for Windows
*** Function.: Enumerate various server types on the networrk
********************************************************************
CREATE CURSOR csResult ( platformid N(12), servername C(30),;
majorver N(12), minorver N(12), softtype N(12), instancename C(250))

EnumerateServers()
BROW NORMAL

FUNCTION EnumerateServers()
DO DeclareAPI

*** Define network machine/server types
#DEFINE SV_TYPE_WORKSTATION 1
#DEFINE SV_TYPE_SERVER 2
#DEFINE SV_TYPE_SQLSERVER 4
#DEFINE SV_TYPE_DOMAIN_CTRL 8
#DEFINE SV_TYPE_PRINTQ_SERVER 0x200
#DEFINE SV_TYPE_SERVER_UNIX 0x800
#DEFINE SV_TYPE_SERVER_NT 0x8000
#DEFINE SV_TYPE_DOMAIN_MASTER 0x80000
#DEFINE SV_TYPE_WINDOWS 0x400000
#DEFINE SV_TYPE_ALL 0xFFFFFFFF

*** Can get any of the machine types with this API Call
= EnumServers (SV_TYPE_SQLSERVER)
*** = EnumServers (SV_TYPE_PRINTQ_SERVER)
*** = EnumServers (SV_TYPE_SERVER)
*** = EnumServers (SV_TYPE_SERVER_NT)
*** = EnumServers (SV_TYPE_DOMAIN_CTRL)
*** = EnumServers (SV_TYPE_ALL)
RETURN

PROCEDURE EnumServers (lnServerType)
#DEFINE MAX_PREFERRED_LENGTH -1
#DEFINE SINFO_101_SIZE 24
* typedef struct _SERVER_INFO_101 {
* DWORD sv101_platform_id; 4
* LPWSTR sv101_name; 4
* DWORD sv101_version_major; 4
* DWORD sv101_version_minor; 4
* DWORD sv101_type; 4
* LPWSTR sv101_comment; 4
* } SERVER_INFO_101, *PSERVER_INFO_101, *LPSERVER_INFO_101;

LOCAL lnBuffer, lnCountRead, lnCountTotal, lnResult
STORE 0 TO lnBuffer, lnCountRead, lnCountTotal

lnResult = NetServerEnum (0, 101, @lnBuffer, MAX_PREFERRED_LENGTH, ;
@lnCountRead, @lnCountTotal, lnServerType, 0, 0)

IF lnResult <> 0
RETURN ""
ELSE
LOCAL lcBuffer, lnBufLen, lnEntry, lnPlatformId, lnNamePtr,;
lnMajorVer, lnMinorVer, lnSofttype, lnMemoPtr,lcServerName, lcServerMemo

*** Convert Struct to array
lnBufLen = lnCountRead * SINFO_101_SIZE
lcBuffer = Repli (Chr(0), lnBufLen)
Heap2String(@lcBuffer, lnBuffer, lnBufLen)

*** Process the result to extract each "record"
FOR lnEntry = 1 TO lnCountRead
*** This is one 'record'
lcServerInfo = SUBSTR (lcBuffer, (lnEntry-1) * SINFO_101_SIZE + 1, SINFO_101_SIZE )
*** Now chop it up...
lnPlatformId = buf2dword (SUBSTR (lcServerInfo, 1,4))
lnNamePtr = buf2dword (SUBSTR (lcServerInfo, 5,4))
lnMajorVer = buf2dword (SUBSTR (lcServerInfo, 9,4))
lnMinorVer = buf2dword (SUBSTR (lcServerInfo, 13,4))
lnSofttype = buf2dword (SUBSTR (lcServerInfo, 17,4))
lnMemoPtr = buf2dword (SUBSTR (lcServerInfo, 21,4))

lcServerName = getStrFromMem (lnNamePtr)
lcServerMemo = getStrFromMem (lnMemoPtr)
INSERT INTO csResult VALUES ( lnPlatformId, lcServerName,;
lnMajorVer, lnMinorVer, lnSofttype, lcServerMemo)
NEXT
ENDIF

*** Must always release the memory block
NetApiBufferFree (lnBuffer)
RETURN

FUNCTION DeclareAPI
*** Only re-declare if not available
lnRes = ADLLS( laJunk )
IF lnRes = 0 OR NOT ( ASCAN( laJunk, 'NetServerEnum', 1, -1, 1, 15 ) > 0)
*** We don't have the function available
DECLARE INTEGER NetServerEnum IN netapi32;
INTEGER servername, INTEGER level, INTEGER @ bufptr,;
INTEGER prefmaxlen, INTEGER @ entriesread, INTEGER @ totalentries,;
INTEGER servertype, INTEGER domain, INTEGER resume_handle
ENDIF

IF lnRes = 0 OR NOT ( ASCAN( laJunk, 'NetApiBufferFree', 1, -1, 1, 15 ) > 0)
*** We don't have the function available
DECLARE INTEGER NetApiBufferFree IN netapi32 INTEGER Buffer
ENDIF

IF lnRes = 0 OR NOT ( ASCAN( laJunk, 'NetApiBufferFree', 1, -1, 1, 15 ) > 0)
*** We don't have the function available
DECLARE RtlMoveMemory IN kernel32 As Heap2String;
STRING @ Destination, INTEGER Source, INTEGER nLength
ENDIF
RETURN

FUNCTION getStrFromMem (tnMemBlock)
LOCAL lcBuffer
*** converts memory allocated Unicode to a VFP string
#DEFINE StrBufferLength 250
lcBuffer = SPACE( StrBufferLength )
Heap2String (@lcBuffer, tnMemBlock, StrBufferLength)
lcBuffer = SUBSTR (lcBuffer, 1, AT(Chr(0)+Chr(0),lcBuffer)-1)
*** Remove any NULL characters on the way out too!
RETURN STRTRAN(lcBuffer, Chr(0),"")

FUNCTION buf2dword (lcBuffer)
RETURN ASC(SUBSTR(lcBuffer, 1,1)) + ;
ASC(SUBSTR(lcBuffer, 2,1)) * 256 +;
ASC(SUBSTR(lcBuffer, 3,1)) * 65536 +;
ASC(SUBSTR(lcBuffer, 4,1)) * 16777216