February 9, 2019 at 5:57 pm
I have problem where a stored procedure is daily executed as part of an SSRS subscription, but the last of 3 statements within the procedure fails validation ie. expected UPDATES in SourceTable are missing.
The third statement is an UPDATE and should only run after the first two. Executed from SSMS it does what it's supposed to ie. it timestamps the Date_Sent column with today's date. (so I know what information was sent via SSRS to the client).
CREATE STORED PROCEDURE SSRS_Report_Update AS
--1
SELECT * INTO #temp from ReportingTable WHERE claim_status = 'DMG' and Date_Submitted = NULL
--2
SELECT * FROM #temp --this retrieves the reports data set which surfaces to SSRS report
--3
UPDATE SourceTable --this updates the source table so that on future runs it is not used for SSRS report
SET Date_Sent = getdate()
FROM SourceTable st JOIN #temp t on st.tracking_number = t.tracking_number
GO
is it possible that when this stored procedure runs, the 3rd statement starts running simultaneously as the first or second,and that it is finishing before it should even begin? If yes, is the fix to use GO statements between? If no, what could be the reason the UPDATE isn't doing it's job?
--Quote me
February 10, 2019 at 6:39 am
from inside the procedure, the commands run in the order you created them in, and there is no way for the third to execute before the previous commands complete.
that said, there is no guarantee there is data you expect in #temp, so the update statement may execute, but affect no rows, obviously, if the #temp table was not populated with data.
That is perfectly normal, though..no data that matches the expected criteria, so nothing to update.
Lowell
February 10, 2019 at 8:02 pm
this is a discouraging. Everyday there is data in the #temp table (verified because the SSRS report is sent out with data (SELECT * FROM #TEMP)
Daily I am having to open the sproc and rerun the SELECT INTO #TEMP + UPDATE statement sections and then the SourceTable is updated.
Below is actual stored procedure
/****** Object: StoredProcedure [dbo].[rpt_UPS_Extract_CT_PDMM] Script Date: 2/10/2019 6:16:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[rpt_UPS_Extract_CT_PDMM]
AS
DECLARE @Id int = 232,
@LogId int,
@EventText varchar(max),
@RowsAffected int,
@ReportDate date = DATEADD(d, 0, DATEDIFF(d, 0, GETDATE()));
SET NOCOUNT ON;
SET ANSI_WARNINGS ON;
BEGIN TRY
-- Begin a ProcessLog for tracking.
EXECUTE ODS_Code.dbo.DoLog
@Id = @Id,
@LogId = @LogId OUTPUT,
@StartDate = 'Now',
@status = 'Running',
@ImportFileDate = @ReportDate;
-- Add milestones or debug information to the ProcessLogEvent table.
EXECUTE ODS_Code.dbo.DoLogEvent
@Id = @Id,
@LogId = @LogId,
@EventText = 'executing ODS_CODE.dbo.rpt_ UPS_Extract_CT_PDMM'
--select data for report
SELECT
UPS_Account_Number
, UPS_Tracking_Number
, UPS_Pick_Up_Date
, Original_Invoice_Number
, Purchase_Order_Number
, materialdescription_ups
, Merchandise_Quantity
, Package_Weight
, LBS_or_KGS
, Replacement_Cost
,REPLACE([Shipping_Charges],'n/a','') AS [Shipping_Charges]
, Total_Claim
, Consignee_Contact_Name
, Consignee_Contact_Number
, Customer_contacted
, Replacement_been_shipped
, Replacement_UPS_Tracking_Number
, Claim_Type as type_of_damage
, '' as package_condition
INTO #TEMP_pdmm
FROM BusOps_Transportation.stg.tracer_ups_extract_ct
WHERE claim_type not in ('CCNR','LATE');
EXECUTE ODS_Code.dbo.DoLogEvent
@Id = @Id,
@LogId = @LogId,
@EventText = 'generate report set';
SELECT
UPS_Account_Number
, UPS_Tracking_Number
, UPS_Pick_Up_Date
, Original_Invoice_Number
, Purchase_Order_Number
, materialdescription_ups
, Merchandise_Quantity
, Package_Weight
, LBS_or_KGS
, Replacement_Cost
, Shipping_Charges
, Total_Claim
, Consignee_Contact_Name
, Consignee_Contact_Number
, Customer_contacted
, Replacement_been_shipped
, Replacement_UPS_Tracking_Number
, Type_of_Damage
, Package_Condition
FROM #TEMP_pdmm;
EXECUTE ODS_Code.dbo.DoLogEvent
@Id = @Id,
@LogId = @LogId,
@EventText = 'report dataset returned from #temp';
UPDATE
sc
SET
Date_Submitted_to_Carrier = CASE WHEN sc.Date_Submitted_to_Carrier is null THEN @ReportDate ELSE sc.Date_Submitted_to_Carrier END
FROM
[ODS_TMUS4].[dbo].[LGTC_ShipmentsClaims] sc JOIN
#TEMP_pdmm ct ON sc.Tracking_Number = ct.UPS_Tracking_Number
EXECUTE ODS_Code.dbo.DoLogEvent
@Id = @Id,
@LogId = @LogId,
@EventText = 'update to [ODS_TMUS4].[dbo].[LGTC_ShipmentsClaims] completed'
EXECUTE ODS_Code.dbo.DoLog
@Id = @Id,
@LogId = @LogId,
@EndDate = 'Now',
@status = 'Success',
@RowsAffected = @@ROWCOUNT;
END TRY
BEGIN CATCH
-- Record the error details to the ProcessLogEvent table.
SET @EventText = ODS_Code.dbo.BuildErrorMessage(ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_LINE(), ERROR_PROCEDURE());
EXECUTE ODS_Code.dbo.DoLogEvent
@Id = @Id,
@LogId = @LogId,
@EventText = @EventText
-- Mark the ProcessLog record as failed and re-try the error.
EXECUTE ODS_Code.dbo.DoLog
@Id = @Id,
@LogId = @LogId,
@EndDate = 'Now',
@status = 'Failed';
RAISERROR(@EventText, 11, 1);
END CATCH
GO
--Quote me
February 11, 2019 at 7:06 am
Is it possible that LGTC_ShipmentsClaims is empty (or at least contains no matching rows) at the time your stored procedure is run?
February 11, 2019 at 9:40 am
Chris Wooding
No, not all possible that the ShipmentsClaims table is empty.
One change I made yesterday was to add an ELSE statement to the UPDATE, and I included this change in the code I posted yesterday.
Before it was like this
UPDATE
sc
SET
Date_Submitted_to_Carrier = CASE WHEN sc.Date_Submitted_to_Carrier is null THEN @ReportDate END
FROM
[ODS_TMUS4].[dbo].[LGTC_ShipmentsClaims] sc JOIN
I checked yesterday's run and the UPDATES did happen post that ELSE change, but since it's been an on/off problem, I can't say after one day that's the fix.
I have also read that if ELSE is left out that by default SQL will interpret that as ELSE Date_Submitted_To_Carrier IS NULL. Could lack of ELSE cause the
records that are NULL to remain NULL?
--Quote me
February 12, 2019 at 8:59 am
polkadot - Monday, February 11, 2019 9:40 AMChris Wooding
No, not all possible that the ShipmentsClaims table is empty.One change I made yesterday was to add an ELSE statement to the UPDATE, and I included this change in the code I posted yesterday.
Before it was like this
UPDATE
sc
SET
Date_Submitted_to_Carrier = CASE WHEN sc.Date_Submitted_to_Carrier is null THEN @ReportDate END
FROM
[ODS_TMUS4].[dbo].[LGTC_ShipmentsClaims] sc JOINI checked yesterday's run and the UPDATES did happen post that ELSE change, but since it's been an on/off problem, I can't say after one day that's the fix.
I have also read that if ELSE is left out that by default SQL will interpret that as ELSE Date_Submitted_To_Carrier IS NULL. Could lack of ELSE cause the
records that are NULL to remain NULL?
Only if @ReportDate is NULL.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 12, 2019 at 9:26 am
Hi sgmunson , thanks for replying.
Yeah, but @ReportDate is
@ReportDate date = DATEADD(d, 0, DATEDIFF(d, 0, GETDATE()))
Two days in a row now, the UPDATE has been working since adding the ELSE statement. I will continue monitoring.
Helen
--Quote me
February 12, 2019 at 9:32 am
polkadot - Monday, February 11, 2019 9:40 AMChris Wooding
No, not all possible that the ShipmentsClaims table is empty.One change I made yesterday was to add an ELSE statement to the UPDATE, and I included this change in the code I posted yesterday.
Before it was like this
UPDATE
sc
SET
Date_Submitted_to_Carrier = CASE WHEN sc.Date_Submitted_to_Carrier is null THEN @ReportDate END
FROM
[ODS_TMUS4].[dbo].[LGTC_ShipmentsClaims] sc JOINI checked yesterday's run and the UPDATES did happen post that ELSE change, but since it's been an on/off problem, I can't say after one day that's the fix.
I have also read that if ELSE is left out that by default SQL will interpret that as ELSE Date_Submitted_To_Carrier IS NULL. Could lack of ELSE cause the
records that are NULL to remain NULL?
The way I read it, for rows where Date_Submitted_to_Carrier is null, the value gets set to the contents of the variable, but for any rows where this column is populated already, it gets set to null unless the CASE has an ELSE (ie. I think your fix is the correct one).
February 13, 2019 at 12:23 am
polkadot - Sunday, February 10, 2019 8:02 PMthis is a discouraging. Everyday there is data in the #temp table (verified because the SSRS report is sent out with data (SELECT * FROM #TEMP)
Daily I am having to open the sproc and rerun the SELECT INTO #TEMP + UPDATE statement sections and then the SourceTable is updated.Below is actual stored procedure (snipped)
UPDATE
sc
SET
Date_Submitted_to_Carrier = CASE WHEN sc.Date_Submitted_to_Carrier is null THEN @ReportDate ELSE sc.Date_Submitted_to_Carrier END
FROM
[ODS_TMUS4].[dbo].[LGTC_ShipmentsClaims] sc JOIN
#TEMP_pdmm ct ON sc.Tracking_Number = ct.UPS_Tracking_Number
Umm, why not
UPDATE
sc
SET
Date_Submitted_to_Carrier = @ReportDate
FROM
[ODS_TMUS4].[dbo].[LGTC_ShipmentsClaims] sc JOIN
#TEMP_pdmm ct ON sc.Tracking_Number = ct.UPS_Tracking_Number
where sc.Date_Submitted_to_Carrier is null
Why update when you don't need to?
February 14, 2019 at 12:53 am
michael.cole 47030 I like the update statement you suggest and appreciate the point about not updating where you don't need to.
For past 3 days, since adding ELSE, I have not seen problem with the UPDATE failing to update the records where date_submitted is NULL.
I don't understand how adding ELSE made the difference, and I will wait a full week of daily successes before making this tweak.
--Quote me
February 14, 2019 at 1:24 am
polkadot - Thursday, February 14, 2019 12:53 AMmichael.cole 47030 I like the update statement you suggest and appreciate the point about not updating where you don't need to.For past 3 days, since adding ELSE, I have not seen problem with the UPDATE failing to update the records where date_submitted is NULL.
I don't understand how adding ELSE made the difference, and I will wait a full week of daily successes before making this tweak.
The ELSE makes a difference because otherwise the result of the CASE statement is null if the WHEN part is not true.
February 14, 2019 at 10:02 am
the UPDATE should happen if a NULL is found, which should be all records on the join (they are harvested into the reporting 'stg' table on the basis that they have date_submitted = NULL in the Source_Table in addition to other qualification, so all records on the join should have NULL. You don't see this part, but it's true. There is nothing in the stg table which doesn't have a Date_submitted = NULL in the sourcetable)
Only if a NULL is *not found* should the ELSE be implemented..... So I had considered an ELSE pointless. Yet, for past 3 days since adding it, I have seen that all records found on the join are now getting updated whereas before they were not.
--Quote me
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply