SQL Server 查找器

• 下载源代码 - 16.1 KB

引言

您是否需要确定本地机器（无论是服务器还是个人电脑）上一个或多个 SQL Server（版本 2005、2000、7.0）实例的连接信息？

这里有一个简单的解决方案，可以解决一个令人头疼的问题。如果您是 DBA，正在进行 ODBC 或 JDBC 连接，那没什么大不了的。实际上，这是一种相当常规的操作。但对于所有其他尝试运行本地 SQL Server 实例的人来说，这并不容易。我无法告诉您有多少开发人员、设计师、测试工程师、报告编写人员和其他专业人士在这些努力中得到了我们的支持。

如果您能回忆起上次进行初始连接的时间，您可能正在点头并会心一笑。许多人很久以前就做过这件事了，可能已经忘记了从哪里获取这些信息……尤其是端口号。我相信很多人都像我一样，想知道为什么收集几个参数并建立连接会成为如此痛苦的经历。

背景

多年来，我一直帮助其他人获取第三方工具并仅仅连接到其 DBMS 的本地实例。

Using the Code

该实用工具使用起来很简单。您可以将文件放置在本地机器上的任何目录中。要运行该程序，请打开命令提示符，更改为放置文件的目录，然后键入以下命令行

> run_SQLServerFinder.bat

该程序将返回本地机器上任何 SQL Server 实例的服务器名称和端口号。以下是代码：

<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>

关注点

仅在 Google 上就有超过 50 万个索引页面与 SQL Server 的连接问题有关，关键词搜索如下：

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

历史

这是此实用工具的第一个版本。该程序的更新将在 这里 提供。