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