Getting all SQL Servers on the network

[Smallville]

Hi,

I am using the Netapi32 windows api to get all SQL Servers running on the network. Unfortunately this is not working. Again this piece of code used to work on the

 Private Declare Function NetServerEnum Lib "Netapi32.dll" ( _ 
ByVal strServerName As String, _

ByVal level As Integer, _

ByRef bufptr As Integer, _

ByVal prefmaxlen As Integer, _

ByRef entriesread As Integer, _

ByRef totalentries As Integer, _

ByRef servertype As Integer, _

ByVal strDomain As String, _

ByRef resumehandle As Integer) As Integer

 'Using the NetApi to perform this task 

Private Declare Function NetApiBufferFree Lib "Netapi32.dll" ( _

ByVal lpBuffer As Integer) As Integer

 Public Sub GetSQLServers() 

'This function will return all the SQL Servers available

'within the default domain

Try

'Variables to specify the parameters for the SQL Server

Dim l As Integer

Dim entriesread As Integer

Dim totalentries As Integer

Dim hREsume As Integer

Dim bufptr As Integer

Dim level As Integer

Dim prefmaxlen As Integer

Dim lType As Integer

Dim domain() As Byte

Dim i As Integer

Dim sv100 As SV_100

level = 100

prefmaxlen = &HFFFFFFFF

'Locate only SQLServers

lType = SV_TYPE_SQLSERVER

'Setting the required parameters

l = NetServerEnum(CStr(0), level, bufptr, prefmaxlen, entriesread, totalentries, SV_TYPE_SQLSERVER, CStr(0), hREsume)

'The NetSErverEnum returns a long type value.

'If that value is 0 or 234& then,

'For no entries read to all entries read

'Add the sql server name to the collection.

'If the returned value is not 0 or 234&, then

'Prompt user that no server was found.

If l = 0 Or l = 234 Then

For i = 0 To entriesread - 1

CopyMemory(sv100, bufptr, Len(sv100))

'Adding the sql server name to the collection

colSQLServers.Add(Trim(Pointer2stringw(sv100.name)))

bufptr = bufptr + Len(sv100)

Next i

End If

'Settings for establishing a connection to the local server

colSQLServers.Add("(local)")

NetApiBufferFree(bufptr)

Catch ex As Exception

MsgBox(ex.ToString, MsgBoxStyle.OKOnly + MsgBoxStyle.Critical, "Exception")

'Prompt user with error message

ErrorControl(Err.Number)

End Try

End Sub

I am sure I did somehting wrong but don't know what. Does anyone has any idea of what it is?

Thanking you in advance,
Kind Regards,
Somebody save me......

 

[Smallville]

Well I've forgot to mention that I called NetServerEnum to get the list but seems that it returns some value like 2351. And that on the network there are actually more than 2 SQL Servers.

 

[jmcilhinney]

Go here and read up on exactly what the return value and arguments of that function mean.

 

[Smallville]

I've seen it in MSDN Library too but I don't understand why this is not working. The language used is Visual Basic .net so the types are not exactly the same but I tried to map them in Visual Basic .Net as you have seen above.

Do you know what I did wrong or do you have any idea of how I can retrieve all the name of the SQL Servers that are available on the network and also be able to search for a specific database name?

 

[Tarik]

Here is a simple example i have made from long time Using The SQL DMO
After Refernce The SQL DMO Library You can use the following code to fill ListBox1 With All SQL Servers Found
FIRST The Imports Section

Imports System.Runtime.InteropServices
Imports SQLDMO

Then The Actual Code

Dim DMONAMELIST, DMOAPP, I
	'Create the Application object
	DMOAPP = New SQLDMO.Application()

	' Get the list of servers
	DMONAMELIST = DMOAPP.ListAvailableSQLServers

	'Get the server names
	For I = 1 To DMONAMELIST.Count
	  ListBox1.Items.Add(DMONAMELIST(I))
	Next