Using SSMS running the below UPDATE command I get returned message says 1 row affected. but if I then follow it up with a Select and it shows that the row never updated.
This issue happens in SSMS and a custom application I built in C# using Entity Framework. I have since stopped using Entity Framework generated code and moved straight to a stored proc. All of 3 different ways of performing the same task I randomly get this issue. Sometimes it can be 100’s or 1000’s of updates before the issue happens again or it could be a few updates later it happens again. I have run UPDATE STATISTICS, I have Rebuilt Indexes and Reorganized Indexes.
As a side note, I NEVER have an issue with INSERTs only UPDATEs we never DELETE any records. and total record count on the table is 201,741
UPDATE CreativeWorkFlowQueue SET CurrentIndicator = 'N' WHERE CreativeWorkFlowQueueId = 198231 SELECT * FROM CreativeWorkFlowQueue WHERE CreativeWorkFlowQueueId = 198231
here is the table
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[CreativeWorkFlowQueue]( [CreativeWorkFlowQueueId] [int] IDENTITY(1,1) NOT NULL, [DigitalCampaignCreativeId] [int] NOT NULL, [WorkFlowQueueId] [int] NOT NULL, [CurrentIndicator] [char](1) NOT NULL, [CreateDate] [datetime] NOT NULL, [LastUpdatedDate] [datetime] NOT NULL, [IsDeleted] [bit] NOT NULL, PRIMARY KEY CLUSTERED ( [CreativeWorkFlowQueueId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[CreativeWorkFlowQueue] ADD CONSTRAINT [CreateDate defaults to utcDate on CreativeWorkFlowQueue] DEFAULT (getutcdate()) FOR [CreateDate] GO ALTER TABLE [dbo].[CreativeWorkFlowQueue] ADD CONSTRAINT [LastUpdatedDate defaults to utcDate on CreativeWorkFlowQueue] DEFAULT (getutcdate()) FOR [LastUpdatedDate] GO ALTER TABLE [dbo].[CreativeWorkFlowQueue] ADD CONSTRAINT [DF_CreativeWorkFlowQueue_IsDeleted] DEFAULT ((0)) FOR [IsDeleted] GO ALTER TABLE [dbo].[CreativeWorkFlowQueue] WITH CHECK ADD CONSTRAINT [CreativeWorkFlowQueue DigitalCampaignCreativeId fk] FOREIGN KEY([DigitalCampaignCreativeId]) REFERENCES [dbo].[DigitalCampaignCreative] ([DigitalCampaignCreativeId]) GO ALTER TABLE [dbo].[CreativeWorkFlowQueue] CHECK CONSTRAINT [CreativeWorkFlowQueue DigitalCampaignCreativeId fk] GO ALTER TABLE [dbo].[CreativeWorkFlowQueue] WITH CHECK ADD CONSTRAINT [CreativeWorkFlowQueue WorkFlowQueueId fk] FOREIGN KEY([WorkFlowQueueId]) REFERENCES [dbo].[WorkFlowQueue] ([WorkFlowQueueId]) GO ALTER TABLE [dbo].[CreativeWorkFlowQueue] CHECK CONSTRAINT [CreativeWorkFlowQueue WorkFlowQueueId fk] GO
EDIT Adding the Stored Proc that also has the same issue.
I did not include this stored proc simply due to the fact that it is complex and if a simple update doesn’t work I figured start there first.
CREATE PROCEDURE [dbo].[sp_WtvToRtl] -- Add the parameters for the stored procedure here @tbl DCCIdList READONLY, @WorkFlowQueueId int AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; DECLARE @dccList TABLE ( CreativeWorkflowQueueId int null, DigitalCampaignCreativeId int null, WorkFlowQueueId int null, CurrentIndicator varchar(1) collate SQL_Latin1_General_CP1_CI_AS null, LastUpdatedDate datetime null ) DECLARE @DCIDList TABLE( DigitalCampaignCreativeId int null ) DECLARE @res TABLE ( MergeAction VARCHAR(50) collate SQL_Latin1_General_CP1_CI_AS , CreativeWorkFlowQueueId INT, _Position INT ) BEGIN TRANSACTION t1 BEGIN TRY INSERT INTO @dccList (CreativeWorkflowQueueId, DigitalCampaignCreativeId,LastUpdatedDate,WorkFlowQueueId,CurrentIndicator) select CreativeWorkflowQueueId, DigitalCampaignCreativeId,LastUpdatedDate,WorkFlowQueueId, 'N' as CurrentIndicator from dbo.CreativeWorkFlowQueue Where CurrentIndicator = 'Y' and IsDeleted = 0 and DigitalCampaignCreativeId In (SELECT DigitalCampaignCreativeId FROM VRF.DigitalCampaignCreative WHERE IsDeleted = 0 and SfClosed = 0 and DccId in (Select DccId COLLATE SQL_Latin1_General_CP1_CI_AS from @tbl) ) INSERT INTO @DCIDList SELECT DigitalCampaignCreativeId FROM @dccList GROUP BY DigitalCampaignCreativeId INSERT INTO @dccList (DigitalCampaignCreativeId,WorkFlowQueueId,CurrentIndicator) select DigitalCampaignCreativeId, @WorkFlowQueueId as WorkFlowQueueId, 'Y' as CurrentIndicator from @DCIDList MERGE vrf.CreativeWorkFlowQueue a USING ( SELECT x.CreativeWorkflowQueueId, x.DigitalCampaignCreativeId, x.LastUpdatedDate, x.WorkFlowQueueId, x.CurrentIndicator FROM @dccList x ) stg ON stg.CreativeWorkflowQueueId = a.CreativeWorkflowQueueId WHEN MATCHED THEN UPDATE SET a.CurrentIndicator = stg.CurrentIndicator, a.LastUpdatedDate = getutcdate() WHEN NOT MATCHED THEN INSERT ( CurrentIndicator, DigitalCampaignCreativeId, WorkFlowQueueId ) VALUES ( stg.CurrentIndicator, stg.DigitalCampaignCreativeId, stg.WorkFlowQueueId ) -- ******************************************************************** -- Remove this if you don't need to return the results below. OUTPUT INSERTED.CreativeWorkFlowQueueId, $ action INTO @res (CreativeWorkFlowQueueId, MergeAction); -- ******************************************************************** COMMIT TRANSACTION t1 END TRY BEGIN CATCH DECLARE @ErrorMessage NVARCHAR(MAX), @ErrorSeverity INT, @ErrorState INT; SELECT @ErrorMessage = ERROR_MESSAGE() + ' Line ' + CAST(ERROR_LINE() AS NVARCHAR(5)), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(); ROLLBACK TRANSACTION t1 --RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState); SELECT 'FAILED' MergeAction, s.CreativeWorkflowQueueId, s.DigitalCampaignCreativeId, '' DccId, s.WorkFlowQueueId, s.CurrentIndicator, null CreateDate, null LastUpdatedDate, null IsDeleted, @ErrorMessage ErrorMessage FROM @dccList s END CATCH -- Normally, I would not have this. But this will simulate -- what EF does if you need it. SELECT r.MergeAction, a.CreativeWorkFlowQueueId, a.DigitalCampaignCreativeId, d.DccId, a.WorkFlowQueueId, a.CurrentIndicator, a.CreateDate, a.LastUpdatedDate, a.IsDeleted, '' ErrorMessage FROM vrf.CreativeWorkFlowQueue a JOIN @res r ON a.CreativeWorkFlowQueueId = r.CreativeWorkFlowQueueId JOIN vrf.DigitalCampaignCreative d ON a.DigitalCampaignCreativeId = d.DigitalCampaignCreativeId ORDER BY r.MergeAction, r._Position END
The User-Defined Table Type is this.
CREATE TYPE [dbo].[DCCIdList] AS TABLE( [DccId] [nvarchar](18) NULL )
