September 9, 2015 at 10:35 am
My current proc updates(updates using joins of two or three tables) millions of records as per the condition provided for each department.
However, when the proc fails it writes to a ErrorTable, ERROR_MESSAGE(), ERROR_SEVERITY() and which department has failed.
Since the records are updated keeping the selected departments in loop, I get the department in a temp variable.
Now, I was asked to log the specific record where the failure was occured.
Something like log the identity column value or primary key value which record has failed.
Can someone help me with this issue?
September 9, 2015 at 10:45 am
biscribbler (9/9/2015)
My current proc updates(updates using joins of two or three tables) millions of records as per the condition provided for each department.However, when the proc fails it writes to a ErrorTable, ERROR_MESSAGE(), ERROR_SEVERITY() and which department has failed.
Since the records are updated keeping the selected departments in loop, I get the department in a temp variable.
Now, I was asked to log the specific record where the failure was occured.
Something like log the identity column value or primary key value which record has failed.
Can someone help me with this issue?
Look at the code and see how the department is being logged. Just do the same thing with the primary key column.
If you need more assistance we are going to need some details. Take a look at the first link in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 9, 2015 at 7:50 pm
Hi,
Below is my proc which updates using joins.
Though I have provided only two tables, I am updating almost all tables.
I am logging the depId and Table which has failed, however, I am now asked to log the VisitId where the failure has occurred.
DECLARE @dep_loop TABLE(id INT IDENTITY(1,1), did INT, rtp INT)
DECLARE @i INT=1 ,@f INT ,@rdt DATE ,@t VARCHAR(128) = 'Staring Soft Delete'
BEGIN TRY
INSERT INTO @dep_loop
SELECT depId, RetentionPeriod FROM DepSettings
WHERE DepId > 0 AND RetentionPeriod > 0
WHILE (@i <= (SELECT MAX(ID) FROM @dep_loop))
BEGIN
SELECT @f = (SELECT did FROM @dep_loop WHERE id=@i)
,@rdt = GETDATE()-(SELECT rtp FROM @dep_loop WHERE id=@i)
--Calls_Total
SET @t = 'Calls_Total'
UPDATE Calls_Total SET IsDeleted =1
WHERE depId = @f AND IsDeleted = 0
AND id IN
(SELECT c.Id FROM Calls_Total c
JOINPV ON c.VisitId = pv.VisitId
AND c.depId = @f AND pv.depId = @f
WHERE pv.CreatedDate < @rdtAND c.DateUpdated < @rdt)
--FSurveys_All
SET @t = 'FSurveys_All'
UPDATE s SET s.IsDeleted =1
FROM FSurveys_All s
JOIN Calls_Total c ON c.VisitId = s.VisitId
WHERE c.IsDeleted =1 AND c.depId = @f
SET @i = @i +1
END
END TRY
BEGIN CATCH
DECLARE @ErrorState INT = ERROR_STATE()
,@ErrorSev INT = ERROR_SEVERITY()
,@ErrorText VARCHAR(MAX) = ERROR_MESSAGE()
INSERT ErrorTable
SELECT @t+'-'+CONVERT(VARCHAR,@f) ,@ErrorText ,GETDATE()
RAISERROR (@ErrorText, @ErrorSev, @ErrorState);
END CATCH
September 10, 2015 at 7:22 am
biscribbler (9/9/2015)
Hi,Below is my proc which updates using joins.
Though I have provided only two tables, I am updating almost all tables.
I am logging the depId and Table which has failed, however, I am now asked to log the VisitId where the failure has occurred.
DECLARE @dep_loop TABLE(id INT IDENTITY(1,1), did INT, rtp INT)
DECLARE @i INT=1 ,@f INT ,@rdt DATE ,@t VARCHAR(128) = 'Staring Soft Delete'
BEGIN TRY
INSERT INTO @dep_loop
SELECT depId, RetentionPeriod FROM DepSettings
WHERE DepId > 0 AND RetentionPeriod > 0
WHILE (@i <= (SELECT MAX(ID) FROM @dep_loop))
BEGIN
SELECT @f = (SELECT did FROM @dep_loop WHERE id=@i)
,@rdt = GETDATE()-(SELECT rtp FROM @dep_loop WHERE id=@i)
--Calls_Total
SET @t = 'Calls_Total'
UPDATE Calls_Total SET IsDeleted =1
WHERE depId = @f AND IsDeleted = 0
AND id IN
(SELECT c.Id FROM Calls_Total c
JOINPV ON c.VisitId = pv.VisitId
AND c.depId = @f AND pv.depId = @f
WHERE pv.CreatedDate < @rdtAND c.DateUpdated < @rdt)
--FSurveys_All
SET @t = 'FSurveys_All'
UPDATE s SET s.IsDeleted =1
FROM FSurveys_All s
JOIN Calls_Total c ON c.VisitId = s.VisitId
WHERE c.IsDeleted =1 AND c.depId = @f
SET @i = @i +1
END
END TRY
BEGIN CATCH
DECLARE @ErrorState INT = ERROR_STATE()
,@ErrorSev INT = ERROR_SEVERITY()
,@ErrorText VARCHAR(MAX) = ERROR_MESSAGE()
INSERT ErrorTable
SELECT @t+'-'+CONVERT(VARCHAR,@f) ,@ErrorText ,GETDATE()
RAISERROR (@ErrorText, @ErrorSev, @ErrorState);
END CATCH
OK so what have you tried? There are two ways to do this. You can either just simply add this new piece of data OR you can rewrite this and get rid of that performance destroying loop. I assume you didn't write this but you have the luxury of dealing with this. Looping for an update is horrible from a performance perspective. This whole thing could made a lot simpler and faster by changing this into set based operations.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 11, 2015 at 7:24 am
biscribbler (9/9/2015)
My current proc updates(updates using joins of two or three tables) millions of records as per the condition provided for each department.However, when the proc fails it writes to a ErrorTable, ERROR_MESSAGE(), ERROR_SEVERITY() and which department has failed.
Since the records are updated keeping the selected departments in loop, I get the department in a temp variable.
Now, I was asked to log the specific record where the failure was occured.
Something like log the identity column value or primary key value which record has failed.
Can someone help me with this issue?
I'm just posting out of curiousity, you've never said why the update is failing. If you could determine the cases that will cause the procedure/update to fail, you could then query for this condition before attempting the update. You would be essentially writing a query to find the record that would prevent a successful update, and log the results of that query.
Just a suggestion, I understand that strategy might not be to your liking 🙂
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply