Microsoft KB Archive/171300: Difference between revisions

From BetaArchive Wiki
(importing KB archive)
 
m (Text replacement - """ to """)
 
(One intermediate revision by the same user not shown)
Line 63: Line 63:
   declare @taskname varchar(10)
   declare @taskname varchar(10)
   select @count = 0
   select @count = 0
   while @count < 101
   while @count < 101
   begin
   begin
       select @taskname = &quot;task&quot; + convert(varchar, @count)
       select @taskname = "task" + convert(varchar, @count)
       exec msdb..sp_addtask @taskname, @subsystem = 'TSQL', @server = null,
       exec msdb..sp_addtask @taskname, @subsystem = 'TSQL', @server = null,
           @username = 'sa', @databasename = 'msdb', @enabled = 1,
           @username = 'sa', @databasename = 'msdb', @enabled = 1,

Latest revision as of 10:05, 21 July 2020

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