Populating list of SQL servers on lan

K

Karlos

Limp Gawd
Joined
Sep 30, 2000
Messages
469
This may be a stupid question... but i'm looking for a way to get a list of all the SQL servers running on a lan. Similar to how MSDE service manager, Enterprise manager, etc have a list of all the sql servers running on the network. Is there anyway to do this with code in VB.Net?

I need a way to be able to select which server to connect to, and right now what it does is gets a list of all the computers on a windows network, but it's slow and takes a while to refresh properly. I could just check each computer to see if sql is running, but that would just slow it down even more.

Thanks in advance.
 
I don't know VB.NET very well, I've never worked with any non-open-source SQL servers, and I've never attempted what you're asking, but hey that never stopped me before.

Assuming the SQL servers are listening on 1433 (google says this is the default port for MS SQL right?), couldn't you just portscan each computer on the LAN at port 1433? Yea, the end doesn't justify the means necessarily, and there is a lot of room for potential error (i.e. if the servers aren't listening on the default port). But a single port scan, even if it's every computer on a big LAN, shouldn't take long to run.

Like I said I don't know VB.NET, but I found this link with a class for portscanning:
http://discuss.develop.com/archives/wa.exe?A2=ind0405a&L=vbdotnet&T=0&F=&S=&P=923

This may not even work but it could be a step in the right direction. I'm giving you an answer based more on theory than experience :)
 
Rolling your own when a documented and viable method exists is never a step in the right direction.

Use SQL-DMO to get your list. Note that this won't get all the servers on the network; only the servers that respond to the broadcast you send out. Your network topology might limit the scope of that broadcast, and some servers might be configured to not respond (or be down at the moment).

Searching the web for "SQL-DMO VB.Net List Servers" should get you some examples. Here's one that looks nice:

http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=2915&lngWid=10

You'll want to add a reference to the "Microsoft.SQLDMO Object Library" to your project using the COM tab in the Add References dialog.
 
Thanks for the idea on the port scanning, i was thinking of doing something similar to that in the begining.

But thats exactly what i was lookijng for. I knew there was something as simple as that, i just couldn't find it. (I think i'm loosing my touch on google :p ). I'll try it out and see how it works.

Thanks for all your help.
 
SQL-DMO can do this easily, but keep in mind that SQL-DMO will only be present if you have installed client tools (or maybe MSDE?). Also, you do not have the right to redistribute sqldmo.dll with your application.

SQL-DMO works great if you are writing tools for yourself, but it doesn't work well for production software.
 
If you don't like SQL-DMO, you can interop with the NetEnumServers API.
 
Earp said:
SQL-DMO can do this easily, but keep in mind that SQL-DMO will only be present if you have installed client tools (or maybe MSDE?). Also, you do not have the right to redistribute sqldmo.dll with your application.

SQL-DMO works great if you are writing tools for yourself, but it doesn't work well for production software.
Click to expand...

Yeah i just realized that after i got it working, i would need it to work on client machines without SQL or MSDE installed. It works great tho, a lot faster than current and it refreshes changes instantly.

Any more infomation on NetEnumServers? Can't seem to find much about it on google.
 
Earp said:
Also, you do not have the right to redistribute sqldmo.dll with your application.
Click to expand...

Are you sure? REDIST.TXT says that SQLDMO.DLL (and its friends) are redistributable and further explains some of the installation requirements.

KB Article 258157 contains instructions for using the deployment wizard in Visual BASIC to create a package that installs SQLDMO.

See also the article "Distributing SQL Server with Applications" -- specifically, the section "Distributing the SQL Server Client Components" in SS BOL.

Karlos said:
Any more infomation on NetEnumServers?
Click to expand...

Sorry, it looks like I've got that one wrong. Or maybe I'm remembering an old Win16 API.

If you want to avoid SQLDMO, then you can use ODBC to enumerate the available servers. This is pretty involved from managed code, since you have to interop with ODBC. If you want some sample code, let me know.
 
Hmm, yeah that does look pretty involved from what i can see. If you have some example code already written that would be awsome :D. I'm not a fan of copying at all, but seeing the code in practical use is the best way to understand how it works so you can write your own i find.

Thanks for all your help btw, this is really helping :)
 
This doesn't find SQL Server 2005 instances. I'm looking into why it doesn't, but it should get you started:

Code: 
using System;
using System.Collections;
using System.Runtime.InteropServices;
using System.Text;

using System.Diagnostics;


namespace EnumerateServers
{
	/// <summary>
	/// interop for ODBC API
	/// </summary>
	internal class ODBCAPI 
	{
		[DllImport("odbc32.dll")]
		public static extern short SQLFreeHandle(
			HandleType nHandleType,
			int nHandleIn);
		[DllImport("odbc32.dll")]
		public static extern short SQLAllocHandle(
			HandleType nHandleType,
			int nHandleIn,
			out int nHandleOut);
		[DllImport("odbc32.dll")]
		public static extern short SQLSetEnvAttr(
			int nHandle,
			EnvOption nAttribute,
			int nValue,
			ValueType nSetting);
		[DllImport("odbc32.dll")]
		public static extern short SQLBrowseConnect(
			int ConnectionHandle,
			StringBuilder sbConnectionString,
			short nConnectionStringLen,	 
			StringBuilder sbOutConnectionString,
			short nOutConnectionStringLen,
			ref short nConnectionStringLength2);
		[DllImport("odbc32.dll")]
		public static extern short SQLSetConnectAttr(
			int handle,
			ConnectionOption nOption,
			Attributes nValue,
			ValueType nType);	
			
		public enum HandleType : short
		{
			EnvironmentHandle = 1,
			ConnectionHandle = 2
		};
		
		public enum EnvOption : int
		{
			ODBCVersion = 200,
		};
			
		public enum ConnectionOption : int
		{
			BrowseConnect = 1241,
			BrowseServer = 1242,
			BrowseCacheData = 1245
		}

		public enum Attributes : int
		{
			MoreInfoNo = 0,
			MoreInfoYes = 1,
			CacheDataYes = 1
		}
		
		public enum ValueType : int
		{
			INTEGER = -6,
			UINTEGER = -5
		}
	}

	/// <summary>
	/// Summary description for Class1.
	/// </summary>
	class Class
	{
		private const int SQL_NEED_DATA = 99;

		internal static bool EnumerateServers(ref ArrayList strReturnValue)
		{
			short result;
			int nHandleEnv;

			result = ODBCAPI.SQLAllocHandle(ODBCAPI.HandleType.EnvironmentHandle, 0, out nHandleEnv);
			if (result != 0)
			{
				Debug.WriteLine("EnumerateServers: SQLAllocHandle failed");
				return false;
			}
			else
			{
				// 3 == SQL_OV_ODBC3
				result = ODBCAPI.SQLSetEnvAttr(nHandleEnv,
					ODBCAPI.EnvOption.ODBCVersion,
					3,
					ODBCAPI.ValueType.UINTEGER);
				if (result != 0)
				{
					Debug.WriteLine("EnumerateServers: SetEnvAttr version failed");
					return false;
				}

				int nHandleDBC;
				result = ODBCAPI.SQLAllocHandle(ODBCAPI.HandleType.ConnectionHandle,
					nHandleEnv, out nHandleDBC);
				if (result != 0)
				{
					Debug.WriteLine("EnumerateServers: SQLAllocHandle failed");
					return false;
				}
				else
				{
					result = ODBCAPI.SQLSetConnectAttr(nHandleDBC,
						ODBCAPI.ConnectionOption.BrowseConnect,
						ODBCAPI.Attributes.MoreInfoNo,
						ODBCAPI.ValueType.UINTEGER);
						
					if (result == 0)
					{
						result = ODBCAPI.SQLSetConnectAttr(nHandleDBC,
							ODBCAPI.ConnectionOption.BrowseCacheData,
							ODBCAPI.Attributes.CacheDataYes,
							ODBCAPI.ValueType.INTEGER);
							
						if (result == 0)
						{
							StringBuilder sbServers = new StringBuilder(32767);
							StringBuilder sbInput = new StringBuilder("driver={sql server}");

							while (true)
							{
								// SQL_NTS == -3
								short nLengthOut = 0;
								result = ODBCAPI.SQLBrowseConnect(nHandleDBC, sbInput,
									-3, sbServers, (short) sbServers.Capacity, ref nLengthOut);
								if (result != SQL_NEED_DATA)
								{
									Debug.WriteLine("SQLBrowseConnect failed!");
									return false;
								}
							
								string strServerList = sbServers.ToString();
								int nStartIndex = strServerList.IndexOf("Server={");
								int nEndIndex = strServerList.LastIndexOf("}");
								if (nEndIndex == -1)
									nEndIndex = strServerList.Length;
								if (nEndIndex == 0 || nStartIndex == -1)
								{
									Debug.WriteLine("EnumerateServers: Ill-formed serverlist");
									return false;
								}
								else
								{
									int nIndex = nStartIndex + 8;
									int nNextIndex = 0;
									while (nNextIndex > -1 && nIndex < nEndIndex)
									{
										nNextIndex = strServerList.IndexOfAny(",} ".ToCharArray(), nIndex);
										if (nNextIndex != -1)
										{
											int nListEnd = strServerList.IndexOf('}', nIndex);
											int nServerEnd = strServerList.IndexOfAny(";,".ToCharArray(), nIndex);
											int nNameEnd = Math.Min(nListEnd, nServerEnd);
											if (nNameEnd != -1)
											{
												string strServerName = strServerList.Substring(nIndex, nNameEnd - nIndex);
												strReturnValue.Add(strServerName);
												nIndex = nNextIndex + 1;
											}
											else
												break;
										}
									}
								}
								
								if (nLengthOut < sbServers.Capacity)
									break;
							}
						}
					}
					ODBCAPI.SQLFreeHandle(ODBCAPI.HandleType.ConnectionHandle, nHandleDBC);
				}

				ODBCAPI.SQLFreeHandle(ODBCAPI.HandleType.EnvironmentHandle, nHandleEnv);
			}

			return true;
		}

		[STAThread]
		static void Main(string[] args)
		{
			ArrayList listServers = new ArrayList();

			if (EnumerateServers(ref listServers))
			{
				foreach (string strServer in listServers)
				{
					Console.WriteLine(strServer);
				}
				// it worked; dump them
			}
			else
			{
				// bad news
				Console.WriteLine("Enuemration failed\n");
			}
		 }
	}
}
 
You must log in or register to reply here.
Back
Top