 |
|
|
How I can list SQL servers on a network at C#?
|
|
|
|
|
|
|
I once needed the exact same functionality and found the following code somewhere on the internet:
#include < sql.h >
#include < sqlext.h >
BOOL EnumerateServers()
{
LPCTSTR pszInputParam = _T("Driver={SQL Server}");
LPCTSTR pszLookUpKey = _T("SERVER:Server=");
SQLHENV hSQLEnv;
SQLHDBC hSQLHdbc;
short sConnStrOut;
DWORD dwRetCode;
dwRetCode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hSQLEnv);
if (dwRetCode == SQL_SUCCESS || dwRetCode == SQL_SUCCESS_WITH_INFO)
{
dwRetCode = SQLSetEnvAttr(hSQLEnv, SQL_ATTR_ODBC_VERSION, (void *)SQL_OV_ODBC3, 0);
if (dwRetCode == SQL_SUCCESS || dwRetCode == SQL_SUCCESS_WITH_INFO)
{
dwRetCode = SQLAllocHandle(SQL_HANDLE_DBC, hSQLEnv, &hSQLHdbc);
if (dwRetCode == SQL_SUCCESS || dwRetCode == SQL_SUCCESS_WITH_INFO)
{
CString szConnStrOut;
dwRetCode = SQLBrowseConnect(hSQLHdbc,
(SQLCHAR *)pszInputParam,
SQL_NTS,
(SQLCHAR *)(szConnStrOut.GetBuffer(ENUM_SERVERS_MAX_RET_LENGTH)),
ENUM_SERVERS_MAX_RET_LENGTH,
&sConnStrOut);
szConnStrOut.ReleaseBuffer();
int iFind = szConnStrOut.Find(pszLookUpKey);
if(iFind != -1)
{
CString szLookUpKey = pszLookUpKey;
szConnStrOut = szConnStrOut.Mid(iFind+szLookUpKey.GetLength());
iFind = szConnStrOut.Find('{');
if(iFind != -1)
{
szConnStrOut = szConnStrOut.Mid(iFind+1);
iFind = szConnStrOut.Find('}');
if(iFind != -1)
{
szConnStrOut = szConnStrOut.Left(iFind);
dwRetCode = TRUE;
}
}
}
SQLDisconnect(hSQLHdbc);
}
SQLFreeHandle(SQL_HANDLE_DBC, hSQLHdbc);
}
SQLFreeHandle(SQL_HANDLE_ENV, hSQLEnv);
}
return dwRetCode;
}I can't remember where I found it, but it works fine for me. 
Calling the NetServerEnum function seems cleaner but the only problem is it won't function on Windows 9x/ME (according to the MSDN). My function will.
Rogier.
|
|
|
|
|
|
|
I'm might be wrong about this, but I thought SQLBrowseConnect only enumerates ODBC sources?
Not much use if using OLE-DB (much faster and more ADO compatability)
Theres a feature for this in the SQL-DMO libs. Technically they're server management, but it does the job.
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q287737
from that page: (microsofts fault if duff..)
Private Sub Command1_Click()
Dim i As Integer
Dim oNames As SQLDMO.NameList
Dim oSQLApp As SQLDMO.Application
Set oSQLApp = New SQLDMO.Application
Set oNames = oSQLApp.ListAvailableSQLServers()
List1.Clear
For i = 1 To oNames.Count
List1.AddItem oNames.Item(i)
Next i
End Sub
-Ed
|
|
|
|
|
|
|
Hi,
You're absolutely right about SQLBrowseConnect, it only enumerates ODBC sources. (according to the MSDN)
But, correct me if I'm wrong, doesn't every OLE-DB source
have an ODBC equivalent? In other words, doesn't every
SQL-Server accept both ODBC and OLE-DB connections?
(or can one disable ODBC-connections in SQL-server?)
I only use SQLBrowseConnect to enumerate the servers,
afterwards I connect using (the faster) OLE-DB (with ADO).
Rogier.
|
|
|
|
|
|
|
A small problem with the code posted in the original article is that it does not display non-default instances of SQL servers. For example, we have two instances named "ACCOUNTING" and "ACCOUNTING\TEST" and the example code only shows "ACCOUNTING", while the list of servers in Query Analyzer lists both.
|
|
|
|
|
|
|
But this code is not working in VB.net though I have referenced this library.
Can U help me out ?
|
|
|
|
|
|
|
Hi,
Well all I had to do to get it working in vb.net was this:
1. add reference to the dll
2. add some controls to a form
- a textbox called txtDomain
- a listbox called lstSvrs
- a button called btnSearch
3. add the following code for the button's click handler
Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
Dim oSqlUtils As New SQLUtils.ListServers()
Dim svr As String
oSqlUtils.Domain = txtDomain.Text
oSqlUtils.GetSQLServers()
For Each svr In oSqlUtils.Servers
lstSvrs.Items.Add(svr)
Next
End Sub
When you say "it's not working" what exactly do you mean? Are you getting an error message? Let me know if you're still having trouble and I'll try and help you out.
HTH
|
|
|
|
|
|
|
mostevil wrote:
I'm might be wrong about this, but I thought SQLBrowseConnect only enumerates ODBC sources?
In MSDN it says that the SQL-DMO function ListAvailableSQLServers function uses SQLBrowseConnect to enumerate the servers anyway.
|
|
|
|
|
|
|
Was there ever a complete solution to this problem. I'm trying to find out how to get a list of SQL servers which also:
Works on Win9x/ME as well as NT/2K & XP
Works when not connected to a network
Picks up all instances of SQL and MSDE
So far, the SQLDMO won't pick up when not on a network and the NetServerEnum won't work on win 9x/Me.
Any help would be much appreciated.
p.s. If anyone who is willing to lend me their Dodge Viper for a few days, please let me know.
|
|
|
|
|
|
|
Here goes the same in VB.Net (I've ported C# article on http://www.ondotnet.pl/sourcecode/SqlLocator.cs)
Imports System
Imports System.Text
Imports System.Runtime.InteropServices
Public Class SqlServerList
#Region "ODBC32 API"
Private Const SQL_HANDLE_ENV As Short = 1
Private Const SQL_HANDLE_DBC As Short = 2
Private Const SQL_ATTR_ODBC_VERSION As Integer = 200
Private Const SQL_OV_ODBC3 As Integer = 3
Private Const SQL_SUCCESS As Short = 0
Private Const SQL_NEED_DATA As Short = 99
Private Const DEFAULT_RESULT_SIZE As Short = 1024
Private Const SQL_DRIVER_STR As String = "DRIVER=SQL SERVER"
<DllImport("odbc32.dll")> _
Private Shared Function SQLAllocHandle(ByVal hType As Short, ByVal inputHandle As IntPtr, ByRef outputHandle As IntPtr) As Short
End Function
<DllImport("odbc32.dll")> _
Private Shared Function SQLSetEnvAttr(ByVal henv As IntPtr, ByVal attribute As Integer, ByVal valuePtr As IntPtr, ByVal strLength As Integer) As Short
End Function
<DllImport("odbc32.dll", CharSet:=CharSet.Ansi)> _
Private Shared Function SQLBrowseConnect(ByVal hconn As IntPtr, ByVal inString As StringBuilder, ByVal inStringLength As Short, ByVal outString As StringBuilder, ByVal outStringLength As Short, ByRef outLengthNeeded As Short) As Short
End Function
<DllImport("odbc32.dll")> _
Private Shared Function SQLFreeHandle(ByVal hType As Short, ByVal handle As IntPtr) As Short
End Function
#End Region
'Lists SQL Server databases
'usage: comboBox1.Items.AddRange(SqlServerList.Servers())
Public Shared Function Servers() As String()
Dim retval As String() = Nothing
Dim txt As String = String.Empty
Dim henv As IntPtr = IntPtr.Zero
Dim hconn As IntPtr = IntPtr.Zero
Dim inString As New StringBuilder(SQL_DRIVER_STR)
Dim outString As New StringBuilder(DEFAULT_RESULT_SIZE)
Dim inStringLength As Short = Fix(CShort(inString.Length))
Dim lenNeeded As Short = 0
Try
If SQL_SUCCESS = SQLAllocHandle(SQL_HANDLE_ENV, henv, henv) Then
If SQL_SUCCESS = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, New System.IntPtr(SQL_OV_ODBC3), 0) Then
If SQL_SUCCESS = SQLAllocHandle(SQL_HANDLE_DBC, henv, hconn) Then
If SQL_NEED_DATA = SQLBrowseConnect(hconn, inString, inStringLength, outString, DEFAULT_RESULT_SIZE, lenNeeded) Then
If DEFAULT_RESULT_SIZE < lenNeeded Then
outString.Capacity = lenNeeded
If SQL_NEED_DATA <> SQLBrowseConnect(hconn, inString, inStringLength, outString, lenNeeded, lenNeeded) Then
Throw New ApplicationException("Unabled to aquire SQL Servers from ODBC driver.")
End If
End If
txt = outString.ToString()
Dim start As Integer = txt.IndexOf("{") + 1
Dim len As Integer = txt.IndexOf("}") - start
If start > 0 AndAlso len > 0 Then
txt = txt.Substring(start, len)
Else
txt = String.Empty
End If
End If
End If
End If
End If
Catch ex As Exception
txt = String.Empty
Throw ex
Finally
If Not hconn.Equals(IntPtr.Zero) Then
SQLFreeHandle(SQL_HANDLE_DBC, hconn)
End If
If Not henv.Equals(IntPtr.Zero) Then
SQLFreeHandle(SQL_HANDLE_ENV, hconn)
End If
End Try
If txt.Length > 0 Then
retval = txt.Split(",".ToCharArray())
End If
Return retval
End Function
End Class
|
|
|
|
|
|
|
You just saved me a ton of time. I normally use C# and have a customer who wanted a program in VB (so his programmer can maintain it). I was looking at converting SqlLocator and found this.  Thanks!
|
|
|
|
|
|
|
This code works like a dream. Thanks bud.
|
|
|
|
|
|
|
zjerry wrote:
Here goes the same in VB.Net (I've ported C# article on http://www.ondotnet.pl/sourcecode/SqlLocator.cs)
Imports System
Imports System.Text
Imports System.Runtime.InteropServices
Public Class SqlServerList ...........
Hello!
I tried this code without connecting my PC to a network and it returned nothing even if there is a running instance of SQL-Server(local).
Is this correct? Is was one of the requests that it even lists non-default instances and that it works even without network connection....
Any idea?
Thanks!
Frank
|
|
|
|
|
|
|
Using the code ZJERRY has posted....
I had been looking all over for something like this and I am very glad I found this. Unfortunately the system I am working on has upwards of 500 servers available and to list them all is taking upwards of ten minutes. 
Has anyone else had this problem, or does anyone have any suggestions on how to speed it up?
Here is my current code:
Dim i As Integer
Dim ServerList As SqlServerList
Dim ServerNumber As Integer = ServerList.Servers.Length
For i = 0 To ServerNumber - 1
combo_db.Items.Add(ServerList.Servers(i))
Next i
Any Help would be greatly appreciated.
-- modified at 16:11 Wednesday 15th February, 2006
|
|
|
|
|
|
|
rugbygeek wrote: For i = 0 To ServerNumber - 1
combo_db.Items.Add(ServerList.Servers(i))
Next i
combo_db.Items.AddRange(ServerList.Servers())
may be a little faster
Pablo
|
|
|
|
|
|
|
It's a nice method to get the list of servers...
How should i get the list of databases available in the particular server without using SQLDMO.dll
Loka
|
|
|
|
|
|
|
Thank you...This is very helpfull! You got my 5 
Pablo
|
|
|
|
|
|
|
Will it be work for Oracle, if I cange Driver details("DRIVER=SQL SERVER")?
I tried it, not working. I want get the list of oracle servers connected to my machine.
Loka
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Rant 
Admin