Microsoft KB Archive/171300

= 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

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.