Recently a post on the MSDN forums asked how to import all SQL Servers on a Network into SSMS automatically. There actually is not a good implementation of this anywhere that I have found. So I worked out the following in C# as a console app. I am posting full source so that anyone can review what it is doing. I had the SQLLocator class from somewhere and I don't have a reference for it, but will figure out where I got it and provide credit here soon. It is not my work, but it does an amazing job for what I wanted to do, and it was open source.
using System;
using System.Collections.Generic;
using System.Text;
using System.Runtime.InteropServices;
using System.IO;
namespace SSMSImportGeneration
{
class Program
{
static void Main(string[] args)
{
string[] theAvailableSqlServers = SqlLocator.GetServers();
if (theAvailableSqlServers != null)
{
WriteFile(theAvailableSqlServers);
}
else
{
Console.WriteLine("No SQL servers found.");
}
}
private static void WriteFile(string[] data)
{
FileStream file = new FileStream("c:\\SQLImport.regsrvr", FileMode.OpenOrCreate, FileAccess.Write);
StreamWriter sw = new StreamWriter(file);
string importdata = "";
importdata = "<?xml version=\"1.0\" encoding=\"utf-8\"?>\n";
importdata += " <Export serverType=\"8c91a03d-f9b4-46c0-a305-b5dcc79ff907\">\n";
importdata += " <ServerType id=\"8c91a03d-f9b4-46c0-a305-b5dcc79ff907\" name=\"Database Engine\">\n";
importdata += " <Group name=\"SQL Locator Import\" description=\"Servers Located by SQL Locator and Imported into SSMS\">\n";
foreach (string server in data)
{
importdata += " <Server name=\"" + server + "\" description=\"\">\n";
importdata += " <ConnectionInformation>\n";
importdata += " <ServerType>8c91a03d-f9b4-46c0-a305-b5dcc79ff907</ServerType>\n";
importdata += " <ServerName>" + server + "</ServerName>\n";
importdata += " <AuthenticationType>0</AuthenticationType>\n";
importdata += " <UserName />\n";
importdata += " <Password />\n";
importdata += " <AdvancedOptions>\n";
importdata += " <PACKET_SIZE>4096</PACKET_SIZE>\n";
importdata += " <CONNECTION_TIMEOUT>15</CONNECTION_TIMEOUT>\n";
importdata += " <EXEC_TIMEOUT>0</EXEC_TIMEOUT>\n";
importdata += " <ENCRYPT_CONNECTION>False</ENCRYPT_CONNECTION>\n";
importdata += " </AdvancedOptions>\n";
importdata += " </ConnectionInformation>\n";
importdata += " </Server>\n";
}
importdata += " </Group>\n";
importdata += " </ServerType>\n";
importdata += " </Export>\n";
sw.Write(importdata);
sw.Close();
file.Close();
Console.WriteLine("File Created in Root c:\\SQLImport.regsrvr");
}
}
public class SqlLocator
{
[DllImport("odbc32.dll")]
private static extern short SQLAllocHandle(short hType, IntPtr inputHandle, out IntPtr outputHandle);
[DllImport("odbc32.dll")]
private static extern short SQLSetEnvAttr(IntPtr henv, int attribute, IntPtr valuePtr, int strLength);
[DllImport("odbc32.dll")]
private static extern short SQLFreeHandle(short hType, IntPtr handle);
[DllImport("odbc32.dll", CharSet = CharSet.Ansi)]
private static extern short SQLBrowseConnect(IntPtr hconn, StringBuilder inString,
short inStringLength, StringBuilder outString, short outStringLength,
out short outLengthNeeded);
private const short SQL_HANDLE_ENV = 1;
private const short SQL_HANDLE_DBC = 2;
private const int SQL_ATTR_ODBC_VERSION = 200;
private const int SQL_OV_ODBC3 = 3;
private const short SQL_SUCCESS = 0;
private const short SQL_NEED_DATA = 99;
private const short DEFAULT_RESULT_SIZE = 1024;
private const string SQL_DRIVER_STR = "DRIVER=SQL SERVER";
private SqlLocator() { }
public static string[] GetServers()
{
string[] retval = null;
string txt = string.Empty;
IntPtr henv = IntPtr.Zero;
IntPtr hconn = IntPtr.Zero;
StringBuilder inString = new StringBuilder(SQL_DRIVER_STR);
StringBuilder outString = new StringBuilder(DEFAULT_RESULT_SIZE);
short inStringLength = (short)inString.Length;
short lenNeeded = 0;
try
{
if (SQL_SUCCESS == SQLAllocHandle(SQL_HANDLE_ENV, henv, out henv))
{
if (SQL_SUCCESS == SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (IntPtr)SQL_OV_ODBC3, 0))
{
if (SQL_SUCCESS == SQLAllocHandle(SQL_HANDLE_DBC, henv, out hconn))
{
if (SQL_NEED_DATA == SQLBrowseConnect(hconn, inString, inStringLength, outString,
DEFAULT_RESULT_SIZE, out lenNeeded))
{
if (DEFAULT_RESULT_SIZE < lenNeeded)
{
outString.Capacity = lenNeeded;
if (SQL_NEED_DATA != SQLBrowseConnect(hconn, inString, inStringLength, outString,
lenNeeded, out lenNeeded))
{
throw new ApplicationException("Unabled to aquire SQL Servers from ODBC driver.");
}
}
txt = outString.ToString();
int start = txt.IndexOf("{") + 1;
int len = txt.IndexOf("}") - start;
if ((start > 0) && (len > 0))
{
txt = txt.Substring(start, len);
}
else
{
txt = string.Empty;
}
}
}
}
}
}
catch (Exception ex)
{
//Throw away any error if we are not in debug mode
#if (DEBUG)
Console.WriteLine(ex.Message, "Acquire SQL Servier List Error");
#endif
txt = string.Empty;
}
finally
{
if (hconn != IntPtr.Zero)
{
SQLFreeHandle(SQL_HANDLE_DBC, hconn);
}
if (henv != IntPtr.Zero)
{
SQLFreeHandle(SQL_HANDLE_ENV, hconn);
}
}
if (txt.Length > 0)
{
retval = txt.Split(",".ToCharArray());
}
return retval;
}
}
}
