As usual, great article James, Good Job!

I finished reading it and it let me so intrigued that I decided to give it an additional twist to the code and try to port it to a stored procedure (using sp_OA.. procedures to instantiate the DMO COM object model). This is the result

CREATE PROC sp_CheckGroups AS

BEGIN

DECLARE @intReturnCode   int,

  @intDMO     int,

  @intServerGroups  int,

  @intGroupTotal   int,

  @intGroupCount   int,

  @intServerGroup   int,

  @intRegisteredServers int,

  @intServerTotal   int,

  @intServerCount   int,

  @intRegisteredServer  int,

  @strTmpChar    varchar(100),

  @strServerGroupName  varchar(100),

  @strServerName   varchar(100),

  @strServerLogin   varchar(100),

  @strServerPassword  varchar(100),

  @strDoing   varchar(100),

  @strSource   varchar(100),

  @strDescription  varchar(100)

 SET @strDoing = 'Creating the DMO object'

 EXEC @intReturnCode = master.dbo.sp_OACreate 'SQLDMO.Application', @intDMO OUTPUT

 IF @intReturnCode <> 0

  GOTO Error

 SET @strDoing = 'Creating the ServerGroups object'

 EXEC @intReturnCode = master.dbo.sp_OAGetProperty @intDMO, 'ServerGroups', @intServerGroups OUTPUT

 IF @intReturnCode <> 0

  GOTO Error

 SET @strDoing = 'Counting Registered Groups'

 EXEC @intReturnCode = sp_OAGetProperty @intServerGroups, 'Count', @intGroupTotal OUTPUT

 IF @intReturnCode <> 0

  GOTO Error

 SET @intGroupCount = 1

 WHILE @intGroupCount <= @intGroupTotal

 BEGIN

  SET @strDoing   = 'Creating the Server Group'

  SET @strTmpChar = 'Item(' + CAST(@intGroupCount AS VARCHAR) + ')'

  EXEC @intReturnCode = sp_OAMethod @intServerGroups, @strTmpChar, @intServerGroup OUTPUT

  IF @intReturnCode <> 0

   GOTO Error

  SET @strDoing = 'Creating the RegisteredServers object'

  EXEC @intReturnCode = master.dbo.sp_OAGetProperty @intServerGroup, 'RegisteredServers', @intRegisteredServers OUTPUT

  IF @intReturnCode <> 0

   GOTO Error

  SET @strDoing = 'Counting Registered Servers'

  EXEC @intReturnCode = sp_OAGetProperty @intRegisteredServers, 'Count', @intServerTotal OUTPUT

  IF @intReturnCode <> 0

   GOTO Error

  SET @intServerCount = 1

  WHILE @intServerCount <= @intServerTotal

  BEGIN

   SET @strDoing    = 'Creating the Registered Server'

   SET @strTmpChar  = 'Item(' + CAST(@intServerCount AS VARCHAR) + ')'

   EXEC @intReturnCode = sp_OAMethod @intRegisteredServers, @strTmpChar, @intRegisteredServer OUTPUT

   IF @intReturnCode <> 0

    GOTO Error   

   SET @strDoing = 'Get the Name Property of The Group'

   EXEC @intReturnCode = sp_OAGetProperty @intServerGroup, 'Name', @strServerGroupName OUTPUT

   IF @intReturnCode <> 0

    GOTO Error

   SET @strDoing = 'Get the Name Property of the Registered Server'

   EXEC @intReturnCode = sp_OAGetProperty @intRegisteredServer, 'Name', @strServerName OUTPUT

   IF @intReturnCode <> 0

    GOTO Error

   SET @strDoing = 'Get the Login Property of the Registered Server'

   EXEC @intReturnCode = sp_OAGetProperty @intRegisteredServer, 'Login', @strServerLogin OUTPUT

   IF @intReturnCode <> 0

    GOTO Error

   SET @strDoing = 'Get the Password Property of the Registered Server'

   EXEC @intReturnCode = sp_OAGetProperty @intRegisteredServer, 'Password', @strServerPassword OUTPUT

   IF @intReturnCode <> 0

    GOTO Error

   PRINT 'Group Name: ' + @strServerGroupName + CHAR(13) + 'Server Name: ' + @strServerName +

    CHAR(13) + 'Server Login: ' + @strServerLogin + CHAR(13) + 'Server Password: ' +

    @strServerPassword

   SET @intServerCount = @intServerCount + 1

   SET @strDoing = 'Destroy Registered Server Object'

   EXEC @intReturnCode = master.dbo.sp_OADestroy @intRegisteredServer

  END

  SET @intGroupCount = @intGroupCount + 1

  SET @strDoing = 'Destroy RegisteredServers Collection'

  EXEC @intReturnCode = master.dbo.sp_OADestroy @intServerGroup

  SET @strDoing = 'Destroy Registered Group Object'

  EXEC @intReturnCode = master.dbo.sp_OADestroy @intServerGroup

 END

 SET @strDoing = 'Destroy ServerGroups Object'

 EXEC @intReturnCode = master.dbo.sp_OADestroy @intServerGroups

 SET @strDoing = 'Destroy DMO Object'

 EXEC @intReturnCode = master.dbo.sp_OADestroy @intDMO

 -- IF we get here the normal way, don't do error

 GOTO Cleanup

Error:

 EXEC sp_OAGetErrorInfo @intDMO, @strSource OUTPUT, @strDescription OUTPUT

 PRINT 'Error While ['  + ISNULL( @strDoing     , '' ) +

   '], Source ['  + ISNULL( @strSource     , '' ) +

   '], Description [' + ISNULL( @strDescription     , '' ) + ']'   

Cleanup:

END

The procedure works OK, but when I run the script (as a .vbs file) I obtain 8 registered server (versus only one when I run the stored procedure). Do you see anything wrong on the SP? Can anybody here reproduce this strange behaviour?

Keep writing this great articles, I really enjoy them!