none
Stored proc loop through error

    Question

  • USE [DBAUtilities]
    GO
    /****** Object:  StoredProcedure [dbo].[Dba_CmsServers]    Script Date: 10/28/2013 08:33:57 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER procedure [dbo].[Dba_CmsServers]






    as 

    SET NOCOUNT ON


    declare @serverid INT
    declare @connectionstring Varchar(100)
    Declare @servername varchar(100)
    Declare @server_type int
    Declare @servercount int
    Declare @env varchar(50)
    Declare @Gname varchar(100)
    Declare @groupcount int
    declare @iscluster varchar(50)
    declare @isstandby int
    declare @subgroup_id int


    Declare @groups table (name varchar(100))

    insert into @groups 

    select name from [msdb].[dbo].[sysmanagement_shared_server_groups_internal] where server_group_id > 5  
    order by name 



      select top 1 @Gname=name from @groups 


      order by name


      set @groupcount=@@ROWCOUNT



    while (@groupcount > 0)





    Begin
    print 100
    --select @gname from @groups

    set @server_type=0
    set @serverid= (select  server_group_id from  [msdb].[dbo].[sysmanagement_shared_server_groups_internal] where name =@Gname )


    Declare @servers Table(name varchar(50),servername Varchar(50),
    connectionstring Varchar(50),Environment varchar(50),Is_Standby Int,IS_Cluster varchar(20))


      insert into @servers 

      select alertgroup as name,servername,connectionstring,environment,StandByServer_C464B3DD_34DF_C0B4_AF31_E6F1FD622897 as  Is_standby ,Cluster_B404A336_2C09_68C7_DA29_0DE084222797
     as Is_CLuster from openquery(QLOPSMGRDB1,'exec dbalocal.dbo.dba_GetSCOMData ')   where AlertGroup=@Gname 


      select top 1 @connectionstring=connectionstring from @servers 
      where name =@Gname and is_standby=@isstandby and is_cluster=@iscluster
      order by name


      set @servercount=@@rowcount


      while (@servercount > 0)


      begin 



      Begin 




    print 200
                 if not exists (select name  from [msdb].[dbo].[sysmanagement_shared_registered_servers_internal] where name=@connectionstring and server_group_id=@serverid)
     and @Gname like  'IT%' and @iscluster is null

                              begin 

                                   set @servername=(select servername from @servers where @connectionstring=connectionstring)

      if @isstandby=0 and @iscluster is null

      begin

      set @subgroup_id=( Select server_group_id FROM [msdb].[dbo].[sysmanagement_shared_server_groups_internal] where name LIKE '%Online' and parent_id=@serverid )

              insert into  [msdb].[dbo].[sysmanagement_shared_registered_servers_internal]
      values 
      (@subgroup_id,@connectionstring,@servername,@servername + '  ' +'Servers',@server_type)

      end


      else if @isstandby=1 and @iscluster is null


      begin

       set @subgroup_id=( Select server_group_id FROM [msdb].[dbo].[sysmanagement_shared_server_groups_internal] where name lIKE '%Standby' and parent_id=@serverid )

              insert into  [msdb].[dbo].[sysmanagement_shared_registered_servers_internal]
      values 
      (@subgroup_id,@connectionstring,@servername,@servername + '  ' +'Servers',@server_type)

      end


     else 

     if @isstandby is null 

     begin

       set @subgroup_id=( Select server_group_id FROM [msdb].[dbo].[sysmanagement_shared_server_groups_internal] where NAME LIKE '%Standalone' and parent_id=@serverid )

              insert into  [msdb].[dbo].[sysmanagement_shared_registered_servers_internal]
      values 
      (@subgroup_id,@connectionstring,@servername,@servername + '  ' +'Servers',@server_type)

      end

                         end



    else 


                 if not exists (select name  from [msdb].[dbo].[sysmanagement_shared_registered_servers_internal] where name=@connectionstring and server_group_id=@serverid)
     and @Gname like  'IT%' and @iscluster='TRUE'

                              begin 

      set @servername=(select servername from @servers where @connectionstring=connectionstring)

      set @subgroup_id=( Select server_group_id FROM [msdb].[dbo].[sysmanagement_shared_server_groups_internal] where name LIKE '%HA' and parent_id=@serverid )

      insert into  [msdb].[dbo].[sysmanagement_shared_registered_servers_internal]
      values 
      (@subgroup_id,@connectionstring,@servername,@servername + '  ' +'Servers',@server_type)



      end


    else 


      if not exists (select name  from [msdb].[dbo].[sysmanagement_shared_registered_servers_internal] where name=@connectionstring and server_group_id=@serverid)
     and @Gname =  'NonCriticalProduction' --and @iscluster='TRUE'

                              begin 

                                   set @servername=(select servername from @servers where @connectionstring=connectionstring)



              insert into  [msdb].[dbo].[sysmanagement_shared_registered_servers_internal]
      values 
      (@serverid,@connectionstring,@servername,@servername + '  ' +'Servers',@server_type)



      end


    else 


                     if not exists (select name  from [msdb].[dbo].[sysmanagement_shared_registered_servers_internal] where name=@connectionstring and server_group_id=@serverid)
    and  @Gname = 'NonProduction'

               Begin 
      set @env=(select   environment from openquery(QLOPSMGRDB1,'exec dbalocal.dbo.dba_GetSCOMData ') where connectionstring=@connectionstring)

    If (@env='Test') 

    Begin   


    set @servername=(select servername from @servers where @connectionstring=connectionstring)
     set @serverid=(select server_group_id from  [msdb].[dbo].[sysmanagement_shared_server_groups_internal] where name ='Test Servers' )

      insert into  [msdb].[dbo].[sysmanagement_shared_registered_servers_internal]
      values 
      (@serverid,@connectionstring,@servername,@servername + '  ' +'Servers',@server_type)


    end




    else 

     If (@env='Beta') 

     Begin   


    set @servername=(select servername from @servers where @connectionstring=connectionstring)
     set @serverid=(select server_group_id from  [msdb].[dbo].[sysmanagement_shared_server_groups_internal] where name ='Beta Servers' )

      insert into  [msdb].[dbo].[sysmanagement_shared_registered_servers_internal]
      values 
      (@serverid,@connectionstring,@servername,@servername + '  ' +'Servers',@server_type)


    end



    else 

     If (@env='Dev') 

     Begin   


    set @servername=(select servername from @servers where @connectionstring=connectionstring)
     set @serverid=(select server_group_id from  [msdb].[dbo].[sysmanagement_shared_server_groups_internal] where name ='Dev Servers' )

      insert into  [msdb].[dbo].[sysmanagement_shared_registered_servers_internal]
      values 
      (@serverid,@connectionstring,@servername,@servername + '  ' +'Servers',@server_type)


    end




                End






    DELETE
    FROM @servers
    WHERE  connectionstring=@connectionstring


    SELECT  TOP 1 @connectionstring=connectionstring
    FROM @servers
    ORDER BY Name

    Set @servercount = @@rowcount







    end 




    end



    DELETE FROM @groups
    WHERE  name=@Gname


    SELECT  TOP 1 @Gname=name
    FROM @groups
    ORDER BY Name

    Set @groupcount = @@rowcount





    end




    hello everyone in above stored proc i cant get to print 200 its not looping through.

    any help plz highly appriciated 

                                                                                                                        
    Monday, October 28, 2013 2:27 PM

Answers

  • SELECT TOP 1
            @connectionstring = connectionstring
    FROM    @servers
    WHERE   name = @Gname
            AND is_standby = @isstandby
            AND is_cluster = @iscluster
    ORDER BY name
    			
    SET @servercount = @@rowcount
    -- *** Check your count ***
    PRINT 'Server count: ' + CAST(@servercount as VARCHAR(255));
    
    WHILE ( @servercount > 0 ) 
        BEGIN 
    	[..]
        END

    • Marked as answer by TrinaMJ8 Monday, October 28, 2013 7:14 PM
    Monday, October 28, 2013 2:36 PM