How to enumerate my Instances?

Hello MVPs,
Is there any way to enumerate my Instances with T-SQL
or ...?
Thanks in Advance.Michas Konstantinos wrote:
> Hello MVPs,
> Is there any way to enumerate my Instances with T-SQL
> or ...?
> Thanks in Advance.
Not a MVP (yet...? :P) but will try to help you.
I am afraid that it is not possible without calling some external COM object
from within T-SQL. See the script by Umachandar Jayachandran:
DECLARE @.ServerName varchar( 128 ), @.SQLServer int, @.RetCode int,
@.Instances int, @.Count int, @.NumInstances int, @.Instance varchar(
128 )
EXEC @.RetCode = sp_OACreate 'SQLDMO.SQLServer2', @.SQLServer OUTPUT
IF @.@.ERROR|@.RetCode <> 0 GOTO Error_Handler
EXEC @.RetCode = sp_OAMethod @.SQLServer, 'ListInstalledInstances', @.Instances
OUTPUT, @.ServerName
IF @.@.ERROR|@.RetCode <> 0 GOTO Error_Handler
EXEC @.RetCode = sp_OAGetProperty @.Instances, 'Count', @.NumInstances OUTPUT
IF @.@.ERROR|@.RetCode <> 0 GOTO Error_Handler
SET @.Count = 1
WHILE ( @.Count <= @.NumInstances )
BEGIN
EXEC @.RetCode = sp_OAGetProperty @.Instances, 'Item', @.Instance OUTPUT,
@.Count
IF @.@.ERROR|@.RetCode <> 0 GOTO Error_Handler
SET @.Count = @.Count + 1
PRINT @.Instance
END
IF @.Instances > 0 EXEC sp_OADestroy @.Instances
IF @.SQLServer > 0 EXEC sp_OADestroy @.SQLServer
RETURN
Error_Handler:
IF @.Instances > 0 EXEC sp_DisplayOAErrorInfo @.Instances, @.RetCode
IF @.SQLServer > 0 EXEC sp_DisplayOAErrorInfo @.SQLServer, @.RetCode
IF @.Instances > 0 EXEC sp_OADestroy @.Instances
IF @.SQLServer > 0 EXEC sp_OADestroy @.SQLServer
You can see intances list on a machine by checking the registry key
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\InstalledInstances.
See "ListSQLSvr.exe - Enumerate SQL Servers" for the non-TSQL approach
http://sqldev.net/misc/ListSQLSvr.htm
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks. Sebastian and Michas. Both of you are quite help. I read the
article Michas sent to me. Now, I understand how the SQL Server should be
configured via a firewall.
Many thanks again, and have a nice day.
Chopin
"Michas Konstantinos" <anonymous@.discussions.microsoft.com> wrote in message
news:2c8101c40069$221787b0$a601280a@.phx.gbl...
> Hello MVPs,
> Is there any way to enumerate my Instances with T-SQL
> or ...?
> Thanks in Advance.
>

How to enumerate my Instances?

Hello MVPs,
Is there any way to enumerate my Instances with T-SQL
or ...?
Thanks in Advance.Michas Konstantinos wrote:
> Hello MVPs,
> Is there any way to enumerate my Instances with T-SQL
> or ...?
> Thanks in Advance.
Not a MVP (yet...? :P) but will try to help you.
I am afraid that it is not possible without calling some external COM object
from within T-SQL. See the script by Umachandar Jayachandran:
DECLARE @.ServerName varchar( 128 ), @.SQLServer int, @.RetCode int,
@.Instances int, @.Count int, @.NumInstances int, @.Instance varchar(
128 )
EXEC @.RetCode = sp_OACreate 'SQLDMO.SQLServer2', @.SQLServer OUTPUT
IF @.@.ERROR|@.RetCode <> 0 GOTO Error_Handler
EXEC @.RetCode = sp_OAMethod @.SQLServer, 'ListInstalledInstances', @.Instances
OUTPUT, @.ServerName
IF @.@.ERROR|@.RetCode <> 0 GOTO Error_Handler
EXEC @.RetCode = sp_OAGetProperty @.Instances, 'Count', @.NumInstances OUTPUT
IF @.@.ERROR|@.RetCode <> 0 GOTO Error_Handler
SET @.Count = 1
WHILE ( @.Count <= @.NumInstances )
BEGIN
EXEC @.RetCode = sp_OAGetProperty @.Instances, 'Item', @.Instance OUTPUT,
@.Count
IF @.@.ERROR|@.RetCode <> 0 GOTO Error_Handler
SET @.Count = @.Count + 1
PRINT @.Instance
END
IF @.Instances > 0 EXEC sp_OADestroy @.Instances
IF @.SQLServer > 0 EXEC sp_OADestroy @.SQLServer
RETURN
Error_Handler:
IF @.Instances > 0 EXEC sp_DisplayOAErrorInfo @.Instances, @.RetCode
IF @.SQLServer > 0 EXEC sp_DisplayOAErrorInfo @.SQLServer, @.RetCode
IF @.Instances > 0 EXEC sp_OADestroy @.Instances
IF @.SQLServer > 0 EXEC sp_OADestroy @.SQLServer
You can see intances list on a machine by checking the registry key
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\InstalledInstances.
See "ListSQLSvr.exe - Enumerate SQL Servers" for the non-TSQL approach
http://sqldev.net/misc/ListSQLSvr.htm
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||By sysservers?
>--Original Message--
>Hello MVPs,
>Is there any way to enumerate my Instances with T-SQL
>or ...?
>Thanks in Advance.
>.
>|||Thanks. Sebastian and Michas. Both of you are quite help. I read the
article Michas sent to me. Now, I understand how the SQL Server should be
configured via a firewall.
Many thanks again, and have a nice day.
Chopin
"Michas Konstantinos" <anonymous@.discussions.microsoft.com> wrote in message
news:2c8101c40069$221787b0$a601280a@.phx.gbl...
> Hello MVPs,
> Is there any way to enumerate my Instances with T-SQL
> or ...?
> Thanks in Advance.
>