Identifying other SQL Server Machines on Network.

  • 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

  • They (I believe) create a DMO object and call ListAvailableServers to get a collection of the servers.

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqldmo/dmoref_m_l_9jfo.asp

    Steve Jones

    sjones@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/sjones

    http://www.dkranch.net

  • You may try OSQL -L.

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

  • 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

  • Thanks for the example!

  • 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


  • 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

  • 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

  • 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 ...

  • Results via UDF

    Select s.ServerName From dbo.fnAvailableServers() s

    Create Function fnAvailableServers()

    Returns @Server table

    (ServerNamevarchar( 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

  • 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 10 (of 10 total)

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