Enumerate SQL Servers using SQLDMO and T-SQL
Microsoft reported BUG :
ListAvailableServers Method of the SQLDMO.Application Object Causes Error 0x800A000E .
When you execute the ListAvailableServers method of the SQLDMO.Application object from an ASP page, the following error message may occur:
Microsoft SQL-DMO (0x800A000E)
[SQL-DMO]Not enough storage is available to complete this operation.
However, there is some solution to get SQL servers using Client Side script on ASP .
Here is Stored Procedure , which will prepare List of available SQL servers , so you can easy show them in your Web Applications.
CREATE PROCEDURE EnumerateSQLServers
AS
/*
Stored Procedure : EnumerateSQLServers
Author : Srdjan Josipovic
Date : June 19 2002
Purpose : Enumerate Available SQL Servers using SQLDMO and T-SQL
*/
DECLARE @retval int
DECLARE @result varchar(500)
DECLARE @object int
DECLARE @objectList int
DECLARE @src varchar(254)
DECLARE @desc varchar(255)
DECLARE @resultsCount int
DECLARE @counter int
DECLARE @method varchar(255)
--create SQLDMO object
EXEC @retval = sp_OACreate 'SQLDMO.Application', @object OUT
-- check if object was created successfully
IF @retval <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@retval), Source=@src, Description=@desc
RETURN
END
--call method ListAvailableServers() , get Object_ID for SQLDMO.NameList
EXEC @retval = sp_OAMethod @object , 'ListAvailableSQlServers()' , @objectList OUT
-- error ?
IF @retval <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objectList, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@retval), Source=@src, Description=@desc
RETURN
END
-- Count Servers in the neighborhood
EXEC @retval = sp_OAGetProperty @objectList , 'Count' , @resultsCount OUT
-- error handler again
IF @retval <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objectList, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@retval), Source=@src, Description=@desc
RETURN
END
-- If there are Servers , step into .....
IF @resultsCount > 0
BEGIN
SET @counter = 1
DECLARE @ServersTbl table (ServerID int IDENTITY ,ServerName varchar(255))
WHILE @counter <= @resultsCount
BEGIN
-- List SQL Server : Name by Name
SET @method = 'Item(' + convert(varchar(3),@counter) + ')'
EXEC @retval = sp_OAGetProperty @objectList ,@method , @result OUT
-- Store data in the temp table
INSERT INTO @ServersTbl (ServerName) SELECT @result
-- move to next record
SET @counter = @counter + 1
END
END
ELSE
BEGIN
SET @result = 'No Servers around you'
INSERT INTO @ServersTbl (ServerName) SELECT @result
END
-- kill object
EXEC @retval = sp_OADestroy @object
IF @retval <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@retval), Source=@src, Description=@desc
RETURN
END
-- OK , List is inside ....
SELECT * FROM @ServersTbl
GO 
