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

ID: Q171300

The information in this article applies to:

Microsoft SQL Server versions 6.0, 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.

The following scripts demonstrate the problem:


   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.

Additional query words: sqlexec


Keywords          : kbusage SSrvGen SSrvStProc 
Version           : 6.0 6.5
Platform          : winnt 
Issue type        : kbprb

Last Reviewed: April 21, 1999