Technical Article

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

Rate

Share

Share

Rate