Hello,
I have a stored procedure that when executed runs for days and does not complete until I stop it. the intent is to update a field in batches of 5000 due to size and structure of database.
When I cancel, it shows me several 5000 batches that completed and asks me to commit. I get no errors or blocks.
Running the update statement by itself works fine.
Please help me determine what the issue is or give me suggestions on a better way to tackle this request.
Here is my code for the SP:
CREATE PROCEDURE [dbo].[UPDATE_INDATE_PARENT_EMAILS_AND_EDOCS_revised]
-- Add the parameters for the stored procedure here
@FieldName VARCHAR(16), @StartDate varchar(15), @EndDate varchar(15), @CustodianIDs varchar(500)
AS
PRINT ('NUMBER OF CUSTODIANS:')
DECLARE @XML AS XML
DECLARE @Delimiter AS CHAR(1) =','
SET @XML = CAST((''+REPLACE(@CustodianIDs,@Delimiter ,'')+'') AS XML)
DECLARE @cust_tbl TABLE (custodianid INT)
INSERT INTO @cust_tbl
SELECT N.value('.', 'INT') AS custodianid FROM @XML.nodes('X') AS T(N)
Declare @cust_id varchar(20)
Declare @update_sql varchar(max)
declare cust_curs cursor for
SELECT * FROM @cust_tbl
ORDER BY 1
open cust_curs
fetch next from cust_curs into @cust_id
while @@FETCH_STATUS = 0
BEGIN -- custodian loop
set @update_sql = '
SET NOCOUNT ON;
Select ID into #DOCS from tbldoc (nolock) where custodianid = ' + @CUST_ID + '
SET NOCOUNT OFF;
WHILE (1=1)
BEGIN
BEGIN TRANSACTION
update TOP(5000) tbldoc set ' + @fieldname + ' = 1
from dbo.tbldoc d (nolock)
join #docs d2 on d2.id = d.id
where (
(d.AttachPID in
(select d3.AttachPID from tblDoc d3 (nolock)
join #docs d4 on d4.id = d3.id
where isnull(d3.Exclude,0) = 0 and d3.AttachPID > 0 and d3.AttachLvl = 0 and
(d3.datesent between ''' + @StartDate + ''' and ''' + @EndDate + ''' or
d3.datelastmod between ''' + @StartDate + ''' and ''' + @EndDate + ''' or
d3.datecreated between ''' + @StartDate + ''' and ''' + @EndDate + ''')))
or
(isnull(d.Exclude,0) = 0 and d.AttachPID = 0 and d.AttachLvl = 0 and
(d.datesent between ''' + @StartDate + ''' and ''' + @EndDate + ''' or
d.datelastmod between ''' + @StartDate + ''' and ''' + @EndDate + ''' or
d.datecreated between ''' + @StartDate + ''' and ''' + @EndDate + ''')))
IF @@ROWCOUNT = 0
BEGIN
COMMIT TRANSACTION
BREAK
END
COMMIT TRANSACTION
END
'
PRINT ('UPDATED INDATE FOR CUSTODIANID ' + @CUST_ID)
exec (@update_sql)
IF OBJECT_ID('tempdb..#DOCS', 'U') IS NOT NULL
DROP TABLE #DOCS;
FETCH NEXT FROM cust_curs INTO @cust_id
END
CLOSE cust_curs
DEALLOCATE cust_curs
GO