November 10, 2017 at 8:54 am
Hi,
I have some code that's been running fine 99% of the time but sometimes it executes the same stored procedure twice for an unknown reason.
So this is the code:DECLARE @sp-2 varchar(150)
DECLARE @cnt INT = 1
DECLARE @DateFrom date = DATEADD(m,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())-12,0))
DECLARE @DateTo date = DATEADD(m,0,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
DECLARE @RunID INT
SET @RunID = (SELECT MAX(RunID) as RunID FROM [RISE].[dbo].[tb_CompaniesToRun])
-----------------------------------------------------------------------------------------------
------------------- EXECUTE ALL THE STORED PROCEDURES THAT NEED TO RUN ------------------------
-----------------------------------------------------------------------------------------------
CREATE TABLE #temp (Target int)
WHILE @cnt < (SELECT DISTINCT MAX(Target_ID)+1 as SPs FROM [RISE].[dbo].[tb_CompaniesToRun] WHERE RunID = @RunID)
BEGIN
SET @sp-2 = NULL
SET @sp-2 = (SELECT DISTINCT '[RISE].[dbo].' + [Target_StoredProcName] as SPs FROM [RISE].[dbo].[tb_CompaniesToRun] WHERE Target_ID = @cnt AND RunID = @RunID)
INSERT INTO #temp SELECT @cnt
BEGIN TRY
EXEC (@sp)
END TRY
BEGIN CATCH
INSERT INTO [RISE_AUDIT].[dbo].[tb_Error_Log]
SELECT Err.*
,CTR.CTR_ID
FROM
(
SELECT @RunID RunID
,GETDATE() AS Date
,ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,(SELECT DISTINCT [Target_ID] FROM [RISE].[dbo].[tb_Targets] WHERE Target_StoredProcName = ERROR_PROCEDURE()) [Target_ID]
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage
) Err
LEFT JOIN
[RISE].[dbo].[tb_CompaniesToRun] CTR
ON CTR.Target_ID = @cnt
AND CTR.RunID = @RunID
DELETE #temp WHERE Target = @cnt
SELECT ERROR_PROCEDURE()
RAISERROR('Something went wrong with: ',1,0);
END CATCH
SET @cnt = @cnt + 1
END
notes: tb_companies_to_run is a queue of stp's that need to run
and they are separated by batches by the RunID
as the loop is going thru running all of the stp's in the current batch sometimes it just decides to run the stp's twice creating duplicates in data tables.
I've checked the batch that had duplicated data but the queue was alright no duplicates in the tb_companies_to_run table
There is a try catch in there but in that run nothing failed so it shouldn't matter
Any ideas anyone please I'm running out of them 😀
November 10, 2017 at 9:27 am
Is it possible something modified [RISE].[dbo].[tb_CompaniesToRun] while the process was running? Since you're requerying that table every time you loop it's possible something was modifying those SP names while it was running.
November 10, 2017 at 9:36 am
What about something like this?
DECLARE @sp-2 nvarchar(max)
DECLARE @RunID INT
SET @RunID = (SELECT MAX(RunID) as RunID FROM [RISE].[dbo].[tb_CompaniesToRun])
-----------------------------------------------------------------------------------------------
------------------- EXECUTE ALL THE STORED PROCEDURES THAT NEED TO RUN ------------------------
-----------------------------------------------------------------------------------------------
SELECT @sp-2 = ( SELECT DISTINCT '[RISE].[dbo].' + [Target_StoredProcName]
FROM [RISE].[dbo].[tb_CompaniesToRun]
WHERE RunID = @RunID
FOR XML PATH(''), TYPE).value('./text()[1]', 'varchar(max)')
EXEC sp_Executesql @sp-2;
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply