--**************************************
-- Name: Enumerate SQL Servers using SQLDMO and T-SQL
-- Description: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 .<BR><BR>Here is Stored Procedure , which will prepare List of available SQL servers , so you can easy show them in your Web Applications.
-- By: Srdjan Josipovic
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=491&lngWId=5--for details.--**************************************
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
|
