How to Connect to Sqlserver2008 using SMO any work

问题:

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.

回答1:

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