July 21, 2003 at 2:07 pm
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
July 21, 2003 at 2:17 pm
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
July 21, 2003 at 2:40 pm
You may try OSQL -L.
You also can use SQL Server 2000 SQL Scan Tool (SQL Scan) to identify all installed instances.
July 21, 2003 at 2:58 pm
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
July 21, 2003 at 3:43 pm
Thanks for the example!
July 22, 2003 at 3:24 am
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
July 22, 2003 at 7:45 am
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
July 22, 2003 at 7:49 am
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
July 22, 2003 at 8:34 am
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
Curtis Smith
SQL Server DBA
Well in worked in Theory ...
July 22, 2003 at 1:09 pm
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
July 22, 2003 at 3:51 pm
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