PRB: Deleting More Than 100 Tasks w/Sp_droptask Causes Msg 21003

• Microsoft SQL Server, versions 6.0 and 6.5

SYMPTOMS

You can use the sp_droptask system procedure to remove all tasks assigned to a given login name. However, if you delete over 100 tasks simultaneously, you will receive the following warning message 21003:

   Error executing xp_schedulersignal extended stored procedure: Trigger
   buffer count exceeded.

   use msdb
   go

   /* Insert 101 tasks. */

   declare @count int
   declare @taskname varchar(10)
   select @count = 0
   while @count < 101
   begin
      select @taskname = "task" + convert(varchar, @count)
      exec msdb..sp_addtask @taskname, @subsystem = 'TSQL', @server = null,
          @username = 'sa', @databasename = 'msdb', @enabled = 1,
          @freqtype = 2, @freqinterval = 0, @freqsubtype = 1,
          @freqsubinterval = 0, @freqrelativeinterval = 1,
          @freqrecurrencefactor = 1, @activestartdate = 19970708,
          @activeenddate = 99991231, @activestarttimeofday = 0,
          @activeendtimeofday = 235959, @runpriority = 0,
          @emailoperatorname = null, @retryattempts = 0,
          @retrydelay = 0, @loghistcompletionlevel = 2,
          @emailcompletionlevel = 0, @command = 'sp_who',
          @tagadditionalinfo = null, @description = null,
          @tagobjectid = 0, @tagobjecttype = 0, @cmdexecsuccesscode = 0
      select @count = @count + 1
   end

   /* Drop all tasks. */

   sp_droptask null, sa

CAUSE

The limit of 100 tasks is set internally.

MORE INFORMATION

Despite the warning, all tasks will still be removed successfully.

This problem was tested on SQL Server builds 6.0.151 and 6.5.258.