SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Identifying other SQL Server Machines on Network.


Identifying other SQL Server Machines on Network.

Author
Message
Greg Larsen
Greg Larsen
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1661 Visits: 290
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
Steve Jones
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: Administrators
Points: 62364 Visits: 19102
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
My Blog: www.voiceofthedba.com
Allen Cui-55137
Allen Cui-55137
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3537 Visits: 1
You may try OSQL -L.

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



Greg Larsen
Greg Larsen
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1661 Visits: 290
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
5409045121009-7368
SSC Eights!
SSC Eights! (888 reputation)SSC Eights! (888 reputation)SSC Eights! (888 reputation)SSC Eights! (888 reputation)SSC Eights! (888 reputation)SSC Eights! (888 reputation)SSC Eights! (888 reputation)SSC Eights! (888 reputation)

Group: General Forum Members
Points: 888 Visits: 1
Thanks for the example!



Sergei Dumnov
Sergei Dumnov
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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






timeouted
timeouted
SSC-Enthusiastic
SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)

Group: General Forum Members
Points: 120 Visits: 91
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
Greg Larsen
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1661 Visits: 290
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
Curtis Smith
SSC-Addicted
SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)

Group: General Forum Members
Points: 405 Visits: 239
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 ...
willhaney
willhaney
SSC-Enthusiastic
SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)

Group: General Forum Members
Points: 174 Visits: 37
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



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search