SqlDataSourceEnumerator.Instance.GetDataSources() does not locate local SQL server 2008 instance
Thanks a lot to Mitch for the great answer he puts together. However, what I've done eventually is like the following:
I have two separate methods to get local and remote server instance respectively. The local instances are retrieved from the registry. You need to search both WOW64 and WOW3264 hives to get both SQL server 2008 (64bit) and SQL server Express (32 bit)
here is the code I use:
/// <summary>
/// get local sql server instance names from registry, search both WOW64 and WOW3264 hives
/// </summary>
/// <returns>a list of local sql server instance names</returns>
public static IList<string> GetLocalSqlServerInstanceNames()
{
RegistryValueDataReader registryValueDataReader = new RegistryValueDataReader();
string[] instances64Bit = registryValueDataReader.ReadRegistryValueData(RegistryHive.Wow64,
Registry.LocalMachine,
@"SOFTWARE\Microsoft\Microsoft SQL Server",
"InstalledInstances");
string[] instances32Bit = registryValueDataReader.ReadRegistryValueData(RegistryHive.Wow6432,
Registry.LocalMachine,
@"SOFTWARE\Microsoft\Microsoft SQL Server",
"InstalledInstances");
FormatLocalSqlInstanceNames(ref instances64Bit);
FormatLocalSqlInstanceNames(ref instances32Bit);
IList<string> localInstanceNames = new List<string>(instances64Bit);
localInstanceNames = localInstanceNames.Union(instances32Bit).ToList();
return localInstanceNames;
}
public enum RegistryHive
{
Wow64,
Wow6432
}
public class RegistryValueDataReader
{
private static readonly int KEY_WOW64_32KEY = 0x200;
private static readonly int KEY_WOW64_64KEY = 0x100;
private static readonly UIntPtr HKEY_LOCAL_MACHINE = (UIntPtr)0x80000002;
private static readonly int KEY_QUERY_VALUE = 0x1;
[DllImport("advapi32.dll", CharSet = CharSet.Unicode, EntryPoint = "RegOpenKeyEx")]
static extern int RegOpenKeyEx(
UIntPtr hKey,
string subKey,
uint options,
int sam,
out IntPtr phkResult);
[DllImport("advapi32.dll", SetLastError = true)]
static extern int RegQueryValueEx(
IntPtr hKey,
string lpValueName,
int lpReserved,
out uint lpType,
IntPtr lpData,
ref uint lpcbData);
private static int GetRegistryHiveKey(RegistryHive registryHive)
{
return registryHive == RegistryHive.Wow64 ? KEY_WOW64_64KEY : KEY_WOW64_32KEY;
}
private static UIntPtr GetRegistryKeyUIntPtr(RegistryKey registry)
{
if (registry == Registry.LocalMachine)
{
return HKEY_LOCAL_MACHINE;
}
return UIntPtr.Zero;
}
public string[] ReadRegistryValueData(RegistryHive registryHive, RegistryKey registryKey, string subKey, string valueName)
{
string[] instanceNames = new string[0];
int key = GetRegistryHiveKey(registryHive);
UIntPtr registryKeyUIntPtr = GetRegistryKeyUIntPtr(registryKey);
IntPtr hResult;
int res = RegOpenKeyEx(registryKeyUIntPtr, subKey, 0, KEY_QUERY_VALUE | key, out hResult);
if (res == 0)
{
uint type;
uint dataLen = 0;
RegQueryValueEx(hResult, valueName, 0, out type, IntPtr.Zero, ref dataLen);
byte[] databuff = new byte[dataLen];
byte[] temp = new byte[dataLen];
List<String> values = new List<string>();
GCHandle handle = GCHandle.Alloc(databuff, GCHandleType.Pinned);
try
{
RegQueryValueEx(hResult, valueName, 0, out type, handle.AddrOfPinnedObject(), ref dataLen);
}
finally
{
handle.Free();
}
int i = 0;
int j = 0;
while (i < databuff.Length)
{
if (databuff[i] == '\0')
{
j = 0;
string str = Encoding.Default.GetString(temp).Trim('\0');
if (!string.IsNullOrEmpty(str))
{
values.Add(str);
}
temp = new byte[dataLen];
}
else
{
temp[j++] = databuff[i];
}
++i;
}
instanceNames = new string[values.Count];
values.CopyTo(instanceNames);
}
return instanceNames;
}
}
SqlDataSourceEnumerator.Instance.GetDataSources() is used to get remote sql server instances.
At the end, I just merge the remote instance list and local instance list to produce the final result.
Listing available SQL Servers with instances does not include SQL Server Express
I guess you can locate local instances in registry.I had same issue long time back.The following link helped me
SqlDataSourceEnumerator.Instance.GetDataSources() does not locate local SQL server 2008 instance
.NET not showing instance or version when enumeration is done via SqlDataSourceEnumerator.Instance.GetDataSources()
Well, it turns out this was a networking based issue with my lab environment, and hopefully this helps someone in the future. While the suggestion to use Wireshark was a good one, it didn't resolve my issue, but made me think about my setup a little further.
My virtual lab environment involves using Oracle's VirtualBox, and to give some further background, when adding a NIC to the VM, the option that was chosen in the drop-down Attached type: was Bridged Adapter. This worked fine, the VM received an IP via DHCP on my network, I as able to ping the VM, connect to it via SSMS, RDP to it, etc.
However, for some reason, the UDP packets were not "leaving" that VM and thus the output from the SQL Browser service were not making it back to my client, therefore the SQL enumeration operation discussed above would return empty results.
So, my solution was to do the following.
- Change the
attached typeon the NIC fromBridged AdaptertoVirtualBox Host-Only Ethernet Adapter.
This will change the VM's IP to a something starting at 192.168.56.101 (default) and will not assign a gateway to the adapter. This means that if you wanted Internet access for the VM, it will not work.
- To allow the VM Internet access, you need to
shutdown the VMin question, and add a second NIC in the VirtualBox settings to the VM, underNetworkand by checking the boxEnable Network Adapterunder theAdapter 2tab. Here is where you can chooseBridged Adapterin theAttached to:drop-down so that the VM will get an IP on your network when restarted.
Now, since the VM has a "leg" into both networks with the routing table configured by VirtualBox, the SQL Browser data is now making it back out to the client machine and the results were as expected.
Hopefully this can be of use to someone.
how can i use System.Data.Sql.SqlDataSourceEnumerator class to know available sql datasources...?
So, according to the following references:
http://social.msdn.microsoft.com/forums/en-US/sqlsmoanddmo/thread/49ba019f-e8b5-457c-80ea-fac5febb9d3d/
http://connect.microsoft.com/SQLServer/feedback/details/146323/enumavailablesqlservers-or-sqldatasourceenumerator-incorrect-list-of-available-databases
http://blogs.msdn.com/b/sushilc/archive/2004/10/14/242395.aspx
http://sqlblogcasts.com/blogs/jonsayce/archive/2008/02/10/programatically-listing-sql-servers.aspx
GetDataSources() is not a perfect method, meaning, it may not list all the available data sources on first try. In fact, I found that it also does not list all of your local sources.
For my purposes, I had some time between when the program started and when I needed to get the list of available sources, both on the network AND local. So, I put the code in a thread that goes on forever collecting all the sources. Here it is below. If you take out the while loop, you can call it manually as many times as you'd like.
private List<string> sqlInstances = new List<string>();
private void collectInstances()
{
while (true)
{
System.Data.Sql.SqlDataSourceEnumerator instance = System.Data.Sql.SqlDataSourceEnumerator.Instance;
System.Data.DataTable dataTable = instance.GetDataSources();
foreach (DataRow row in dataTable.Rows)
{
string instanceName = String.Format(@"{0}\{1}", row["ServerName"].ToString(), row["InstanceName"].ToString());
//Do not add the local instance, we will add it in the next section. Otherwise, duplicated!
if (!sqlInstances.Contains(instanceName) && !instanceName.Contains(Environment.MachineName))
{
sqlInstances.Add(instanceName);
}
}
/*
* For some reason, GetDataSources() does not get local instances. So using code from here to get them
* http://stackoverflow.com/questions/6824188/sqldatasourceenumerator-instance-getdatasources-does-not-locate-local-sql-serv
*/
List<string> lclInstances = GetLocalSqlServerInstanceNames();
foreach (var lclInstance in lclInstances)
{
string instanceName = String.Format(@"{0}\{1}", Environment.MachineName, lclInstance);
if (!sqlInstances.Contains(instanceName)) sqlInstances.Add(instanceName);
}
sqlInstances.Sort();
}
}
//Got code from: http://stackoverflow.com/questions/6824188/sqldatasourceenumerator-instance-getdatasources-does-not-locate-local-sql-serv
/// <summary>
/// get local sql server instance names from registry, search both WOW64 and WOW3264 hives
/// </summary>
/// <returns>a list of local sql server instance names</returns>
public static List<string> GetLocalSqlServerInstanceNames()
{
RegistryValueDataReader registryValueDataReader = new RegistryValueDataReader();
string[] instances64Bit = registryValueDataReader.ReadRegistryValueData(RegistryHive.Wow64,
Registry.LocalMachine,
@"SOFTWARE\Microsoft\Microsoft SQL Server",
"InstalledInstances");
string[] instances32Bit = registryValueDataReader.ReadRegistryValueData(RegistryHive.Wow6432,
Registry.LocalMachine,
@"SOFTWARE\Microsoft\Microsoft SQL Server",
"InstalledInstances");
//FormatLocalSqlInstanceNames(ref instances64Bit);
//FormatLocalSqlInstanceNames(ref instances32Bit);
List<string> localInstanceNames = new List<string>(instances64Bit);
foreach (var item in instances32Bit)
{
if (!localInstanceNames.Contains(item)) localInstanceNames.Add(item);
}
//localInstanceNames = localInstanceNames.Union(instances32Bit).ToList();
return localInstanceNames;
}
.NET not detecting any SQL Server instances
I have decided to go with this ODBC implementation as it is working correctly and an async/await update can be implemented so it won't block the UI.
Before choosing this implementation, I also poked around with another project using the SqlDataSourceEnumerator class, which worked perfectly. Its Target framework property was set to .NET Framework 2.0. I discovered that the SqlDataSourceEnumerator gives the expected results (on my machine) when the .NET target version is 3.5 or lower. Something in the .NET implementation has probably changed over time, causing it to stop working in some specific conditions.
Edit: on my machine I have installed SQL Server 2012 again, and everything (except for the detection, will use ODBC) is working fine again. I want to thank everyone for their input.
Get SQL instance return error in SQL Server Express
Put This Class
Imports System.Runtime.InteropServices
Imports System.Text
Public Class SQLInfoEnumerator
<DllImport("odbc32.dll")>
Private Shared Function SQLAllocHandle(handleType As Short, inputHandle As IntPtr, ByRef outputHandlePtr As IntPtr) As Short
End Function
<DllImport("odbc32.dll")>
Private Shared Function SQLSetEnvAttr(environmentHandle As IntPtr, attribute As Integer, valuePtr As IntPtr, stringLength As Integer) As Short
End Function
<DllImport("odbc32.dll")>
Private Shared Function SQLFreeHandle(hType As Short, Handle As IntPtr) As Short
End Function
<DllImport("odbc32.dll", CharSet:=CharSet.Ansi)>
Private Shared Function SQLBrowseConnect(handleConnection As IntPtr, inConnection As StringBuilder, stringLength As Short, outConnection As StringBuilder, bufferLength As Short, ByRef stringLength2Ptr As Short) As Short
End Function
Private Const SQL_DRIVER_STR As String = "DRIVER=SQL SERVER"
Private Const SQL_SUCCESS As Short = 0
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_NEED_DATA As Short = 99
Private Const DEFAULT_RESULT_SIZE As Short = 1024
Private Const START_STR As String = "{"
Private Const END_STR As String = "}"
''' <summary>
''' A string to hold the selected SQL Server
''' </summary>
Private m_SQLServer As String
''' <summary>
''' A string to hold the username
''' </summary>
Private m_Username As String
''' <summary>
''' A string to hold the password
''' </summary>
Private m_Password As String
''' <summary>
''' Property to set the SQL Server instance
''' </summary>
Public WriteOnly Property SQLServer() As String
Set(value As String)
m_SQLServer = value
End Set
End Property
''' <summary>
''' Property to set the Username
''' </summary>
Public WriteOnly Property Username() As String
Set(value As String)
m_Username = value
End Set
End Property
''' <summary>
''' Property to set the Password
''' </summary>
Public WriteOnly Property Password() As String
Set(value As String)
m_Password = value
End Set
End Property
''' <summary>
''' Enumerate the SQL Servers returning a list (if any exist)
''' </summary>
''' <returns></returns>
Public Function EnumerateSQLServers() As String()
Return RetrieveInformation(SQL_DRIVER_STR)
End Function
''' <summary>
''' Enumerate the specified SQL server returning a list of databases (if any exist)
''' </summary>
''' <returns></returns>
Public Function EnumerateSQLServersDatabases() As String()
Return RetrieveInformation(Convert.ToString((Convert.ToString((Convert.ToString(SQL_DRIVER_STR & Convert.ToString(";SERVER=")) & m_SQLServer) + ";UID=") & m_Username) + ";PWD=") & m_Password)
End Function
''' <summary>
''' Enumerate for SQLServer/Databases based on the passed information it the string
''' The more information provided to SQLBrowseConnect the more granular it gets so
''' if only DRIVER=SQL SERVER passed then a list of all SQL Servers is returned
''' If DRIVER=SQL SERVER;Server=ServerName is passed then a list of all Databases on the
''' servers is returned etc
''' </summary>
''' <param name="InputParam">A valid string to query for</param>
''' <returns></returns>
Private Function RetrieveInformation(InputParam As String) As String()
Dim m_environmentHandle As IntPtr = IntPtr.Zero
Dim m_connectionHandle As IntPtr = IntPtr.Zero
Dim inConnection As New StringBuilder(InputParam)
Dim stringLength As Short = CShort(inConnection.Length)
Dim outConnection As New StringBuilder(DEFAULT_RESULT_SIZE)
Dim stringLength2Ptr As Short = 0
Try
If SQL_SUCCESS = SQLAllocHandle(SQL_HANDLE_ENV, m_environmentHandle, m_environmentHandle) Then
If SQL_SUCCESS = SQLSetEnvAttr(m_environmentHandle, SQL_ATTR_ODBC_VERSION, New IntPtr(SQL_OV_ODBC3), 0) Then
If SQL_SUCCESS = SQLAllocHandle(SQL_HANDLE_DBC, m_environmentHandle, m_connectionHandle) Then
If SQL_NEED_DATA = SQLBrowseConnect(m_connectionHandle, inConnection, stringLength, outConnection, DEFAULT_RESULT_SIZE, stringLength2Ptr) Then
If SQL_NEED_DATA <> SQLBrowseConnect(m_connectionHandle, inConnection, stringLength, outConnection, DEFAULT_RESULT_SIZE, stringLength2Ptr) Then
Throw New ApplicationException("No Data Returned.")
End If
End If
End If
End If
End If
Catch ex As Exception
Throw New ApplicationException("Cannot Locate SQL Server.")
Finally
FreeConnection(m_connectionHandle)
FreeConnection(m_environmentHandle)
End Try
If outConnection.ToString() <> "" Then
Return ParseSQLOutConnection(outConnection.ToString())
Else
Return Nothing
End If
End Function
''' <summary>
''' Parse an outConnection string returned from SQLBrowseConnect
''' </summary>
''' <param name="outConnection">string to parse</param>
''' <returns></returns>
Private Function ParseSQLOutConnection(outConnection As String) As String()
Dim m_Start As Integer = outConnection.IndexOf(START_STR) + 1
Dim m_lenString As Integer = outConnection.IndexOf(END_STR) - m_Start
If (m_Start > 0) AndAlso (m_lenString > 0) Then
outConnection = outConnection.Substring(m_Start, m_lenString)
Else
outConnection = String.Empty
End If
Return outConnection.Split(",".ToCharArray())
End Function
Private Sub FreeConnection(handleToFree As IntPtr)
If handleToFree <> IntPtr.Zero Then
SQLFreeHandle(SQL_HANDLE_DBC, handleToFree)
End If
End Sub
End Class
and add this method
Private Sub GetSQLDetails(SQLListBox As ListBox)
Dim sie As New SQLInfoEnumerator()
Try
If SQLListBox.Name = "listboxSQLServerDatabaseInstances" Then
SQLListBox.Items.Clear()
sie.SQLServer = listboxSQLServerInstances.SelectedItem.ToString()
sie.Username = textboxUserName.Text
sie.Password = textboxPassword.Text
SQLListBox.Items.AddRange(sie.EnumerateSQLServersDatabases())
Else
SQLListBox.Items.Clear()
SQLListBox.Items.AddRange(sie.EnumerateSQLServers())
End If
Catch ex As Exception
MessageBox.Show(ex.ToString())
End Try
End Sub
Enumerating sql server instances
I had same issue and after digging into deep, i found the max Outconnection size is less than return stringLength2Ptr and that is a reason was getting truncated.
So check your return stringLength2Ptr size and assigned max size to outconnection. if return stringLength2Ptr size is bigger and you trying to get string data of OutConnection then it will failed to return full string.
I do not know how you using your outconnection string data.
if (SQL_NEED_DATA ==
SQLBrowseConnect(m_connectionHandle, inConnection, stringLength, outConnection,
4 * DEFAULT_RESULT_SIZE, out stringLength2Ptr))
{
}
in my case increasing outconnection got fixed my issue.
Related Topics
Linq to Entities Doesn't Recognize a Method
How to Avoid "Too Many Parameters" Problem in API Design
Expression.Lambda and Query Generation at Runtime, Simplest "Where" Example
Date Format Yyyy-Mm-Ddthh:Mm:Ssz
Selectively Suppress Custom Obsolete Warnings
My C# Application Is Returning 0Xe0434352 to Windows Task Scheduler But It Is Not Crashing
Launching a Process in User's Session from a Service
C# Generics Won't Allow Delegate Type Constraints
How to List Directory Contents with Ftp in C#
Any Reason to Use Auto-Implemented Properties Over Manual Implemented Properties
Checking File/Folder Access Permission
When Should I Use the Hashset<T> Type
Are 2 Dimensional Lists Possible in C#
C# Generic "Where Constraint" with "Any Generic Type" Definition