How to find ACTIVE Sql Servers in LAN

• pulikondarajesh

  SSC Enthusiast

  Points: 104

  More actions

  October 17, 2003 at 4:21 am

  #162576

  I want to find whether the given (Server name as input )SQL Server is active or not using T-SQL.

• GRN

  Hall of Fame

  Points: 3260

  More actions

  October 18, 2003 at 3:30 am

  #478709

  the two scripts below should give you what you want, the first one uses the SQL dmo object - and the other isql

  EXEC master..xp_cmdshell 'isql -L;'

  DECLARE @AppObj int

  DECLARE @NmList int

  DECLARE @Countint

  DECLARE @Incrint

  DECLARE @server nvarchar(255)

  EXEC sp_OACreate 'SQLDMO.Application', @AppObj OUTPUT

  EXEC sp_OACreate 'SQLDMO.NameList', @NmList OUTPUT

  EXEC sp_OAMethod @AppObj, 'ListAvailableSQLServers', @NmList OUT

  EXEC sp_OAGetProperty @NmList, 'Count', @Count OUT

  SET @Incr = 1

  WHILE @Incr < @Count

  BEGIN

  SET @server = 'Item('+ CONVERT(varchar,@Incr)+')'

  EXEC sp_OAGetProperty @NmList, @server , @server OUT

  PRINT @server

  SET @Incr = @Incr + 1

  END

• GRN

  Hall of Fame

  Points: 3260

  More actions

  October 18, 2003 at 3:31 am

  #478710

  sorry - other way round