Registrations are now open. Join us today!
There is still a lot of work to do on the wiki yet! More information about editing can be found here.
Already have an account?

Microsoft KB Archive/171300

From BetaArchive Wiki
Knowledge Base


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

Article ID: 171300

Article Last Modified on 10/16/2003



APPLIES TO

  • Microsoft SQL Server 6.0 Standard Edition
  • Microsoft SQL Server 6.5 Standard Edition



This article was previously published under Q171300

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: kbprb kbusage KB171300