Registered local Servers

Question

Post reply

Registered local Servers

JKSQL

SSCrazy Eights

Points: 8481
More actions
July 2, 2010 at 10:54 am

#234637

We are trying to validate server names.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

here's one way via TSQL, but it requires osql and xp_cmdshell:

CREATE PROCEDURE dbo.ListLocalServers

AS

BEGIN

SET NOCOUNT ON

CREATE TABLE #servers(sname VARCHAR(255))

INSERT #servers EXEC master..XP_CMDShell 'OSQL -L'

-- play with ISQL -L too, results differ slightly

DELETE #servers WHERE sname='Servers:'

SELECT LTRIM(sname) FROM #servers WHERE sname != 'NULL'

DROP TABLE #servers

END

EXEC ListLocalServers

results:

(local)

ASTRAH

AURORA

BELLE

etc....

and here's how to do it in .NET: i'm returning a DataTable with this function with all the servers and their attributes:

Imports Microsoft.SqlServer.Management

Imports Microsoft.SqlServer.Management.Smo

Module MyGlobalModule

Friend Function GetServers() As DataTable

Dim serverTable As New DataTable

Dim i As Integer

Dim c As Integer

serverTable = Smo.SmoApplication.EnumAvailableSqlServers(False)

serverTable.TableName = "serverTable"

'this sorting is not working, doing it the hard way by copying.

'serverTable.DefaultView.Sort = "[Server] asc"

Dim finalTable As New DataTable

finalTable = serverTable.Copy

finalTable.Clear()

Dim a() As DataRow = serverTable.Select("", "[Server] asc")

For Each dr As DataRow In a

Dim newdr As DataRow = finalTable.NewRow

newdr.BeginEdit()

newdr!Name = dr!Name

newdr!Server = dr!Server

newdr!Instance = dr!Instance

newdr!IsClustered = dr!IsClustered

newdr!Version = dr!Version

newdr!IsLocal = dr!IsLocal

newdr.EndEdit()

finalTable.Rows.Add(newdr)

Next

'Name, Server, Instance, IsClustered, Version, IsLocal

For c = 0 To finalTable.Columns.Count - 1

Console.Write(finalTable.Columns(c).ColumnName & ", ")

Next

Console.WriteLine("")

For i = 0 To finalTable.Rows.Count - 1

For c = 0 To finalTable.Columns.Count - 1

Console.Write(finalTable.Rows(i).Item(c).ToString & ", ")

Next c

Console.WriteLine("")

Next i

ServerListisLoaded = True

Return finalTable

End Function

End Module

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

JKSQL SSCrazy Eights Points: 8481 More actions · Answer 2

The only problem is -L is local and broadcast servers. I want those broadcast servers filtered out.

-L

Lists the locally configured servers and the names of the servers broadcasting on the network.

It is close though. I looked but did not see another switch in the command. The only thing I have found so far is to actually look in the registry

Lowell SSC Guru Points: 323518 More actions · Answer 3

the SMO method is more robust; there is a IsLocal

field in the set returning 0 or 1 for true/false if it is local;

you could create a CLR to return that info, or a separate application of course...that's all i could think of.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!