none
SQL Server Instance RRS feed

  • Question

  • Hi Experts,

    Could anyone please support with how can I get the list of SQL Server instances running on a server with .net core. I tried

    the below links but its not running with .net core.

    https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlclientfactory.createdatasourceenumerator?view=netframework-4.8
    
    https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/enumerating-instances-of-sql-server

    Thanks

    Priya


    • Edited by Priya Bange Thursday, February 27, 2020 3:20 PM .
    Thursday, February 27, 2020 11:00 AM

Answers

  • Hello,

    In older versions of SQL-Server those methods usually fail and not knowing what version you are targeting this may be the case, other issues may be permissions to see the servers even if they are seen in SSMS.

    You could try using SMO SmoApplication.EnumAvailableSqlServers. Here is a basic example, if it fails an exception may be thrown or the DataTable may be null so use assertion for these cases.

    public async Task<DataTable> GetInstancesAsync()
    {
        var serverInstanceTable = new DataTable();
        await Task.Run(() => { serverInstanceTable = SmoApplication.EnumAvailableSqlServers(true); });
        return serverInstanceTable;
    }

    If SMO is installed it will be under a path like this were the 130 may be different on your box. If not installed install from NuGet.

    C:\Program Files\Microsoft SQL Server\130\SDK\Assemblies\Microsoft.SqlServer.Smo.dll

    This is the using statement

    using Microsoft.SqlServer.Management.Smo;

    Other than that there are solutions on the web for checking names in the registry which can be problematic in regards to a user having rights to read the registry under HKLM.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, February 27, 2020 11:54 AM
    Moderator
  • An async main is only supported starting with C# 7.1. So the first thing you need to ensure is that you are compiling against 7.1+. Note that this has nothing to do with the framework version (which is confusing). The framework version is the runtime. The language version is the version of the language you're compiling against. The current version is 8.0 but that is only supported with .NET Core. .NET Framework apps are limited to 7.3 which was the last version. Given the error it seems like you are using 7.1+

    The second issue is the method signature. async Main must have a Task return type so the task can be returned to the runtime to await it.

    class Program
    {
       static async Task Main ( string[] args )
       {
       ...
       }
    }
    This is consistent with the general behavior that async methods should return Task.


    Michael Taylor http://www.michaeltaylorp3.net

    • Marked as answer by Priya Bange Sunday, March 1, 2020 6:02 AM
    Thursday, February 27, 2020 3:50 PM
    Moderator

All replies

  • Hello,

    In older versions of SQL-Server those methods usually fail and not knowing what version you are targeting this may be the case, other issues may be permissions to see the servers even if they are seen in SSMS.

    You could try using SMO SmoApplication.EnumAvailableSqlServers. Here is a basic example, if it fails an exception may be thrown or the DataTable may be null so use assertion for these cases.

    public async Task<DataTable> GetInstancesAsync()
    {
        var serverInstanceTable = new DataTable();
        await Task.Run(() => { serverInstanceTable = SmoApplication.EnumAvailableSqlServers(true); });
        return serverInstanceTable;
    }

    If SMO is installed it will be under a path like this were the 130 may be different on your box. If not installed install from NuGet.

    C:\Program Files\Microsoft SQL Server\130\SDK\Assemblies\Microsoft.SqlServer.Smo.dll

    This is the using statement

    using Microsoft.SqlServer.Management.Smo;

    Other than that there are solutions on the web for checking names in the registry which can be problematic in regards to a user having rights to read the registry under HKLM.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, February 27, 2020 11:54 AM
    Moderator
  • Install the version from NuGet. It supports .NET Core 2.0+.  It is not listed as supporting .NET Standard (which is weird) so you might have to adjust the target framework if you're trying to use this in a .NET Standard library.

    Michael Taylor http://www.michaeltaylorp3.net

    Thursday, February 27, 2020 2:52 PM
    Moderator
  • Install the version from NuGet. It supports .NET Core 2.0+.  It is not listed as supporting .NET Standard (which is weird) so you might have to adjust the target framework if you're trying to use this in a .NET Standard library.

    Michael Taylor http://www.michaeltaylorp3.net

    Thank you all for the timely support as a newbie am getting static Main method not suitable for entry point. Please suggest

        class Program
        {
            static async void Main(string[] args)
            {
                DataTable table = await GetInstancesAsync();
    
            }
    
            public static async Task<DataTable> GetInstancesAsync()
            {
                var serverInstanceTable = new DataTable();
                await Task.Run(() => { serverInstanceTable = SmoApplication.EnumAvailableSqlServers(true); });
                return serverInstanceTable;
            }
    
            
    
        }
    

    Thursday, February 27, 2020 3:19 PM
  • An async main is only supported starting with C# 7.1. So the first thing you need to ensure is that you are compiling against 7.1+. Note that this has nothing to do with the framework version (which is confusing). The framework version is the runtime. The language version is the version of the language you're compiling against. The current version is 8.0 but that is only supported with .NET Core. .NET Framework apps are limited to 7.3 which was the last version. Given the error it seems like you are using 7.1+

    The second issue is the method signature. async Main must have a Task return type so the task can be returned to the runtime to await it.

    class Program
    {
       static async Task Main ( string[] args )
       {
       ...
       }
    }
    This is consistent with the general behavior that async methods should return Task.


    Michael Taylor http://www.michaeltaylorp3.net

    • Marked as answer by Priya Bange Sunday, March 1, 2020 6:02 AM
    Thursday, February 27, 2020 3:50 PM
    Moderator