#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2009
    Posts
    67
    Rep Power
    6

    List of SQL Servers & Databases


    Hi, I am using Visual Studio 2012 Ultimate and I am trying to display a combo box of SQL Servers and then depending on what server is selected, a list of databases on that server.
    I am new to VB and would like some suggestions on ideas.
    I have searched the internet but can only find connection strings.
    I want the user to be able to select their Server and Database.
    Thanks
  2. #2
  3. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,179
    Rep Power
    9398
    You can't just get a list of servers out of nothing. There has to be a list somewhere (in your program or not) or something you know ahead of time that will let you find them.

    Once you have those servers you then have to know login credentials so that you can connect to it and get a list of databases. Assuming they're not all open to anonymous access. Again, you can't get that from nothing: has to be a list somewhere, or maybe a predefined username/password that you know always works, or something else.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2009
    Posts
    67
    Rep Power
    6
    Thanks requinix,
    Forgive my ignorance but is it not possible to search the local network and find SQL Servers?
  6. #4
  7. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,179
    Rep Power
    9398
    Technically yes, but it's not something that should be done. Like trying to find your car in a huge parking lot by checking every single parking spot.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2009
    Posts
    67
    Rep Power
    6
    Thanks for that, I can understand why that would be a problem.
    So if I want the user to type in the server name and then display a list of databases from that server would that be a suitable way to do it?

    I basically want an OPTIONS section of my app where the user can choose the server and database to connect to.
  10. #6
  11. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,179
    Rep Power
    9398
    You can prompt for a server name, yes, but you'd probably also need credentials to connect (eg, username and password) to be able to query for the available databases. They're not just to connect too: generally databases are also restricted by user, so one user may see one set of databases while another user may see another set.
  12. #7
  13. No Profile Picture
    Grumpier old Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2003
    Posts
    14,445
    Rep Power
    4539
    From a command prompt you can simply use osql -L to get a list of available mssql servers.

    Comments on this post

    • requinix agrees : figures that sql server broadcasts itself...
    ======
    Doug G
    ======
    Bartender to Rene Descartes "have another beer?" Descartes: "I think not" and he vanished.
    --Alfred Bester
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2009
    Posts
    67
    Rep Power
    6
    I appreciate the feedback and suggestions.
    However, If someone was writing an application and they wanted the user to be able to choose a server and databases and then use windows authentication to log into that server. How would it be done. I am after something like the ADD CONNECTION in Data Connections in Visual Studio but I want to incorporate it into my application as a user configurable settings. From there, I will create the connection string. Thanks
  16. #9
  17. No Profile Picture
    Grumpier old Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2003
    Posts
    14,445
    Rep Power
    4539
    Like requinix said, your user will have to get logged in the selected sql server to view available databases, or you'd need to use code with your own login, get the list of databases then build that list into your selection list for the user. But this approach won't guarantee the desired user can access the database selected, though.

    If your sql server is configured for windows authentication or mixed-mode authentication you could add all the possible windows users as sql server users to all the potential servers, then they'd be logged into the sql server when they use your code to access it.

    Otherwise you'd set up a sql user that your code can use to gain entrance to the db server.
    ======
    Doug G
    ======
    Bartender to Rene Descartes "have another beer?" Descartes: "I think not" and he vanished.
    --Alfred Bester

IMN logo majestic logo threadwatch logo seochat tools logo