SQL Server Finder

• Download source code - 16.1 KB

Introduction

Do you need to determine connection information for one or more instances of SQL Server (versions 2005, 2000, 7.0) on your local machine (be it a server or a PC)?

Here is a simple solution to a problem that is a royal pain to deal with. Sure, if you are a DBA making an ODBC or a JDBC connection, it is not a big deal. Actually, it is a pretty routine affair. But to every one else trying to run a local instance of SQL Server, it is not so easy. I can't tell you how many developers, designers, test engineers, report writers, and other professionals we have supported over the years in this very endeavor.

If you can remember the last time you had to make an initial connection, you are probably nodding your head and chuckling. Many of you did it so long ago, you probably forgot where to get this information... especially the port number. I am sure there are many of you out there like me who wonder why gathering a few parameters and making a connection can be such a traumatic experience.

Background

I have assisted others countless times for many years in getting third party tools and just getting connected to local instances of their DBMS.

Using the Code

The utility is simple to use. You can place the files in any directory on your local machine. To run the program, open up the command prompt, change the directory to where you placed the files, and type the following command line:

> run_SQLServerFinder.bat

The program will return the server name and the port number for any instance of SQL Server that is on your local machine. Shown below is the code:

<Job id="SQLServerFinder">
<Script language="JScript" src="Class_Connection.js"></Script>
<Script language="JScript" src="Common_File_Function.js"></Script>
<Script language="JScript">
    var sql2005_path = 
        "HKEY_LOCAL_MACHINE\\Software\\Microsoft\\
        Microsoft SQL Server\\MSSQL.1\\Setup\\SQLPath";
    var sql2000_path = "HKEY_LOCAL_MACHINE\\Software\\
        Microsoft\\MSSQLServer\\Setup\\SQLPath";
      
      var sRegTypes;
      var HKLM = 0x80000002;
      var oReg;
      
      var myArray_sqlpath = new Array(100);
      var myArray_index   = 0;
      main();
function main ()
{        
      find_enum_subkey_main( "Software\\Microsoft\\Microsoft SQL Server" ); 
      
      find_enum_subkey_main( "Software\\Microsoft\\MSSQLServer" ); 
           
      find_all_sql_instances ();
}
function find_all_sql_instances ()
{     
      for (var i = 0; i < myArray_index; i++)
      {         
          show_host_name ( myArray_sqlpath[i] );
      }      
}
function find_enum_subkey_main( sRegPath )
{
    //WScript.Echo( "Searching Registry Key = 
    //    HKEY_LOCAL_MACHINE\\" + sRegPath + "\n" );    
    sRegTypes = new Array(
                           "                              ",    // 0
                           "REG_SZ                        ",    // 1
                           "REG_EXPAND_SZ                 ",    // 2
                           "REG_BINARY                    ",    // 3
                           "REG_DWORD                     ",    // 4
                           "REG_DWORD_BIG_ENDIAN          ",    // 5
                           "REG_LINK                      ",    // 6
                           "REG_MULTI_SZ                  ",    // 7
                           "REG_RESOURCE_LIST             ",    // 8
                           "REG_FULL_RESOURCE_DESCRIPTOR  ",    // 9
                           "REG_RESOURCE_REQUIREMENTS_LIST",    // 10
                           "REG_QWORD                    ");    // 11
    try
    {
       oLoc    = new ActiveXObject("WbemScripting.SWbemLocator");
       oSvc    = oLoc.ConnectServer(null, "root\\default");    
       oReg    = oSvc.Get("StdRegProv");
       oMethod = oReg.Methods_.Item("EnumKey");
    
       oInParam             = oMethod.InParameters.SpawnInstance_();
       oInParam.hDefKey     = HKLM;
       oInParam.sSubKeyName = sRegPath;
       oOutParam            = oReg.ExecMethod_(oMethod.Name, oInParam);
    
       aNames = oOutParam.sNames.toArray();
       for (var i = 0; i < aNames.length; i++)
       {
           if ( aNames[i] == "80" || aNames[i] == "90" )
           {
              continue;
           }
           //WScript.Echo(" SubKeyName: ", aNames[i]);
           find_enum_subkey( sRegPath + "\\" + aNames[i] );
       }     
    }
    catch( err )
    {
       WScript.Echo("Error occurred\n" + "Descriptions: " + err.description);
    }
}
function find_enum_subkey( sRegPath )
{
    //WScript.Echo( 
    //"Sub : Registry Key = HKEY_LOCAL_MACHINE\\" + sRegPath + "\n" ); 
    try
    {
       var oMethod          = oReg.Methods_.Item("EnumKey");    
       var oInParam         = oMethod.InParameters.SpawnInstance_();
       oInParam.hDefKey     = HKLM;
       oInParam.sSubKeyName = sRegPath;
       var oOutParam        = oReg.ExecMethod_(oMethod.Name, oInParam);
    
       if ( oOutParam.sNames == null )
       {
          find_subkeyname( sRegPath );
          return;
       }
       
       var aNames = oOutParam.sNames.toArray();
       for (var i = 0; i < aNames.length; i++)
       {
           if ( aNames[i] == "80" || aNames[i] == "90" )
           {
              continue;
           }
           //WScript.Echo(" SubKeyName: ", aNames[i]);
           find_enum_subkey( sRegPath + "\\" + aNames[i] );
       } 
       
       //WScript.Echo("\n");    
    }
    catch( err )
    {
       WScript.Echo("Error occurred\n" + "Descriptions: " + err.description);
    }
}
function find_sql2005 ()
{
      try
      {
         sql2005_instance_dir = g_Shell.RegRead( sql2005_path ); 
         //WScript.echo( "SQL 2005 path = " + sql2005_instance_dir );
      }
      catch (e)
      { 
         WScript.echo( "The error messages are:\r\n\r\n" + e.description );
      }    
}
function find_sql2000 ()
{
      try
      {
         sql2000_instance_dir = g_Shell.RegRead( sql2000_path ); 
         //WScript.echo( "SQL 2000 path = " + sql2000_instance_dir );
      }
      catch (e)
      { 
         WScript.echo( "The error messages are:\r\n\r\n" + e.description );
      }      
}
function show_host_name ( instance_dir )
{
      try
      {
        var error_log_file = instance_dir + "\\LOG\\ERRORLOG";
      
        //WScript.echo( "Parsing Log File = " + error_log_file + "\n" );
        
        var file       = fso.GetFile( error_log_file );
        var fileStream = file.OpenAsTextStream(ForReading, 
            TristateUseDefault);
        var line, pos, pos2;
        var server_name = "Unknown";
        var port_info   = "";
        
        while (!fileStream.atEndOfStream)
        {
            line = fileStream.ReadLine();
            pos  = line.indexOf("Server name is");
            
            if (pos >= 0)
            { 
               pos  = line.indexOf("'");
               pos2 = line.indexOf("'", pos + 1); 
               server_name = line.substring(pos + 1, pos2);
            } 
            else
            {
               pos = line.indexOf("listening on");
               if (pos >= 0)
               {
                  port_info += " " + line.substring(pos + 12); 
               } 
               else
               {
                  pos = line.indexOf("listen on");
                  if (pos >= 0)
                  {
                     port_info += " " + line.substring(pos + 9); 
                  } 
               }           
            }        
        }
        WScript.echo( 
            "The Server name is " + server_name + 
            "\nThe Server name is listening on" + port_info + "\n" );
        fileStream.Close();
      }
      catch (e)
      { 
         //WScript.echo( "The error messages are:\r\n\r\n" + e.description );
      }    
}
function find_subkeyname( sRegPath )
{
    //WScript.Echo( "Get SubKeyName From Registry Key = 
    //    HKEY_LOCAL_MACHINE\\" + sRegPath + "\n" );    
    try
    {
       var oMethod          = oReg.Methods_.Item("EnumValues");
       var oInParam         = oMethod.InParameters.SpawnInstance_();
       oInParam.hDefKey     = HKLM;
       oInParam.sSubKeyName = sRegPath;
       var oOutParam        = oReg.ExecMethod_(oMethod.Name, oInParam);
    
       if ( oOutParam.sNames == null )
       {
          return;
       }
       var aNames = oOutParam.sNames.toArray();
       var aTypes = oOutParam.Types.toArray();
       for (var i = 0; i < aNames.length; i++)
       {
           //WScript.Echo(" KeyName: ", aNames[i]);
           if ( aNames[i] == "SQLDataRoot" || aNames[i] == "SQLPath" )
           {
              find_sqlpath_regkey_value (
                  "HKEY_LOCAL_MACHINE\\" + sRegPath + "\\" + aNames[i] );
           }
       }
    }
    catch( err )
    {
       WScript.Echo("Error occurred\n" + "Descriptions: " + err.description);
    }
}
function find_sqlpath_regkey_value ( strKeyPath )
{
      try
      {
         var strKeyValue = g_Shell.RegRead( strKeyPath ); 
         //WScript.echo( "SQLPath Value = " + strKeyValue ); 
         if ( strKeyValue.indexOf("\\OLAP\\") == -1 )
         {      
            if ( check_dup_key_vale ( strKeyValue ) == 0 )
            {       
               myArray_sqlpath[myArray_index++] = strKeyValue;
            }
         }
      }
      catch (e)
      { 
         WScript.echo( "The error messages are:\r\n\r\n" + e.description );
      }      
}
function check_dup_key_vale ( key_value )
{     
      for (var i = 0; i < myArray_index; i++)
      {         
          if ( myArray_sqlpath[i] == key_value )
          {
             return 1;
          }
      }
      return 0;
}
</Script>
</Job>

Points of Interest

There are over 500,000 indexed pages on Google alone pertaining to connection problems with SQL Server, under the following keyword searches:

• [connection problems "sql server"]
• [troubleshooting connection "sql server"]
• ["hard to connect" "sql server"]
• [connect* pain "sql server"]

History

This is the first version of this utility. Updates for this program will be available here.