1

I wrote this for finding the sql server instances on the local machine:

using System;
using System.Data;
using Microsoft.SqlServer.Management.Smo;

namespace Application3
{
    class Program
    {
        static void Main(string[] args)
        {

            string srvname = string.Empty; string srvnames = null;

             DataTable dt = SmoApplication.EnumAvailableSqlServers(true);

             Console.WriteLine("------------->" + dt.Rows.Count);

             foreach (DataRow dr in dt.Rows)
             {
               try{

                Console.WriteLine("-->Instance " + dr["name"]);

                 Server srv = new Server((string)dr["name"]);

                  foreach (Database db in srv.Databases)

                      Console.WriteLine(db.Name);
              }catch(Exception e)
           {
               Console.writeLine(e.toString());
            }
        }
    }
}

I have 3 instances in my local machine

  1. rk2k3-vm-sr (sql2008 instance)
  2. rk2k3-vm-sr\sql2k8express (sql2k8 instacne)
  3. rk2k3-vm-sr\sqlexpress (sql2k5 instance)

But it is only showing 2 and 3. 1 does not show. And when I connect using server object it fails for sql2k8express.

Here is the Output....


-->Instance RK2K3-VM-SR\SQLEXPRESS
master
model
msdb
tempdb


-->Instance RK2K3-VM-SR\SQL2K8EXPRESS

Microsoft.SqlServer.Management.Common.ConnectionFailureException: Failed to conn ect to server RK2K3-VM-SR\SQL2K8EXPRESS. ---> Microsoft.SqlServer.Management.Com mon.ConnectionFailureException: This SQL Server version (10.0) is not supported. at Microsoft.SqlServer.Management.Common.ConnectionManager.CheckServerVersion (ServerVersion version) at Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnect(Wi ndowsIdentity impersonatedIdentity) at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect() --- End of inner exception stack trace --- at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect() at Microsoft.SqlServer.Management.Common.ConnectionManager.PoolConnect() at Microsoft.SqlServer.Management.Common.ConnectionManager.get_ServerVersion( ) at Microsoft.SqlServer.Management.Smo.ExecutionManager.get_ServerVersion() at Microsoft.SqlServer.Management.Smo.SqlSmoObject.GetDbComparer(Boolean inSe rver) at Microsoft.SqlServer.Management.Smo.SqlSmoObject.InitializeStringComparer() at Microsoft.SqlServer.Management.Smo.AbstractCollectionBase.get_StringCompar er() at Microsoft.SqlServer.Management.Smo.SimpleObjectCollectionBase.InitInnerCol lection() at Microsoft.SqlServer.Management.Smo.SmoCollectionBase.get_InternalStorage() at Microsoft.SqlServer.Management.Smo.SmoCollectionBase.InitializeChildCollec tion(Boolean refresh) at Microsoft.SqlServer.Management.Smo.SmoCollectionBase.GetEnumerator() at ConsoleApplication3.Program.Main(String[] args) in C:\Documents and Settin gs\Administrator.APP\Desktop\ConsoleApplication3\Program.cs:line 25

How can i fix this? My System firewall is disabled and all sql services are running.

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
Cute
  • 13,643
  • 36
  • 96
  • 112

1 Answers1

3

Is this code previously written for SQL 2005? If yes you need to

  1. Install SQL 2005 Backwards Compatibilty Pack for SQL 2008 + Management Objects on the machine. (http://www.microsoft.com/downloads/details.aspx?FamilyID=b33d2c78-1059-4ce2-b80d-2343c099bcb4&displaylang=en)

  2. Remove references to SDK dlls of SLQ 2005 & re reference to SK Dlls of SQL 2008. (http://msdn.microsoft.com/en-us/library/ms162129.aspx)

  3. Rebuild.

Also refer to: Application cannot find Microsoft.SQLServer.SMO on SQL 2008 machine

Community
  • 1
  • 1
Ganesh R.
  • 4,337
  • 3
  • 30
  • 46
  • No it is exclusively written for sql2008 – Cute Jul 16 '09 at 06:17
  • Previously DMO used to Discover sql2k8 i used SMo but it shows the above Problems. – Cute Jul 16 '09 at 06:19
  • try running DataTable dt = SmoApplication.EnumAvailableSqlServers(true) twice. Reason: The computer running the instance SQL Server might not receive responses to the EnumAvailableSqlServers method in a timely manner. The returned list might not show all the available instances of SQL Server on the network. When you call the EnumAvailableSqlServers method in subsequent tries, more servers might become visible on the network. This method fails to list the local instance if a firewall is operating on the server. The firewall blocks any broadcast traffic issued – Ganesh R. Jul 16 '09 at 06:26
  • "(i make sql2k8 default instance.and named instance of sql2k8 is displayed)"? I did not understand. You have to specify if you need to install SQL Server with default instance at install time. I dont think you can do it on the fly. – Ganesh R. Jul 16 '09 at 09:35
  • My question was Is your default instance installed?? You will be sure if you see "SQL Server (MSSQLSERVER)" in services.msc. Else you don't have a default instance of SQL installed. – Ganesh R. Jul 16 '09 at 11:22
  • Yes Mydefault Instance installed. – Cute Jul 17 '09 at 06:16