Microsoft KB Archive/171300
Article ID: 171300
Article Last Modified on 10/16/2003
- Microsoft SQL Server 6.0 Standard Edition
- Microsoft SQL Server 6.5 Standard Edition
This article was previously published under Q171300
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:
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
The limit of 100 tasks is set internally.
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