none
Server tree RRS feed

  • Question

  • I am creating a SQL Server app. I want the user to create their server structure when the app first runs. So I want them to create a list of production servers, staging servers and test servers. What is the best way to incorporate this?

    Alan

    Friday, March 30, 2018 2:26 AM

Answers

  • Hello,

    To find SQL Servers there is SmoApplication.EnumAvailableSqlServers.

    The following is taken from a C# code sample I wrote and translated it to VB.NET (it's missing parts so it can not be used as is).

    Public Function AvailableServers() As DataTable
    	Return SmoApplication.EnumAvailableSqlServers(True)
    End Function
    Public Function LocalServers() As List(Of LocalServer)
    	Return SmoApplication.EnumAvailableSqlServers(True).AsEnumerable().Select(Function(row) New LocalServer() With {.Name = row.Field(Of String)("Name"), .Instance = row.Field(Of String)("Instance"), .ServerName = row.Field(Of String)("Server")}).ToList()
    End Function

    .

    Namespace SMO_Library
       Public Class LocalServer
    	Public Property ServerName() As String
    	Public Property Name() As String
    	Public Property Instance() As String
       End Class
    End Namespace

    You can also use SMO to get databases on a server, tables in a database and also all properties for a table including primary keys and foreign keys etc.

    There are screenshots for my code sample here.

    Required references are here (you path may be different depending on the version of SQL-Server installed)


    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

    • Marked as answer by anaylor01 Sunday, April 1, 2018 10:09 PM
    Friday, March 30, 2018 1:44 PM
    Moderator

All replies

  • Please explain more about what you are trying to do.  There isn't enough information here to understand your objective.

    1) What do you call a "SQL Server app"? A database application that uses SQL as the database or something else?

    2) What code object represents the "servers"?  Are these a list of string server names? An instance of a class you designed or referenced from somewhere?

    3) Incorporate it into what?  The GUI?  The business logic?  The backend data?  All three?

    4) What is the overall purpose of the program and how do you envision users working with it?


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Friday, March 30, 2018 12:54 PM
    Moderator
  • Hello,

    To find SQL Servers there is SmoApplication.EnumAvailableSqlServers.

    The following is taken from a C# code sample I wrote and translated it to VB.NET (it's missing parts so it can not be used as is).

    Public Function AvailableServers() As DataTable
    	Return SmoApplication.EnumAvailableSqlServers(True)
    End Function
    Public Function LocalServers() As List(Of LocalServer)
    	Return SmoApplication.EnumAvailableSqlServers(True).AsEnumerable().Select(Function(row) New LocalServer() With {.Name = row.Field(Of String)("Name"), .Instance = row.Field(Of String)("Instance"), .ServerName = row.Field(Of String)("Server")}).ToList()
    End Function

    .

    Namespace SMO_Library
       Public Class LocalServer
    	Public Property ServerName() As String
    	Public Property Name() As String
    	Public Property Instance() As String
       End Class
    End Namespace

    You can also use SMO to get databases on a server, tables in a database and also all properties for a table including primary keys and foreign keys etc.

    There are screenshots for my code sample here.

    Required references are here (you path may be different depending on the version of SQL-Server installed)


    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

    • Marked as answer by anaylor01 Sunday, April 1, 2018 10:09 PM
    Friday, March 30, 2018 1:44 PM
    Moderator
  • Hi Karen, what I am trying to do is create something like Registered servers in SSMS. It's so they can link there servers to groups like Production, staging, and test.  Like below.


    Alan

    Sunday, April 1, 2018 10:12 PM

  • Alan

    Sunday, April 1, 2018 10:16 PM
  • Hi Alan,

    Never needed to do this and even if I were to try my current environment (at home) has but one server and at my current position (loaned out from my company to another company) they only have Oracle so I can't try at work. Now if I were not being loaned out I could try it as that position has over 100 SQL-Servers (most virtual).

    What I would suggest is looking at the LinkedServer class for SMO. A tad bit more (not much more) on LinkedServer.

    A small starter

    'Connect to the local, default instance of SQL Server.
    Dim srv As Server
    srv = New Server
    'Create a linked server.
    Dim lsrv As LinkedServer
    lsrv = New LinkedServer(srv, "OLEDBSRV")
    'When the product name is SQL Server the remaining properties are not required to be set.
    lsrv.ProductName = "SQL Server"
    lsrv.Create()
    


    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

    Sunday, April 1, 2018 11:42 PM
    Moderator