Identifying other SQL Server Machines on Network.

  • Greg Larsen

    SSC-Insane

    Points: 20635

    Some of the wizards, like DTS Input/Export Wizard, are able to detect SQL Server machines and display them in the server pull down. Is there a command I can use to identify all SQL Server machines on our network, much like the like wizards do? I would like to produce report of all the SQL Server machines in our environment.

    Gregory Larsen, DBA

    If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

    Gregory A. Larsen, MVP

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 717351

  • Allen Cui-55137

    SSC Guru

    Points: 51653

    You may try OSQL -L.

    You also can use SQL Server 2000 SQL Scan Tool (SQL Scan) to identify all installed instances.

  • Greg Larsen

    SSC-Insane

    Points: 20635

    Thanks for the suggestions, I ended up using DMO, via TSQL. Here is my hack, if you are interested.

    Declare @RC int

    DECLARE @object int

    declare @Output varchar(255)

    Declare @Method varchar(255)

    DECLARE @src varchar(255)

    Declare @desc varchar(255)

    -- Create hash for entered password

    EXEC @RC = sp_OACreate 'sqldmo.application', @object OUT

    IF @rc <> 0

    BEGIN

    EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT

    SELECT hr=convert(varbinary(4),@RC), Source=@src, Description=@desc

    RETURN

    END

    set @method = 'ListAvailableSQLServers.count'

    declare @cnt int

    EXEC @RC=sp_OAMethod @Object, @method, @cnt out

    IF @RC <> 0

    BEGIN

    EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT

    SELECT hr=convert(varbinary(4),@RC), Source=@src, Description=@desc

    RETURN

    END

    declare @i int

    set @i = 0

    declare @name varchar(255)

    while @i < @cnt

    begin

    set @i = @i + 1

    set @method = 'ListAvailableSQLServers.item(' + rtrim(cast(@i as char)) + ')'

    EXEC @RC=sp_OAMethod @Object, @method, @name out

    IF @RC <> 0

    BEGIN

    EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT

    SELECT hr=convert(varbinary(4),@RC), Source=@src, Description=@desc

    RETURN

    END

    print @name

    end

    EXEC @rc = sp_OADestroy @object

    IF @rc <> 0

    BEGIN

    EXEC sp_OAGetErrorInfo @object

    RETURN

    END

    Gregory Larsen, DBA

    If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

    Gregory A. Larsen, MVP

  • 5409045121009-7368

    SSCoach

    Points: 15740

    Thanks for the example!

  • Sergei Dumnov

    Grasshopper

    Points: 10

    quote:


    Thanks for the suggestions, I ended up using DMO, via TSQL. Here is my hack, if you are interested.

    Declare @RC int

    DECLARE @object int

    declare @Output varchar(255)

    Declare @Method varchar(255)

    DECLARE @src varchar(255)

    Declare @desc varchar(255)

    -- Create hash for entered password

    EXEC @RC = sp_OACreate 'sqldmo.application', @object OUT

    IF @rc <> 0

    BEGIN

    EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT

    SELECT hr=convert(varbinary(4),@RC), Source=@src, Description=@desc

    RETURN

    END

    set @method = 'ListAvailableSQLServers.count'

    declare @cnt int

    EXEC @RC=sp_OAMethod @Object, @method, @cnt out

    IF @RC <> 0

    BEGIN

    EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT

    SELECT hr=convert(varbinary(4),@RC), Source=@src, Description=@desc

    RETURN

    END

    declare @i int

    set @i = 0

    declare @name varchar(255)

    while @i < @cnt

    begin

    set @i = @i + 1

    set @method = 'ListAvailableSQLServers.item(' + rtrim(cast(@i as char)) + ')'

    EXEC @RC=sp_OAMethod @Object, @method, @name out

    IF @RC <> 0

    BEGIN

    EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT

    SELECT hr=convert(varbinary(4),@RC), Source=@src, Description=@desc

    RETURN

    END

    print @name

    end

    EXEC @rc = sp_OADestroy @object

    IF @rc <> 0

    BEGIN

    EXEC sp_OAGetErrorInfo @object

    RETURN

    END

    Gregory Larsen, DBA

    If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples


  • timeouted

    SSC Enthusiast

    Points: 150

    Greg,

    There are two points you must pay attention using ListAvailableServers.

    The fist one is relative with clustering. This method returns the real name of a clustered machine and not the virtual name.

    The second point is relative to your network topology. If you have a firewall filtering network packets between the server you are using to run your script and other servers, its possible that you wont see some servers. This is because ListAvailableServers uses UDP broadcasts to do its task and most firewalls are factory-configured to not permit it.

    Its important to you to determine your network segmentation and run your script in some servers among each segment and cross the data to achieve better results.

    []'s

    João Bosel Polisél

  • Greg Larsen

    SSC-Insane

    Points: 20635

    Very good information, and advice.

    Gregory Larsen, DBA

    If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

    Gregory A. Larsen, MVP

  • Curtis Smith

    Hall of Fame

    Points: 3063

    good tips. what about personal edition. does anyone have a method for finding them on the network. some of the MS wizards find them.

    Curtis Smith

    Application Dev. Manager

    http://www.PSAKIDS.com




    Curtis Smith
    SQL Server DBA
    Well in worked in Theory ...

  • willhaney

    Mr or Mrs. 500

    Points: 596

    Results via UDF

    Select s.ServerName From dbo.fnAvailableServers() s

    Create Function fnAvailableServers()

    Returns @Server table

    (ServerName varchar ( 255 ))

    as

    Begin

    Declare

    @rc int

    , @object int

    , @Method varchar(255)

    , @cnt int

    , @i int

    , @name varchar(255)

    -- initialize variables

    set @i = 0

    -- instantiate object

    Exec @rc = sp_OACreate 'sqldmo.application', @object OUT

    If @rc <> 0 Return

    -- get count of servers

    Exec @rc=sp_OAMethod @Object, 'ListAvailableSQLServers.count', @cnt out

    If @rc <> 0 Return

    -- loop through each server

    while @i < @cnt

    begin

    set @i = @i + 1

    set @method = 'ListAvailableSQLServers.item(' + cast(@i as varchar) + ')'

    -- get server name

    Exec @rc=sp_OAMethod @Object, @method, @name out

    If @rc <> 0 Return

    -- insert into table

    Insert @Server (ServerName) values (@Name)

    End

    -- destroy objects

    Exec @rc = sp_OADestroy @object

    Return

    End

    GO

  • Mark Twain

    Newbie

    Points: 7

    You also can use Win32 API NetServerEnum from netapi32.dll library to do this programmatically; will not work on Win95/98.

    Callable both from C and VB. SQL Server is identified by the SV_TYPE_SQLSERVER passed as servertype parameter.

    I am also aware of small programs that encapsulates this functionality

    ListSQLSvr.exe and SQLPING.EXE

    MSFT also suggets use of OLEDB provider for SQL Server calling ISourcesRowset::GetSourcesRowset to enumerate all visible sources. Detailed example (C++) in BOL. Also relies on the NetServerEnum API behind the scenes.

    SQL-DMO (explained in posts above)

    isql -L command line utility (also mentioned in posts)

Viewing 11 posts - 1 through 11 (of 11 total)

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