Named Instances

• Ron Berry-205588

  SSC Rookie

  Points: 34

  More actions

  February 22, 2005 at 4:43 pm

  #86915

  Does anyone know how to list all the named instances on a given server?

• Helen-84411

  Hall of Fame

  Points: 3320

  More actions

  February 22, 2005 at 8:20 pm

  #543599

  Hai,

  Did you try quering the sysprocesses table or looking into registry for all instrance. There is a system stored proc master..xp_regread  to read

  

  
  Helen
  --------------------------------
  Are you a born again. He is Jehova Jirah unto me

• babuganesh2000

  Hall of Fame

  Points: 3123

  More actions

  February 22, 2005 at 10:08 pm

  #543605

  Script from Umachandar Jayachandran Site...

  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