Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Identifying other SQL Server Machines on Network. Expand / Collapse
Author
Message
Posted Monday, July 21, 2003 2:07 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, June 6, 2014 2:06 PM
Points: 1,040, Visits: 277
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

Need SQL Server Examples check out my website at http://www.sqlserverexamples.com
Post #14387
Posted Monday, July 21, 2003 2:17 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:15 PM
Points: 31,210, Visits: 15,654
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
www.dkranch.net







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #71448
Posted Monday, July 21, 2003 2:40 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 2, 2005 11:49 AM
Points: 2,611, Visits: 1
You may try OSQL -L.

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




Post #71449
Posted Monday, July 21, 2003 2:58 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, June 6, 2014 2:06 PM
Points: 1,040, Visits: 277
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

Need SQL Server Examples check out my website at http://www.sqlserverexamples.com
Post #71450
Posted Monday, July 21, 2003 3:43 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, May 30, 2007 4:59 PM
Points: 672, Visits: 1
Thanks for the example!


Post #71451
Posted Tuesday, July 22, 2003 3:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 26, 2009 5:36 AM
Points: 4, Visits: 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







Post #71452
Posted Tuesday, July 22, 2003 7:45 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 10, 2014 10:54 AM
Points: 120, Visits: 72
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



Post #71453
Posted Tuesday, July 22, 2003 7:49 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, June 6, 2014 2:06 PM
Points: 1,040, Visits: 277
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

Need SQL Server Examples check out my website at http://www.sqlserverexamples.com
Post #71454
Posted Tuesday, July 22, 2003 8:34 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, February 20, 2014 10:05 AM
Points: 287, Visits: 208
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
www.PSAKIDS.com





Curtis Smith
SQL Server DBA
Well in worked in Theory ...
Post #71455
Posted Tuesday, July 22, 2003 1:09 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 24, 2014 11:03 PM
Points: 161, Visits: 35
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





Post #71456
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse