none
Can't list SQL Servers RRS feed

  • Question

  • Hi Everyone:

    Please forgive me, but I am not sure to which forum this question belongs.

    I have a VB 2013 application that scans my home network looking for instances of MS SQL Server.

    Currently, I have MS SQL Server 2017 installed on PC named MRM-WINX-002. Using 'Ping' I made sure I could ping MRM-WINX-001 from MRM-WINX-002 and vis-versa. MRM-WINX-002 uses McAfee as a firewall, but it says my home network is protected.

    I can access SQL Server using SSMS from MRM-WINX-001. However, when I try to list the SQL Servers using my application I get a "connection failed" error. This doesn't make any since, because I can ping both systems. 

    The code to locate SQL Servers uses Imports System.Data.Sql and System.Data.SqlClient. The code is:

    Public Function lst_SQLServers() As List(Of String)
         'Purpose:       List all instances of MS-SQL Servies on the
         '               the Network
         'Parameters:    None
         'Returns:       A List of SQL Server instances.
         '               declare variables
         Dim dt As Data.DataTable = Nothing
         Dim dr As Data.DataRow = Nothing
         Dim listSQL As List(Of String) = New List(Of String)
    
         Try
             'get sql server instances in to DataTable object
             dt = Sql.SqlDataSourceEnumerator.Instance.GetDataSources()
             For I = 0 To dt.Rows.Count - 1 'Step I + 1
                 listSQL.Add(dt.Rows(I)(0).ToString)
                 'Debug.Print(dt.Rows(I)(0).ToString)
             Next
    
         Catch ex As System.Data.SqlClient.SqlException
             'Log error 
             Dim el As New Log.ErrorLogger
             el.WriteToErrorLog(ex.Message, _
                                ex.StackTrace, _
                                "Error - lst_SQLServers")
    
         Catch ex As Exception
             'Log error 
             Dim el As New Log.ErrorLogger
             el.WriteToErrorLog(ex.Message, _
                                ex.StackTrace, _
                                "Error - lst_SQLServers")
         Finally
             'clean up ;)
             dr = Nothing
             dt = Nothing
         End Try
         Return listSQL
    End Function

    This worked in the past. Can someone tell me why all of the sudden it doesn't?

    Thanks,


    MRM256

    Friday, January 4, 2019 6:09 PM

All replies

  • Hello,

    Perhaps try using SMO, using SmoApplication.EnumAvailableSqlServers. You can install SMO inside Visual Studio using NuGet package console via this link or see if SMO is installed already and add references to your project.

    If on your machine it will be under where dependent on the version installed 130 may change..

    C:\Program Files\Microsoft SQL Server\130\SDK\Assemblies\

    Look for Microsoft.SqlServer.Smo.dll

    If this fails then there is something to do with permissions which SSMS has proper permissions to do the same task.


    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, January 4, 2019 6:27 PM
    Moderator
  • Karen,

    How do I install NuGet 2.12 so I can update my VS 2013 to use the Microsoft.SqlServer.Smo.dll?

    One other stupid question: Why isn't this included in a regular software update?

    Thanks,


    MRM256

    Friday, January 4, 2019 7:48 PM
  • Karen,

    How do I install NuGet 2.12 so I can update my VS 2013 to use the Microsoft.SqlServer.Smo.dll?

    One other stupid question: Why isn't this included in a regular software update?

    Thanks,


    MRM256

    Sorry I don't know (have not using anything lower than 2017 in several years) but you might try the following and note I see there are a handful of unhappy developers.

    https://marketplace.visualstudio.com/items?itemName=NuGetTeam.NuGetPackageManagerforVisualStudio2013


    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, January 4, 2019 8:41 PM
    Moderator
  • Karen,

    I managed to update VS 2013, but now I am getting this error: Error 1 An error occurred while signing: Failed to sign bin\Debug\app.publish\UniDataEntry_2013.exe.
    SignTool Error: No certificates were found that met all the given criteria. UniDataEntry_2013.

    I went to the signing area of the project.

    This is what it was when I installed VS 2013 when the PC was a Windows 8 System. It has since been upgraded to a Windows 10 system, but for some reason the certificate information was not.

    How do we fix this?

    Thanks,


    MRM256

    Friday, January 4, 2019 9:51 PM
  • SMO does not need signing, did you sign the project? 

    NOTE: I'm in and out of my building since two hours ago and still moving around so I may not reply back right away, sorry.


    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, January 4, 2019 10:54 PM
    Moderator
  • Thinking about the certificate issue, not sure what is happening here especially if this only happened after installing the NuGet package unless you are signing your project and this is then caused by SMO DLL's not being signed.

    Going back to the original issue with or without SMO, perhaps the code which use to work now does not, has anything changed in your environment e.g. an update to SQL-Server, using a newer version of the .NET Framework etc.

    Lastly, has anything changed to permissions on one or more of the servers or user name/passwords changed?


    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, January 5, 2019 11:13 AM
    Moderator
  • Hi Karen,

    Because of the signing issue I cannot test my program using the IDE. In other words, it will not compile for testing through the IDE.

    I took an .exe of the previously working application and copied it onto a flash drive. Plugged that into the PC hosting SQL Server and tried to see if it will see SQL Server while running on the SQL Server PC. Same error.

    No, nothing on the server side has changed. The systems update automatically. If updates start causing applications that previously worked to fail. Then we need to go back to a complier and stop using the framework.

    About the only solution I can think of is a Scorched Earth tactic. Remove Visual Studio and reinstall. However, I will have to perform the update again so I can bring my application back to operational status.

    Thanks,


    MRM256

    Saturday, January 5, 2019 3:23 PM
  • Have you considered using Visual Studio 2017. The community edition is free, see the license. Going this route, you can still target the same .NET Framework as currently using or use a newer version of the Framework.

    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, January 5, 2019 3:31 PM
    Moderator
  • I don't think the community edition has the same capabilities as the Professional. 

    Besides, it really isn't cost effective if you have to spend $500+ every year or so just, to make sure your applications continue to work.


    MRM256

    Saturday, January 5, 2019 3:41 PM
  • Karen,

    I install VS 2017 community edition, and I still have the same problem. When I try to run the VS 2013 version of the project I still get the signing error. Furthermore, VS 2017 won't update the project to 2017.

    Why?


    MRM256

    Saturday, January 5, 2019 8:46 PM
  • Here are a few facts about VS2013 project -> VS2015 and VS2017.

    There are small differences between 2013 and higher while 2015 on up are pretty much the same, interchangeable (but not so much with VS2019). No matter when open a VS2013 project in 2017 if you look at the solution folder in Window explorer there will be a folder named .vs which means essentially your solution is now VS2017 ready. Same goes for projects unless I'm messing something as I have a boat load of VS2013 solutions I open and save in VS2017 no problem other than needing to go into project properties and update the Framework version as VS2017 will not touch this to stay compatible with where the project was created in, in this case VS2013.

    Going back to the original issue with signing, make a copy of the Visual Studio solution in another folder. Open it, remove the signing and try again.

    When our organization signs projects it's using Wix toolset with this a installer and signed cert from GoDaddy. Not that it helps you but wanted to mention it because this method properly signs other libraries. Downside is the cert is not free.

     

     


    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, January 5, 2019 10:30 PM
    Moderator