locked
How to get a list of all the visible MS SQL Server instances? RRS feed

  • Question

  • I'm trying to use GetDataSource to return all the visible MS SQL Server Instances.  It is sort of working but it is not showing all the visible MS SQL Server Instances.  For example, I have 2 instances on a 64bit machine: SQLEXPRESS 32 bit and default instance of SQL Server 2005 64bit.   Executing GetDataSource on that machine will only return the SQLEXPRESS instance but not default instance.  I can see both instances in another machine.

     

    Also, if SQL Server Browser Service is not running, even the SQLEXPRESS instance will not be returned.  It makes sense to me for instances on other machines but not local instances.

     

     

    Monday, December 17, 2007 9:33 PM

Answers

  • Hello,

     

    Sorry to be so late, but it's really difficult to connect to us forums ( i've to go through french forums but it needs about 30 minutes)

     

    My prog ( in VB 2005 is giving :

     

    Microsoft Windows XP [version 5.1.2600]
    (C) Copyright 1985-2001 Microsoft Corp.

    E:\Documents and Settings\patrick>d:

    D:\>cd vb2005\projects\smotest

    D:\vb2005\Projects\SMOTest>dir
     Le volume dans le lecteur D s'appelle Données
     Le numéro de série du volume est 0EC0-4A30

     Répertoire de D:\vb2005\Projects\SMOTest

    21/12/2007  10:50    <REP>          .
    21/12/2007  10:50    <REP>          ..
    21/12/2007  13:42    <REP>          SMOTest
    21/12/2007  10:50               917 SMOTest.sln
                   1 fichier(s)              917 octets
                   3 Rép(s)  174 269 464 576 octets libres

    D:\vb2005\Projects\SMOTest>cd smotest

    D:\vb2005\Projects\SMOTest\SMOTest>dir
     Le volume dans le lecteur D s'appelle Données
     Le numéro de série du volume est 0EC0-4A30

     Répertoire de D:\vb2005\Projects\SMOTest\SMOTest

    21/12/2007  13:42    <REP>          .
    21/12/2007  13:42    <REP>          ..
    21/12/2007  13:50    <REP>          bin
    21/12/2007  13:42             4 183 Module1.vb
    21/12/2007  11:37                61 Module2.vb
    21/12/2007  10:50    <REP>          My Project
    21/12/2007  13:50    <REP>          obj
    21/12/2007  12:15             4 717 SMOTest.vbproj
                   3 fichier(s)            8 961 octets
                   5 Rép(s)  174 269 464 576 octets libres

    D:\vb2005\Projects\SMOTest\SMOTest>cd bin\debug

    D:\vb2005\Projects\SMOTest\SMOTest\bin\Debug>smotest.exe
    Test for GetDataSource() and SMO/WMI
    Wait for execution of GetDataSources()
    Visible instances count : 2
    --- Instance 1
        ServerName : CHAMBRE  InstanceName : SQLEXPRESS  Version : 9.00.3042.00
    Test service Sql Server on CHAMBRE SQLEXPRESS
    Service MSSQL$SQLEXPRESS Type SqlServer State Running
    Service SQLBrowser Type SqlBrowser State Running
    Service MSSQL$SQLEXPRESS exists  State : Running
    Wait for start of MSSQL$SQLEXPRESS
    Service MSSQL$SQLEXPRESS : Running
    --- Instance 2
        ServerName : LAMBIN-274404DC  InstanceName :   Version :

    Answer yes to redo the GetDataSources()

    Answer :
    Answer yes to go on
    n

    D:\vb2005\Projects\SMOTest\SMOTest\bin\Debug>dir
     Le volume dans le lecteur D s'appelle Données
     Le numéro de série du volume est 0EC0-4A30

     Répertoire de D:\vb2005\Projects\SMOTest\SMOTest\bin\Debug

    21/12/2007  13:45    <REP>          .
    21/12/2007  13:45    <REP>          ..
    21/12/2007  13:42    <REP>          fr
    22/09/2007  20:27           363 376 Microsoft.SqlServer.BatchParser.dll
    10/02/2007  04:23         1 626 480 Microsoft.SqlServer.Replication.dll
    21/12/2007  13:42            28 672 SMOTest.exe
    21/12/2007  13:42            32 256 SMOTest.pdb
    09/12/2005  09:36             5 632 SMOTest.vshost.exe
    21/12/2007  13:42               115 SMOTest.xml
                   6 fichier(s)        2 056 531 octets
                   3 Rép(s)  174 267 224 064 octets libres

    D:\vb2005\Projects\SMOTest\SMOTest\bin\Debug>cd ..

     

    The code is :

    Imports System.Data

    Imports System.Data.Sql

    Imports System.Text

    Imports Microsoft.SqlServer.Management.Smo

    Imports Microsoft.SqlServer.Management.Smo.Wmi

    Module Module1

    Public Dt As DataTable

    Public str As String

    Public NoRow As Int32

    Sub Main()

    Console.WriteLine("Test for GetDataSource() and SMO/WMI")

    str = "yes"

    While (str.Equals("yes"))

    FillDt()

    Console.WriteLine("Answer : {0}", str)

    End While

    Console.WriteLine("Answer yes to go on")

    str = Console.ReadLine()

    If (Not str.Equals("yes")) Then

    Return

    End If

    End Sub

    Sub FillDt()

    Dim str1 As String

    Dim inst As SqlDataSourceEnumerator = SqlDataSourceEnumerator.Instance

    Console.WriteLine("Wait for execution of GetDataSources()")

    Dt = inst.GetDataSources()

    Console.WriteLine("Visible instances count : {0}", Dt.Rows.Count.ToString())

    Dim num As Int32

    num = 0

    For Each row As DataRow In Dt.Rows

    num = num + 1

    Dim bu As StringBuilder = New StringBuilder()

    bu.Append(" ServerName : ")

    bu.Append(row("ServerName").ToString())

    bu.Append(" InstanceName : ")

    bu.Append(row("InstanceName").ToString())

    bu.Append(" Version : ")

    bu.Append(row("Version").ToString())

    str1 = bu.ToString

    Console.WriteLine("--- Instance {0}", num.ToString())

    Console.WriteLine(str1)

    If (row("ServerName").ToString().Equals(Environment.MachineName)) Then

    NoRow = num - 1

    TestService()

    End If

    Next

    Console.WriteLine(" ")

    Console.WriteLine("Answer yes to redo the GetDataSources()")

    str = Console.ReadLine()

    ' i choose the first instance because i'm working on the same computer

    ' the both computers are belonging to a workgroup and not a domain

    ' so it's impossible to use WMI ( the 2nd computer is a XP Home !!! )

    End Sub

    Sub TestService()

    Dim row As DataRow

    Dim servicename As String

    row = Dt.Rows(NoRow)

    Console.WriteLine("Test service Sql Server on {0} {1}", row("ServerName").ToString(), row("InstanceName").ToString())

    Dim mc As ManagedComputer = New ManagedComputer(row("ServerName").ToString())

    Dim servicecoll As ServiceCollection

    servicecoll = mc.Services

    For Each serv As Service In servicecoll

    Console.WriteLine("Service {0} Type {1} State {2}", serv.Name, serv.Type.ToString(), serv.ServiceState.ToString())

    Next

    servicename = "MSSQL"

    If (String.IsNullOrEmpty(row("InstanceName").ToString())) Then

    servicename = servicename & "SERVER"

    Else

    servicename = servicename & "$" & row("InstanceName").ToString()

    End If

    If (Not servicecoll.Contains(servicename)) Then

    Console.WriteLine("Service {0} : unknown", servicename)

    Return

    End If

    Console.WriteLine("Service {0} exists State : {1}", servicename, servicecoll(servicename).ServiceState.ToString())

    Dim sqlserver As Service = servicecoll(servicename)

    If (sqlserver.ServiceState.Equals(ServiceState.Running)) Then

    Console.WriteLine("Service {0} : {1}", servicename, sqlserver.ServiceState.ToString())

    Return

    End If

    Console.WriteLine("Wait for start of {0}", servicename)

    sqlserver.Start()

    While (Not sqlserver.ServiceState.Equals(ServiceState.Running))

    'Console.WriteLine("{0} : {1}", servicename, sqlserver.ServiceState.ToString())

    sqlserver.Refresh()

    End While

    Console.WriteLine("Service {0} : {1} Well done", servicename, sqlserver.ServiceState.ToString())

    End Sub

    End Module

     

    It' a console project. It is not finished : especially no try/catch and so on but it works.

    The computer CHAMBRE is the local computer XP Pro ). The other computer is a XP Home.

    Some problems with the last one ( only the ServerName is returned and other fields are empty ). so to a retry

     

    With SMO, you can do:

    backups ( even with SQL Server 2005 Express Edition )

    create databases

    explore databases

    Dim serveur1 as Server = new Server("ServerName\InstanceName")

    Server.DataBases is giving a collection of databases on the instance

     

    You can do many things. But with WMI, you must be careful as it's well working on the computer where the Sql Server instance resides. In case it's not the case, you would struggle with security of WMI and it is not a funny job

     

    I hope this will help you.

    I prefer to give you VB 2005 code as i have tested it ( you have results for SQL Server running or not running )

     

    Good luck and have a nice day

     

    Remarks : Too many troubles when i installed the CTP version of VC# 20005

    I had to reinstall Windows. So I have not downloaded VC2008 and SQL Server 2008

     

     

    Friday, December 21, 2007 2:34 PM
  • (I don't think it's possible with T-SQL to start the service SQL Server ( if i'm in error , i will be glad to learn how )

     

    The current SQL Server Microsoft says it is not good practice to start the service in a program and in ADO.NET it takes a few lines of code to create a database and all the objects in it using command objects.

     

    (i found in SQL Server 2008 BOL,a page about ManagerComputer . I read that's only working with Framework 2.0 , i am supprised because i thought that Framework 3.0 and 3.5 were mainly extensions of the Framework 2.0.)

     

    You know your DMO(data management object) code is obsolete now SMO(sql server management object) is here for how long?  T-SQL must be be close to ANSI SQL standard so less changes more stable code.

     

    Saturday, December 22, 2007 6:05 PM

All replies

  • That is actually very simple the Express is only 32bits so it must be installed in WOW64 and the 64bits is in the default SQL Server directory.  However you could run these two both should return every instance in your box.  Another thing to know even in the 64bits not all subsystems are 64bits you have to check the BOL(books online) for details. Try the links below for details.

     

    http://msdn2.microsoft.com/en-us/library/ms187944.aspx

     

    http://msdn2.microsoft.com/en-us/library/ms174396.aspx

     

     

     

    Monday, December 17, 2007 11:25 PM
  • Hi Caddre,

     

    I'm confused about your reply.  Are you saying that GetDataSource should able to return all the instances regardless whether they are 32bits and 64bits? 

     

    Regarding the subsystems, I'm not sure what you're referring to.   My machine OS is Windows XP Profession 64bit and the default SQL Server is Developer Edition 64bit.

     

    Tuesday, December 18, 2007 12:27 AM
  • (Are you saying that GetDataSource should able to return all the instances regardless whether they are 32bits and 64bits)

     

    No what I am saying the ones I gave to you in the links will return both 32bits and 64bits but if you configured both SQL Server after installation you should see both in server explorer which means you just change connection property.


    (Regarding the subsystems, I'm not sure what you're referring to.   My machine OS is Windows XP Profession 64bit and the default SQL Server is Developer Edition 64bit)

     

    SQL Server is very big some of the components like some parts integration services, Business Intelligence management studio and Analysis services in the 64bits version are in 32bits.  Check the BOL for the complete list.

     

    Tuesday, December 18, 2007 2:26 PM
  • Hi Caddre,

     

    I'm trying to get a list of SQL Servers on the domain inside a .NET application.

     

    I have 2 instances on a Windows XP Professional 64bit: Express Edition and Developer Edition 64bit.

     

    I have no problem seeing the Instances in Object Explorer in SQL Server Management Studio.

     

    However, I have problem seeing both instances in VS2008 Server Explorer or thru .NET coding (GetDataSource, SMO).

     

    When SQL Browser service is running, I only see the Express Edition.  When SQL Browser service is not running, I only see the Developer Edition.

    Wednesday, December 19, 2007 7:05 PM

  • Here are the reasons one you are using the wrong SMO class and two there are known issues with the correct class not returning all instances.  The first link is the correct class, second is many code samples doing what you want in both VB and C# modify for your needs and the last link includes alternative code from a SQL Server program manager and the known issues with the class.


    http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.smoapplication.enumavailablesqlservers.aspx

     

    http://www.sqldbatips.com/showarticle.asp?ID=45

     

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=198078&SiteID=1

     

     

    (When SQL Browser service is running, I only see the Express Edition.  When SQL Browser service is not running, I only see the Developer Edition)

     

    The developer edition and Express edition comes with most features disabled by default so spend time with your configuration manager and surface area configuration tool.  There are a lot of things you need to enable for use with Visual Studio.

     

    Thursday, December 20, 2007 12:50 AM
  •  

    Hi Caddre,

     

    Thanks for the links.  I'm already using the correct SMO class.  I'm hitting the known issue in the last link.  I will try the alternative code.  

     

    Since this is a known issue for over a year, has MS fixed it for .NET 2.0 or have another SMO class to handle it in .NET 3.5?

     

    I wonder what coding VS2008 Server Explorer use since it has the same problem.

     

     

     

     

     

     

     

    Thursday, December 20, 2007 2:11 AM
  • SMO(sql server management object) is not actually .NET it is just defined in .NET so new classes if any will come next year with SQL Server 2008.  But you know that the T-SQL version I gave to you in my first post works so it is really not an issue with the SQL Server team.

     

    Thursday, December 20, 2007 2:22 AM
  • Hi Caddre,

     

    I do notice that SMO is part of SQL Server.  I have checked the SQL Server 2008 BOL (october 2007)  and I don't see the ManagedComputer Class.   I have not checked the BOL (Nov 2007).  I guess I should install SQL Server 2008 CTP (Nov).

     

    I think it is a .NET issue since other .NET coding (GetDataSource) has the same problem.

     

    Thursday, December 20, 2007 2:36 AM
  • Hello,

     

    ManagedComputer exists in .NET 2.0

    You have to add a reference to

     -  Microsoft.SqlServer.ConnectionInfo

     -  Microsoft.SqlServer.Smo

     -  Microsoft.SqlServer.SMOEnum

     -  Microsoft.SqlServer.SqlEnum

     -  Microsoft.SqlServer.WMIEnum

     

    You have to add  ( in C# )

    using Microsoft.SqlServer.Management.Common;

    using Microsoft.SqlServer.Management.Smo;

    using Microsoft.SqlServer.Management.Smo.Wmi;

     

    for VB same way but replace using by Imports and no ; at the end of line

     

    I have the same problem : no view of a default instance ( with SQL Server 2005 Express Edition )

    So i decide to create always instances with name ( named instances ).

    Moreover, if you try thru a program , to start a Sql Server service , you will have a small problem

     for a default instance : the name of the service is MSSQLSERVER

    for a named instance "MyInstance" , the name of the same service is MSSQL$MyInstance

     

    My way to retrieve the visible instances is

     

    using System.Data.Sql;

     

     

    DataTable dt;

    SqlDataSourceEnumerator instance = SqlDataSourceEnumerator.Instance;

    Commun.Dt = instance.GetDataSources();

    InstanceDgv.DataSource = Commun.Dt;

    InstancesDgv is a DataGridView in which i transfer Dt

     

    This method is OK , with one exception.

    If the instance is not on the computer where the program is executing, you have to reexecute your load code to be sure you get all the instances. Except for the default instances which gives some problems

    you can see :

     http://msdn2.microsoft.com/en-us/library/system.data.sql.sqldatasourceenumerator.getdatasources(VS.80).aspx

     

    especially :

    Due to the nature of the mechanism used by SqlDataSourceEnumerator to locate data sources on a network, the method will not always return a complete list of the available servers, and the list might not be the same on every call. If you plan to use this function to let users select a server from a list, make sure that you always also supply an option to type in a name that is not in the list, in case the server enumeration does not return all the available servers. In addition, this method may take a significant amount of time to execute, so be careful about calling it when performance is critical

     

    I'm hoping this post will help you.

    I may post some code about WMI  if you are willing ( in C# i'm not very fond of VB )

     

    Have a nice day

    and sorry for my poor english

    Thursday, December 20, 2007 11:13 AM
  • Hi Papy,

     

    Your english is fine and I have no problem understanding your post.

     

    I think you may have misunderstood my statement regarding ManagedComputer class.  If I understand correctly, SMO classes are installed with SQL Server 2005 SDK coption.  I was looking at the Sept 2007 BOL of SQL Server 2008, ManagedComputer class was not listed.  However, since the BOL is not final, I need to installed SQL Server 2008 CTP (Nov 2007) to find out whether ManagedComputer class is still available or not.

     

    I have already tried the following ways to get a list of SQL Server Instances:

    • SmoApplication.EnumAvailableSqlServers
    • DbProviderFactory.CreateDataSourceEnumerator
    • SqlDataSourceEnumerator

    All of them have the problem returning the default instance when Express instance co-exists on the same machine.  I believe DbProviderFactory.CreateDataSourceEnumerator and SqlDataSourceEnumerator are pretty much the same.

     

    Based on this link provided by Caddre: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=198078&SiteID=1, using ManagedComputer class in Smo.Wmi may be a workaround for the problem.

     

    If you can post the ManagedComputer code in C#, it will be helpful since I can use C# to VB converter to convert your code to VB.

     

    I'm using VS2008 and the routine is to be written in VB for both SQL Server 2005 and SQL Server 2008 instances.

     

    Thanks,

     

    Peter

    Thursday, December 20, 2007 7:13 PM
  • Hello,

     

    Sorry to be so late, but it's really difficult to connect to us forums ( i've to go through french forums but it needs about 30 minutes)

     

    My prog ( in VB 2005 is giving :

     

    Microsoft Windows XP [version 5.1.2600]
    (C) Copyright 1985-2001 Microsoft Corp.

    E:\Documents and Settings\patrick>d:

    D:\>cd vb2005\projects\smotest

    D:\vb2005\Projects\SMOTest>dir
     Le volume dans le lecteur D s'appelle Données
     Le numéro de série du volume est 0EC0-4A30

     Répertoire de D:\vb2005\Projects\SMOTest

    21/12/2007  10:50    <REP>          .
    21/12/2007  10:50    <REP>          ..
    21/12/2007  13:42    <REP>          SMOTest
    21/12/2007  10:50               917 SMOTest.sln
                   1 fichier(s)              917 octets
                   3 Rép(s)  174 269 464 576 octets libres

    D:\vb2005\Projects\SMOTest>cd smotest

    D:\vb2005\Projects\SMOTest\SMOTest>dir
     Le volume dans le lecteur D s'appelle Données
     Le numéro de série du volume est 0EC0-4A30

     Répertoire de D:\vb2005\Projects\SMOTest\SMOTest

    21/12/2007  13:42    <REP>          .
    21/12/2007  13:42    <REP>          ..
    21/12/2007  13:50    <REP>          bin
    21/12/2007  13:42             4 183 Module1.vb
    21/12/2007  11:37                61 Module2.vb
    21/12/2007  10:50    <REP>          My Project
    21/12/2007  13:50    <REP>          obj
    21/12/2007  12:15             4 717 SMOTest.vbproj
                   3 fichier(s)            8 961 octets
                   5 Rép(s)  174 269 464 576 octets libres

    D:\vb2005\Projects\SMOTest\SMOTest>cd bin\debug

    D:\vb2005\Projects\SMOTest\SMOTest\bin\Debug>smotest.exe
    Test for GetDataSource() and SMO/WMI
    Wait for execution of GetDataSources()
    Visible instances count : 2
    --- Instance 1
        ServerName : CHAMBRE  InstanceName : SQLEXPRESS  Version : 9.00.3042.00
    Test service Sql Server on CHAMBRE SQLEXPRESS
    Service MSSQL$SQLEXPRESS Type SqlServer State Running
    Service SQLBrowser Type SqlBrowser State Running
    Service MSSQL$SQLEXPRESS exists  State : Running
    Wait for start of MSSQL$SQLEXPRESS
    Service MSSQL$SQLEXPRESS : Running
    --- Instance 2
        ServerName : LAMBIN-274404DC  InstanceName :   Version :

    Answer yes to redo the GetDataSources()

    Answer :
    Answer yes to go on
    n

    D:\vb2005\Projects\SMOTest\SMOTest\bin\Debug>dir
     Le volume dans le lecteur D s'appelle Données
     Le numéro de série du volume est 0EC0-4A30

     Répertoire de D:\vb2005\Projects\SMOTest\SMOTest\bin\Debug

    21/12/2007  13:45    <REP>          .
    21/12/2007  13:45    <REP>          ..
    21/12/2007  13:42    <REP>          fr
    22/09/2007  20:27           363 376 Microsoft.SqlServer.BatchParser.dll
    10/02/2007  04:23         1 626 480 Microsoft.SqlServer.Replication.dll
    21/12/2007  13:42            28 672 SMOTest.exe
    21/12/2007  13:42            32 256 SMOTest.pdb
    09/12/2005  09:36             5 632 SMOTest.vshost.exe
    21/12/2007  13:42               115 SMOTest.xml
                   6 fichier(s)        2 056 531 octets
                   3 Rép(s)  174 267 224 064 octets libres

    D:\vb2005\Projects\SMOTest\SMOTest\bin\Debug>cd ..

     

    The code is :

    Imports System.Data

    Imports System.Data.Sql

    Imports System.Text

    Imports Microsoft.SqlServer.Management.Smo

    Imports Microsoft.SqlServer.Management.Smo.Wmi

    Module Module1

    Public Dt As DataTable

    Public str As String

    Public NoRow As Int32

    Sub Main()

    Console.WriteLine("Test for GetDataSource() and SMO/WMI")

    str = "yes"

    While (str.Equals("yes"))

    FillDt()

    Console.WriteLine("Answer : {0}", str)

    End While

    Console.WriteLine("Answer yes to go on")

    str = Console.ReadLine()

    If (Not str.Equals("yes")) Then

    Return

    End If

    End Sub

    Sub FillDt()

    Dim str1 As String

    Dim inst As SqlDataSourceEnumerator = SqlDataSourceEnumerator.Instance

    Console.WriteLine("Wait for execution of GetDataSources()")

    Dt = inst.GetDataSources()

    Console.WriteLine("Visible instances count : {0}", Dt.Rows.Count.ToString())

    Dim num As Int32

    num = 0

    For Each row As DataRow In Dt.Rows

    num = num + 1

    Dim bu As StringBuilder = New StringBuilder()

    bu.Append(" ServerName : ")

    bu.Append(row("ServerName").ToString())

    bu.Append(" InstanceName : ")

    bu.Append(row("InstanceName").ToString())

    bu.Append(" Version : ")

    bu.Append(row("Version").ToString())

    str1 = bu.ToString

    Console.WriteLine("--- Instance {0}", num.ToString())

    Console.WriteLine(str1)

    If (row("ServerName").ToString().Equals(Environment.MachineName)) Then

    NoRow = num - 1

    TestService()

    End If

    Next

    Console.WriteLine(" ")

    Console.WriteLine("Answer yes to redo the GetDataSources()")

    str = Console.ReadLine()

    ' i choose the first instance because i'm working on the same computer

    ' the both computers are belonging to a workgroup and not a domain

    ' so it's impossible to use WMI ( the 2nd computer is a XP Home !!! )

    End Sub

    Sub TestService()

    Dim row As DataRow

    Dim servicename As String

    row = Dt.Rows(NoRow)

    Console.WriteLine("Test service Sql Server on {0} {1}", row("ServerName").ToString(), row("InstanceName").ToString())

    Dim mc As ManagedComputer = New ManagedComputer(row("ServerName").ToString())

    Dim servicecoll As ServiceCollection

    servicecoll = mc.Services

    For Each serv As Service In servicecoll

    Console.WriteLine("Service {0} Type {1} State {2}", serv.Name, serv.Type.ToString(), serv.ServiceState.ToString())

    Next

    servicename = "MSSQL"

    If (String.IsNullOrEmpty(row("InstanceName").ToString())) Then

    servicename = servicename & "SERVER"

    Else

    servicename = servicename & "$" & row("InstanceName").ToString()

    End If

    If (Not servicecoll.Contains(servicename)) Then

    Console.WriteLine("Service {0} : unknown", servicename)

    Return

    End If

    Console.WriteLine("Service {0} exists State : {1}", servicename, servicecoll(servicename).ServiceState.ToString())

    Dim sqlserver As Service = servicecoll(servicename)

    If (sqlserver.ServiceState.Equals(ServiceState.Running)) Then

    Console.WriteLine("Service {0} : {1}", servicename, sqlserver.ServiceState.ToString())

    Return

    End If

    Console.WriteLine("Wait for start of {0}", servicename)

    sqlserver.Start()

    While (Not sqlserver.ServiceState.Equals(ServiceState.Running))

    'Console.WriteLine("{0} : {1}", servicename, sqlserver.ServiceState.ToString())

    sqlserver.Refresh()

    End While

    Console.WriteLine("Service {0} : {1} Well done", servicename, sqlserver.ServiceState.ToString())

    End Sub

    End Module

     

    It' a console project. It is not finished : especially no try/catch and so on but it works.

    The computer CHAMBRE is the local computer XP Pro ). The other computer is a XP Home.

    Some problems with the last one ( only the ServerName is returned and other fields are empty ). so to a retry

     

    With SMO, you can do:

    backups ( even with SQL Server 2005 Express Edition )

    create databases

    explore databases

    Dim serveur1 as Server = new Server("ServerName\InstanceName")

    Server.DataBases is giving a collection of databases on the instance

     

    You can do many things. But with WMI, you must be careful as it's well working on the computer where the Sql Server instance resides. In case it's not the case, you would struggle with security of WMI and it is not a funny job

     

    I hope this will help you.

    I prefer to give you VB 2005 code as i have tested it ( you have results for SQL Server running or not running )

     

    Good luck and have a nice day

     

    Remarks : Too many troubles when i installed the CTP version of VC# 20005

    I had to reinstall Windows. So I have not downloaded VC2008 and SQL Server 2008

     

     

    Friday, December 21, 2007 2:34 PM
  • Hello,

     

    Please have a look about this link

    http://support.microsoft.com/kb/941823/en-us

     

    This morning ( time is 15:52 ) , i found in msdn ManagedComputer was available in Version 2008. But i cant retrieve the link. Whether i succeeds, i will post it

     

    Have a nice day

    Friday, December 21, 2007 2:54 PM
  • I understand using .NET code for most things but I don't think all this code is needed to replace either one line of T-SQL or more than ten lines if you want details.


    http://www.umachandar.com/technical/SQL2000Scripts/Main9.htm

    Saturday, December 22, 2007 3:30 PM
  • Hello,

     

    I agree with you that the code may be long. But if you want to use T-SQL commands in a prpgram ( VB or VC# ) , i think the code will be long.

    You will have to code the parameters of the SELECT Command ( somes lines...)

    I don't think it's possible with T-SQL to start the service SQL Server ( if i'm in error , i will be glad to learn how ).

    For me, i'm programing from 27 years. I begin programing SQL from 1997 ( VC++ 4.0 ) with SQL Server 6.5.

    I think that it's simpler for the user to use a program than to use ISL , or Studio Management SeQL Server.

     

    Personnally, i have to code a program which creates databases when it's necessary and i don't want to oblige an common user to use T-SQL or SMSS ( too difficult and too dangerous ).

     

    My code was only an example and it was the first i have written in VB ( i prefer VC# as i was using C language during 22 years on big systems.

     

    In a program, you have to verify whether the user has rights to create a database or start a service. That's more complicated to do. Execute some T-SQL commands lines is faster but more dangerous for users who are not experts in SQL Server.

     

    I give this code for help especially because it's really difficult to find examples of programming with SMO and WMI.

    I'm using T-SQL to know exactly what i have to do but afterwards, i use these T-SQL commands through VC# for i can create Windows Forms programs more simple to use for a user who is not a specialist of databases.

     

    If my code is not clear, i will be glad to explain it.

    If i have done some errors, i 'm always ready to learn and correct my code

     

    Have a nice day

     

    P.S. : i found in SQL Server 2008 BOL,a page about ManagerComputer . I read that's only working with Framework 2.0 , i am supprised because i thought that Framework 3.0 and 3.5 were mainly extensions of the Framework 2.0.

    I'm waiting the final edition of the BOL

     

    Sorry for my english...I stopped english studies 35 years ago

    Saturday, December 22, 2007 4:59 PM
  • (I don't think it's possible with T-SQL to start the service SQL Server ( if i'm in error , i will be glad to learn how )

     

    The current SQL Server Microsoft says it is not good practice to start the service in a program and in ADO.NET it takes a few lines of code to create a database and all the objects in it using command objects.

     

    (i found in SQL Server 2008 BOL,a page about ManagerComputer . I read that's only working with Framework 2.0 , i am supprised because i thought that Framework 3.0 and 3.5 were mainly extensions of the Framework 2.0.)

     

    You know your DMO(data management object) code is obsolete now SMO(sql server management object) is here for how long?  T-SQL must be be close to ANSI SQL standard so less changes more stable code.

     

    Saturday, December 22, 2007 6:05 PM